Greetings,
I have a dataTable variable which is created with a Machine step. There is a column which is NULL initially. Later, I iterate through each row in that table using Assignment steps. For each row (a date), I use some complex SQL queries to determine a boolean value for that specific date. My question is how I can use FlowScript (or something else), to update my table variable with these new values?
Basically, what I want to do is:
"
update TABLEVAR
set “value” = :result (result of my complex queries)
where “date” = :firstDate
"
In a later stage, I use this table variable as a data source in a ‘list selection input’ in a User step, and I need to show both the date and calculated value for each side by side.
Very grateful for any advice!
I assume you cannot update the table already in SQL?
You should be able to do this using the with ... where syntax on tables.
FlowScript tables are immutable, so you don’t update a row in place. Instead, you create a modified version of the table and assign it back to the same variable.
For example:
TABLEVAR = TABLEVAR with [value: result] where date = firstDate
This updates the value column for the row where date = firstDate.
If you’re doing this inside a loop, you can simply reassign the table variable on each iteration:
TABLEVAR = TABLEVAR with [value: result] where date = firstDate
After the loop has finished, TABLEVAR will contain all the calculated values and can be used directly as the data source for your List Selection Input, allowing you to display both the date and the calculated boolean value side by side.
Disclaimer that I have recently been much more in Connect’s flow script.