Update table with a loop in a Process Model

Question

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.

Mike Schmitt · Answer

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.

Mike Schmitt · Answer

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.

Mike Schmitt · Answer

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.

Mike Schmitt · Answer

Also you said you "don't need to filter", but then your "update" portion idicates that you actually do need filtering. As far as I can tell, you should actually be doing this filtering on your initial query. 
 
 Additionally if you're trying to update the value in a table MY_TABLE based on the view results you originally queried from your separate view, you would want to use an intermediary step to query whatever entries from MY_TABLE you actually need to update, after your original View query (presumably).

Mike Schmitt · Answer

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.)

Mike Schmitt · Answer

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.

Update table with a loop in a Process Model

Top Replies