Save data from two diff forms to mysql db

Hi i have the following app in process, basically its a stock transfer creation, so i will move Product XYZ from warehouse A to warehouse B, and the right table its a temp working table in mysql to save current products before creation, and when i click Green button (Save & generate) i should save all info like: date, status, ref number, warehouse from, warehouse too, and the info of the added product in the right table. And all that will be saved to 4 diff tables in mysql, how i can achieve that?

also your opinion, the left table it can be like hidden? so only show product when i search using the search field? right now im query select * all my product table, but that will be bigger like 100k products i dont want to show any product, like this video where it uses a select box and then user modify quantity and so on:

when i found a product it always put quantity 1 (how i can do that?)

pd: also im not sure how to control, if i have 2 users at the same time creating new stock transfer, if im using one tmp working table in mysql, app will be crazy when it saves :frowning:

thank you

Hey @agaitan026! I have a bunch of questions for your questions :sweat_smile:

For your first question, you’ll need 4 separate queries to write to 4 different tables if they're in different databases!

For your second question, are you asking if you should hide the left table? Seems fine either way, and your point about not wanting to load a lot of data in is great. Retool performs much better with less data pulled in.

For your third question, are you asking how to set a default value on a component?

For your fourth question, how are both users working at the same time? Are both in preview mode of your Retool app?

1 Like

yes i solved this issue by using another gui :stuck_out_tongue:

will look like that and for your last question.

That was because what happens if i got 4 users generating different purchase orders or invoices etc, what i use was localstorage and will user the current user id from retool to filter results and add that ui as purchase_order_added_by_user XXXXXX

1 Like

Actually you can do it with a single query:

  form1 AS (INSERT INTO table1...),
  form2 AS (INSERT INTO table2...),
  form3 AS (INSERT INTO table3...),
INSERT INTO table4 (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
1 Like

^ thank you so much for adding that, @Mendy! I had (incorrectly) assumed that @agaitan026 was referring to 4 tables in different databases :person_facepalming: