UPSERT only inserts

[FlorVk]

Hey all!

I have a problem with my bulk UPSERT on my table. The table consists of a editable checkbox and a dropdown filter, which controls the customer selected in the view. The checkbox is the main component to the UPSERT.

This is the table:

Now the problem is as follows: as the checkbox is selected and saved it will save and insert a new row in the database as intended, but if that same box is unselected and saved again it will create a new record and skips over the already made record row.

This is the transformer I have for the UPSERT:
With "overzicht_filter" the drop down selection and CMTableAll as the table. Module_on is the value of the checkboxes as a boolean and module_id is the module name in the table.

This is the UPSERT query to loop over all the changes in the transformer:

I believe something goes wrong with the primary key, but am left unsure. Is there a way to fix this or an easier way to do the upsert?
I will gladly provide more information if things are unclear.

Thanks!

[Tess]

Hi @FlorVk Thanks for reaching out & providing all of these details!

It looks like the primary key is 'id.' For each object in the array of records that you want to update, there needs to be an id defined, but it looks like your logic is only working with customer_id and module_id.If there is no id key in the object, Retool will assume that it is meant to be inserted.

In the case of the below data, the query should be updating the row with id = 1 and inserting the second object as a new row because it doesn't have an id defined.

[{id: 1, module_on: true, module_id: 123}, {module_on: false, module_id: 1234}]