Hi,
My use case is as follows: I want to update a main table and multiple linked sub tables (which may have multiple inserts each) in MySQL.
The way I would logically think to do this is to write a custom transaction SQL insert query, with a repeat loop based on the number of items there are in each of the form repeats, so that there is no risk of one of the inserts not happening correctly.
For example, if I had 1 main form with 1 sub table containing 2 items within a form repeat, I would write the custom transaction as follows:
at the start of the API I would start the custom SQL as follows:
"START TRANSACTION
INSERT INTO main_form_table main_form_id VALUES({{POST_main_form_id}});"
Then using a loop of the form repeat, it would then continue the transaction to insert those 2 sub items into the sub table, then commit the transaction outside of the loop of the form repeat.
Sadly, this is not possible using the custom SQL logic. I also can’t think of a way to make this work by calling stored procedures, as there are multiple form repeats stored within the main form. If I called the stored procedures individually, it would still execute individually instead of in a transaction as a whole as intended.
Is there a way to get around this limitation??? Or am I missing something? Would love some help here - driving me up the wall and I can’t find another forum post that actually solves this issue.
Last updated: