Update and if not exists, insert a row

[Maartenvdv]

Hi,

I have followed these steps:

How to use "Update a record, or create a new record if it doesn't exist"?

Definitely! We need a form and pre-fill it with data from the selected row. We can do this by adding a default value to the inputs: [Screenshot 2024-08-26 at 2.33.31 PM] The query to 'Update a record, or create a new record if it doesn't exist.' The way we'll use it is: If a row is selected, we will update. If a row is not selected, we'll create a new user. How does Retool know this? It's all in the 'Filter by.' We can set it up to find the record to update by the id of the selected row. I…

To create a form that can both update an existing record and create a new record. When I create a new object and set the identifier to an empty string, 0 or null, it returns an error.

Cannot insert explicit value for identity column in table 'budgetLines' when IDENTITY_INSERT is set to OFF.

I am surprised that the identifier is added to the insert statement. It is used to filter, but it shouldn't appear in the insert query? These identifiers are usually auto increment, so not known in retool before the insert.

Even if I actively delete the identifier from the form data object, in the background retool seems to still add the identifier back into the object.

{{ Object.fromEntries(
Object.entries(addBudgetLineForm.data).filter(([key]) => key !== 'id')
) }}

another thing I tried was to hardcode in the filter that the identifier is null, but that doesn't help either:

{{ addBudgetLineForm.data.id == '' ? null : addBudgetLineForm.data.id }}

Any suggestion how to deal with this?

[Maartenvdv]

well ok, i made it work by not using that specific update or insert function, but to split it, and to have a script on the form submit button that decides what function to call.

Still think this is some sort of bug or at least unintuitive behaviour.

[Jack_T]

Hi @Maartenvdv,

Apologies for the unintuitive behavior. I am glad you were able to get it working!

I was going to ask what resource are you using for the DB and these queries, as we have an option for a 'Bulk Upsert' query in the GUI for some resources. A bulk upsert will check to see if an existing record/row of data is in the table with a matching unique ID and 'update' the existing data. Then if there are no matches found it will insert the data.

We have this built out for use cases like yours so that users do not need to run a 'find' query and then need to build out logic to either update or insert.

Let me know more about the DB/Resource you are using so I can check if we have an 'Upsert' option and potentially request one if possible. I am definitely confused as to why the query is insisting that IDENTITY_INSERT need to be on and filled out

I am not sure if this is a schema rule from the Resource/DB or if this is uniquely coming from Retool, in which case I would like to reproduce the app to check with my team on if this is a bug.