With the great help of AnsonHwang I was able to successfully run a "save action" on my table that updates several entries on my Retool internal database (link).
Now, I'd like to be able to programmatically "add more changes" to the instruction. Meaning: the table (table_orders) has the row "payment_status" (enum data type) and row "order_completed" (date data type). If the changesetArray includes the instruction to update a row's payment_status to "paid" I want to also update the order_completed with the current Datetime (in ISO 8601).
Not sure what the best approaches are here.
I've tried to:
- Set a delay on the "updateRowTable" query.
- Run a script that runs before the update and that adds the key-pay "order_completed: new Date().toISOString();" to the "table_orders.changesetArray"
It did not produce the intent. Maybe I should also update the "table_orders.changesetObject". Would this approach work?
Or alternatively should I "not clear the changeset on successful save" and run an SQL query specifically to update order_completed after the save actions ran, and programmatically clear the changeset afterwards?
Or any other better approach?
By the way, afterwards I'd also like to trigger a email sent via SMTP to provide the user with a payment receipt.
Thank you for your tips
Here is the updateRowTable for reference:
Hello, You can't change the value of table_orders.changesetObject, or table_orders.changesetArray. It seem readonly to developer.
But you can use a variable to store the value wait to insert.
you can modify your script to
tempState.setIn(["modifiedChangeSetArray"],[...table1.changesetArray.map(item=>({...item, "order_completed": new Date().toISOString()}))])
and then in your insert query you can refer it with {{tempState.modifiedChangeSetArray}}
If you want to reload data, don't forget to add the query to batchInsertData query's success handler.
You also can clear changeset auto by check
1 Like
By the way, though the Run script is before batchInserData.trigger. but it may run after that.
So for more confidence result.
You better to run only one script in the save action event handlers. using additionalScope to pass the rearranged changesetArray (which with extra data as you said 
) to the query.
Here is my full solution.(this time we don't need variable of tempState)
batchInsertData.trigger({
additionalScope:{
changesetArr: [...table1.changesetArray.map(item=>({...item, "order_completed": new Date().toISOString()}))]
}
})
here a some docs of additionalScope
2 Likes
Wow. This last solution is somehow close to what I envisioned, but it's so much more "convoluted". I'd never reach this. You clearly have a deep insight into Retool's working. Thank you.
Sounds great. I'm going to try this. I just need to somehow add in that script a condition that matches the results that one gets from:
table_orders.changesetArray.filter(item => item.status === "Paid").map(item => item.id)
2 Likes
Hi @AnsonHwang
I've tried to directly replicate your suggestion without any changes besides the table name.
The batchInsertData query is currently entering an infinite loop. Something is not right for me.
Afterwards, in order to try to debug, I made a mix with your first suggestion where I also saved the array into the global variable "tempState" and this is the structure:
Maybe the object to be inserted needs to be an array instead if a JS object?
Yes, batch insert need Array instead of Object.
But this code, which I'm using, should produce an array I guess... So not sure what might be wrong here. Any way to debug? I see no log in console while Query is looping infinitely.
Have you trigger batchInsertData in other place? such as event handler of table?
HI @AnsonHwang
I just created the batchInsertData query and only added it to the "Save action" on the "table_orders" table.
Here is the table_orders with no event handler
