Parker Software Ltd Homepage
Forum Home Forum Home > ThinkAutomation Business Process Automation > Scripting
  New Posts New Posts RSS Feed - Pass RecordSet back to TA Trigger environment?
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Pass RecordSet back to TA Trigger environment?

 Post Reply Post Reply
Author
Message
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Topic: Pass RecordSet back to TA Trigger environment?
    Posted: 08 Aug 2019 at 1:47pm
Hello
I am attempting to use a template to generate a report based on a recordset from a SQL database.  How would I go about passing the recordset between the script environment and the TA trigger environment so that I can walk through the records?  
thanks
Mark
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Posted: 08 Aug 2019 at 4:38pm
To be more clear, I am looking for a way for the recordset to persist between the script environment and the trigger environment, so that I can do something like this:

run a script -> open the database and pull the recordset for a month of activity
return to trigger

Repeat loop
create a section of the report with Record-1
run a script -> find a recordset of data related to the Record-1 and process
return to trigger
collect the results of the related data and add to the right section of the report
Repeat until recordset is at end of file

final report formatting
send report
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
parkersoft-nick View Drop Down
Admin Group
Admin Group
Avatar
Lead Developer - ThinkAutomation

Joined: 28 Dec 2015
Location: UK
Points: 130
Post Options Post Options   Thanks (0) Thanks(0)   Quote parkersoft-nick Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2019 at 3:29am
Hi Mark,

It largely depends on the manner you want to use in order to build the report.

I assume you are looking to use the 'run a report trigger action', in which you would edit a report layout using field replacements / variables?

The below suggestions also assume you are using a script to connect to your database via something like ADO to query and return your recordsets. (I have assumed you are familiar with pulling recordsets via script but feel free to ask for further clarification on this if needed.)

I think your best solution would be to define a variable for the body of the report (within the report designer) in which you are planning to append your data records on each query to the DB.

This means that each time you get a returned record set which you want to include within the report, you can append it to the existing value of your variable. (The variable is one that would be created via the 'Set Variable' trigger action in the trigger action list)

So for example, if you have a %reportBody% variable in the body of your report, in your script you can do something like the below to append your recordsets after each query.

AccountVariable("reportBody") = AccountVariable("reportBody") & [returned recordset string]


By the end of your script, having completed all the relevant queries, your reportBody should contain all of the records you want in the report, and as you have already added the reportBody variable to your report via the report designer, it should be fully populated when the report is ran.

Obviously you would need to format your recordset strings appropriately before appending them.

Hope this helps. 



Edited by parkersoft-nick - 09 Aug 2019 at 3:34am
Nick Proud - Parker Software

Software Development Lead -ThinkAutomation
Back to Top
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2019 at 7:04am
Hi Nick, thanks for this fast response.  I have plenty of scripts pulling recordsets using the ADO support, no problem.  I'm looking for a way that the recordset would persist outside the script, back into the trigger interpreter environment.  If this is possible then the logic of pulling the primary record followed by a related recordset, and then using the template fields would work well.  Otherwise I will continue to build out the script and make the body of the report by an html table (assuming I can put a html table into the report template and have the html interpreted). 
thanks
mark
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
parkersoft-nick View Drop Down
Admin Group
Admin Group
Avatar
Lead Developer - ThinkAutomation

Joined: 28 Dec 2015
Location: UK
Points: 130
Post Options Post Options   Thanks (0) Thanks(0)   Quote parkersoft-nick Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2019 at 7:11am
Hi Mark,

Yes the AccountVariable object allows you to update variables within the trigger interpreter environment. So if you used 'Set Variable' at the top of the trigger and made a variable called %foo%, at any time after that action when you use a script, changing the value of AccountVariable("foo") will persist that value into the variable in the trigger environment.

Is that what you were looking for?

Regarding the HTML table, you would need to perform the HTML formatting on your recordset string, but the report can be saved as an HTML file so the table should be rendered as such.

Hope this helps.


Edited by parkersoft-nick - 09 Aug 2019 at 7:11am
Nick Proud - Parker Software

Software Development Lead -ThinkAutomation
Back to Top
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2019 at 7:41am
Hmm, I tried that with the variable that will contain the recordset and got a run time error in the script.  I'll try again.   If I can get this to work then I thought to create columns in the template that can be filled in and automatically formatted. 
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2019 at 12:13pm
Trying to save the record set into a Trigger Set Variable results in "TAA Administrator Type mismatch FROM clsMetaVariableCache.SetVariableValue" but I realized this was not very elegant in the first place.  I'm going to see about the trigger environment keeping track of the record pointer in the recordset and jumping to the next one upon re-entry into a script. 
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Posted: 09 Aug 2019 at 2:18pm
Looks like each script needs to open the table and close the table; assigning the ADODB.Connection string to a trigger variable and attempting to use it in a different script doesn't work.  Must be plenty of other supporting metadata in the interpreter environment that gets lost when leaving a script.
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.05
Copyright ©2001-2016 Web Wiz Ltd.

This page was generated in 0.094 seconds.