Save to sql database

Hi, hope someone could help me :slightly_smiling_face: I'm currently displaying data from 2 sql tables into 1 retool table. I have several columns where user can choose from dropdown or write notes and everything should be saved in my sql tables depending on id and type, so it would know in which table and where to save needed data. I can't seem to solve the saving problem, will add querie of how I'm trying to save, but nothing works. I saves, but after one or two refreshes it disappears. So basically maybe someone knows how can i save data by writing sql query? I don't really know where is the problem, cause sometimess it works, sometimes its not, maybe retool itself has bugs, have no idea, so hope someone could help on this if its even possible :slightly_smiling_face:

It looks like you have two update queries that need to be triggered on the “save” event. As such, I’d suggest you add a JS Query that triggers each of them, and set this as the save handler.

I'd agree w @jg80. i guess i can think of 2 other options, the first i've never tried in retool before but you could try specifying the transaction. i think this would let you do 2 updates at the same time(?) so you don't have to worry about query chaining.

BEGIN;
  UPDATE domain_information
  SET
    user_notes = {{ group4.changesetArray[0].user_notes }},
    category_id = {{ group4.changesetArray[0].category_id }},
    subcategory_id = {{ group4.changesetArray[0].subcategory_id }},
  WHERE
    domain_id = {{ group4.selectedRow.domain_id }};

  UPDATE subdomains_info
  SET
    user_notes = {{ group4.changesetArray[0].user_notes }},
    category_id = {{  group4.changesetArray[0].category_id }},
    subcategory_id = {{ group4.changesetArray[0].subcategory_id }}
  WHERE
    id = {{ group4.selectedRow.id }};
COMMIT;

the only other option i can think of would be to make a workflow, set the trigger to webhook, for test JSON parameters you can use {changesetArray: [{user_notes: "test user_notes", category_id: "category_test_id", subcategory_id: "subcategory_test_id"}]}, then add 3 blocks (1 to update domain_information, 1 to update subdomains_info and 1 for the response), for the response you can do something like {results: [block1.data.result[0], block2.data.result[0]}. back in your app, you can add a new resource query and select Workflow then find the workflow you just made and replace the example test data that it auto-generates w the proper data. this would let you use the Automatic Run Behavior for workflows so on the advanced tab you can set watched input to like {{ group4.changesetArray[0] }} and every time that value changes the workflow will run automatically. do note, there is a limit to the number of workflow runs per month depending on your subscription tier.

Thank you for help! Will try :blush:

1 Like