Bulk insert records with foreign key

[MFP]

Hi,

I'm trying to insert records in 2 tables

• 1 records into a parent table 'ecriture', say
  [ { id : xx , comment : "some comment"} ]
  with the id being defined when record is inserted.

• Several records into a children table 'lignes_ecritures', with foreign key to 'ecriture', say
  [ { id : aa , amount : 33 , ecriture : xx },
  { id : bb , amount 44 , ecriture : xx } ]
  with the ecriture foreign key being the id of inserted 'ecriture' record

The records in the children table are stored in a variable 'currentLignesList'
like
[ { amount : 33 , ecriture : null },
** { amount : 44 , ecriture : null } ]**
since ecriture FK is unknown before the 'ecriture' record is inserted

When I trigger the insert query, it works fine, but of course the FK stay null :

I have tried additionalScope :

But this does not work.

Any idea how I could bulk edit the FK either in :

• the data array 'currentLignesList' once the parent 'ecriture' is created
• or with passing a correct additionalScope

Thanks in advance

[jg80]

Hi @MFP - similar question and resolution here:

Insert 2 records (2 tables) with linking foreign key

Hi, 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 - …

Take a look and let us know if you need any further help!

[MFP]

Hi Jg,

Thanks for looking, but the question is 'similar', but not same.
In this post, the question is 1 parent 1 child, so it's fine.

My problem is to address several children records, which make things quite different ...

[jg80]

Same concept; you need to map in the id to the children objects and pass the whole updated set as additional scope to your bulk insert.

//Code to play around with
// defining the parent key for code
let ecriture = [ { id : 01 , comment : "some comment"} ];
// for you, it will be generated by the insert

// fake child record set
let children = [ { id : 'aa' , amount : 33 },
{ id : 'bb' , amount : 44 } ]

let childrenInsertArray = children.map(obj => ({
  ...obj,                     // the child object
  ecritureId: ecriture[0].id  // the FK
}))

return childrenInsertArray //child record set with FK

To pass the updated array, make sure you have the additional scope variable setup in your insert, and then run:

insertListeEcritures.trigger(
  {additionalScope: { currentLignesList.value.map(obj => ({
                        ...obj,
                        ecritureId: ecriture[0].id
                      }))
  }
});

OR (if you create the variable in your script)

insertListeEcritures.trigger(
  {additionalScope: { childrenInsertArray } }
);

Insert code might not be 100% depending on how this are setup in your App, but the "code to play around with" should help you get there.

[MFP]

@jg80

let childrenInsertArray = children.map(obj => ({
  ...obj,                     // the child object
  ecritureId: ecriture[0].id  // the FK
}))

I Assume if my children structure is
{ id : 'aa' , amount : 33, ecriture : ecriture[0].id }
the exact syntax would be :

let childrenInsertArray = children.map(obj => ({
  amount : obj.amount  ,                   
  ecriture: ecriture[0].id  
}))

as I understand this, I was wondering if there was a way of modifying directly the initial 'children' array with something like forEach.
(which I could not figure out, of course) ...

[jg80]

Are you trying to update the currentLignesList variable? If so, you could use:

currentLignesList.setValue(
  currentLignesList.value.map((obj) => ({
    ...obj,
    ecriture: ecriture[0].id,
  }))
);