Hello all -
I have a process model, where the first task is to call the database for a view (image1).
This view has a unique column that is saved to a CDT with other columns (roleid and rest of columns).
I save the output of the query database results to an array variable that has the view CDT.
My next task would be to iterate through the array variable to update the table that matches the roleid.
How can I loop through each array with roleid from the variable and match it to the table roleid? I don't think using a Query Database with the following is correct as the variable propert doesn't seem accessible.
Thank you for your help.
Discussion posts and replies are publicly visible
First and foremost I recommend you completely abandon use of the Query DB node you're using here, and replace it with Query Entity and Write to DSE node(s) as needed. There is absolutely no reason I can see that you should be using Query DB here.
There is no Query Entity in process modeler, even when I search for it.
Query Entity is not a node - you merely use a!queryEntity() in the Data Output tab of a script task (or any other sort of node, when appropriate).
My standard Best Practice is to create an Expression Rule preconfigured to do a basic query (with a few basic filters) for every individual table and view, and then it's just a matter of referencing that expression rule in your process model, instead of configuring a brand new a!queryEntity() statement. But either method will work.
It's highly recommended to set up your queries in Expression Rules though, because it dramatically simplifies and empowers the process of testing out your querying and filtering for any given table / view.
(Note in advance: to clarify, any tables that are configured to use Synced Record Type data instead of Entity data, you'd replace the elements I describe above with Query Record rule(s) and a Write Records node.)
But if I created a view already in the database, there is no need for me to an expression rule to filter. I don't know best standards yet, but I can assume I can use the view already since its "filtered" in the database view.
Also are you saying that I should use a Script Task, then in the Data Panel on Inputs, use my CDT and then on the Data Panel on the Outputs, use the a!queryEntity in Expression properties to loop through each?
here2learn said:But if I created a view already in the database, there is no need for me to an expression rule to filter.
Views need to be queried via the Query Entity function. First you would need to create a CDT to match the view (if you already have, that's fine), and a Data Store entity matching that CDT which would be kept in whatever Data Store object you use to reference your Views (if you created your CDT directly from the View, it's likely this may have been handled automatically).
Then, you would build a basic use case a!queryEntity() call for that DSE in its own unique expression rule. This is not required but helps a lot.
Inside the expression rule you can configure any (or no) filtering, handle default paging options, etc. You can then simply test the query in the expression rule editor, and be sure what the output will be for any given input(s) when called in your process model.
Here's a QE expression rule for a template "reference data" table. It would be functionally the same when doing it for a View - the key consideration is which constant is passed in for the "entity" parameter.
here2learn said:I should use a Script Task, then in the Data Panel on Inputs, use my CDT and then on the Data Panel on the Outputs
No. Ignore the "input" tab. On the "output" tab add a Custom Output item and place your call to your Query Entity (preferably expression rule) in the Expression box, and set the Target to your CDT. This would handle the Querying portion for what you're trying to accomplish.
Then in a subsequent node, you can loop over the query results and do whatever transformation you want to them.