Insert 2 records (2 tables) with linking foreign key


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 =[0].tennant_id
const tennantAccountDetails = {
  //your object details will go here...
  fk_field_name = tennant_account_id,
  //...maybe some more fields...
  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.

Good luck!

1 Like

A slightly different version from @jg80 :

Within your addTenantDetails query, you can refer to the created tenant as[0].id. You can simply configure your query with the GUI interface/Insert a record and in the foreign key field just insert {{[0].id}}

Your InsertTennant query can trigger addTennantDetails on success and you're set up.


Thanks jg80 and Miguel.

Capture d’écran 2024-05-17 à 16.02.02

Miguels solution was exactly what I was looking for ...
I learned the 'data?.result' keyword. :slight_smile:

But I'll keep in mind the additional scope, which might get handy at some time, and I'll RTFM ... :roll_eyes:

Now I have to figure out what to do when the second record insert fails while the first is inserted ...

1 Like

Glad it worked!

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.


I was trying to get down that road, but still struggling with code and unexpected results ...
(you can't mess with Bubble :roll_eyes:)