I'm fairly new with Retool concept, and I'm not proficient enough to keep things simple.
I have 2 tables (say, 'tennant', and 'tennant_account' which hold the foreign key of corresponding 'tennant')
When creating a tennant, I'd like to create the corresponding account at the same time.
I need to have the uniqueId of the inserted 'tennant_account' as Foreign Key to populate the 'tenant' record.
How would I do that with as little clutter as possible (possibly in a Retool resource Query - as opposed to JS Query ...) ?
The simplest approach that I often use (open to suggestions for improvement!) is to create two insert queries, one for each table (I'll call then "InsertTennant" and "addTennantDetails").
For the addTennantDetails query, use an "additional scope" variable in the changeset object (something like {{dataFromTennantInsert}}). The variable it will lint as undefined, but we will get to that.
Set up InsertTenant as needed/typical, but then add "execute script" as a success event and put something along the lines of the following into the script:
// assumes a single record insert from InsertTennant
// add a for loop otherwise
const tennant_account_id = InsertTenant.data[0].tennant_id
const tennantAccountDetails = {
//your object details will go here...
fk_field_name = tennant_account_id,
//...maybe some more fields...
}
addTennantDetails.trigger({
additionalScope: {
dataFromTennantInsert: tennantAccountDetails
}
});
Then when you trigger a (successful) Tennant insert, it will automatically trigger the details insert using the ID from the original insert (and whatever additional details you give it) as the {{dataFromTennantInsert}} in the body of the addTennantDetails insert.
Within your addTenantDetails query, you can refer to the created tenant as addTenantDetails.data?.result[0].id. You can simply configure your query with the GUI interface/Insert a record and in the foreign key field just insert {{addTenantDetails.data?.result[0].id}}
Your InsertTennant query can trigger addTennantDetails on success and you're set up.
For the fail scenario: You can create a delete query which triggers upon failure of your second query and use the same variable to delete your tennant - or make it show a modal window for user to actually generate the tenant details.
