I'm not quite sure how set up a query for this.
I'm trying to relate some tables using primary and foreign keys but it's new to me.
Main table "maintenance_requests" has primary key "request_id".
I have another table called "expenses" that I want to record all expenses in that are associated with a single maintenance request.
Expenses table has foreign key "request_id" associated with the Maintenance Requests table.
So you'll see in the screenshot, i have a table pulling in the maintenance requests, then I want to submit an "Expense Submissions" form that will add data to the Expenses table with the proper request_id in the foreign key.
Thanks,
Hi @dru_nasty ,
You can access the request_id from maintenance_requests like this:
table1.selectedRow.data.request_id
In your SQL that saves an Expense, just include the following for request_id:
{{ table1.selectedRow.data.request_id }}
Also, you should consider naming your table components with appropriate names so its easier to remember what is what.
So instead of table1.selectedRow you could have maintenanceRequestsTable.selectedRow.
Also, recommend naming any inputs appropriately too.
hth
I understand how to get the selected row data, i'm confused on how to craft the query to add the data. GUI mode SQL mode, do i need key value pairs for each form item, can i send a query object? Also, good call on the table naming, thank you.
Ahh, ok,
I am not sure exactly how to utilize the 'GUI mode SQL' query. However, it appears that the prompts are just asking for the entire form. Which to me means it would take an Object of key value pairs where the key would have to match the name of the column in the resource. But I am not positive.
Are you using a Retool Database and not your own separate database (just curious)?
Yes, I'm using the Retool database.
Gotcha,
It looks like the Changeset field allows you to either reference the complete form or build an object with name value pairs. If you need the request_id from the other data set included in the insert, you should do name value pairs:
{
request_id: table1.selectedRow.data.request_id,
field1: formField1.value,
field2: formField2.value
..
}
Worked great, thanks for the help! This is what I ended with
{
request_id: {{ maintenanceRequestsTable.selectedRow.data.request_id }},
vendor: {{textInput20.value}},
description: {{textInput21.value}},
hours: {{textInput22.value}},
cost: {{textInput23.value}},
bill_back: {{select3.value}},
credit_card: {{checkbox1.value}},
reimbursement: {{checkbox2.value}},
employee_id: 1
}
1 Like
