Hi! Can anyone give an example of how to use "Update a record, or create a new record if it doesn't exist" for a simple CRUD operation? Which fields to insert into Filter by and which objects into Changeset (changesetArray or newRows or something else). There is no documentation!
1 Like
Hi Alex,
Welcome to the Retool Community Forum! Looks like there is two asks here.
-
To update a record that does exists, you typically want to filter by the most unique key attached to a specific entity. This is usually the primary key or id of an entity. You can reference the component and use dot notation to access the correct information like so.
Then to update a specific field, you'd select the correct field that you want to update from the left hand drop down. On the right you'd leverage the changeSetArray object to access to correct info. Because this is an array, you'd want to key into the object at the 0th index. Example below
-
To create a resource that does not already exist, you can select which resource you are trying to insert into, then the action you are trying to perform. Then you can select the parameters that match the qualities of that resource, then reference the components input value. Example below.
Please let us know if this was helpful or if you have any other questions 
Diego, big thanks for your answer, but how to use all these operations in case edit or add a row in an editable table (not from a form)? Table has only 'Save action'. Retool has Query action type "Update a record? or create a new record if it doesn't exist", but how it works can't figure out
1 Like
Hi @AlexSh, here is a step by step guide that goes over adding an event handler to the "Save action," which should run your update query.
On the other hand, once you add the "Add Row" action to your table:
Just add an event handler that will run your query to create a new record.
1 Like
I dont know if either of the above answers actually answer the OP's complete question. I have the same question, but these replies, while helpful, dont really hit on it.
@Diego_Chavez @Paulo
What @AlexSh is asking is how to configure the GUI query when using the "Update a record, or create a new record if it doesn't exist" action type? Data comes in from two different sources when you have the table editable and when you enable the "Add Row" Button. The data is either edited and displayed in the [table_name].changesetArray or it is a new row added that is displayed in the [table_name].newRows option.
His question, and mine, is how does he configure the query so that either the record is updated by the primary key and uses the changesetArray or a new record is created using the data from the newRows array.
3 Likes
"Update a record or create a new record if it doesn't exist" is designed to be used for a single record, hence the form. When using editable columns, users would be able to edit many rows at a time. Therefore, we should use two different queries if we use editable columns, one to run a bulk update after the edits are made, and another one to add a new row.
Is it possible to create a workaround to only use one query? Yes, but there is a tradeoff:
We could run an "Update a record or create a new record if it doesn't exist" on an event handler for "Change cell," but this can be expensive as this query would run 'x' amount of times per row, where 'x' is the number of columns on your table. In addition, we would have to refresh the table every time the query runs in order to show changes in real time. This would unselect the row the user is working on, bring the table scroll back to the top, and potentially change the table's sorting, depending on how the query set as data source is constructed and if the user has sorted the table using the UI already.
What if we run a single query when the user clicks on a new row? This would be more efficient than the approach above, but when users click on a new row, the query would also run before changes are made. This can potentially render unnecessary errors. Accidental clicks could also trigger the refresh and reset the sorting, impacting the user experience.
What if we use a single query, then using JS we iterate over the changesetArray or values of the changesetObject, and we use additional scope to pass the id of the record in case it is an update? This would be more efficient than approach #1 for a single row, and would prevent rendering the unnecessary errors from approach #2, but it's more tedious and less efficient than running a single bulk update query when multiple rows are edited.
For these reasons, the solution provided of using two different queries with editable columns is the most efficient and user friendly approach to the problem if we do not need/want a form.
2 Likes
That makes sense @Paulo, the workaround I have chosen based on this was to check the length of the changesetArray and to check the length of the newRows options and based on that and some logic trigger each query independently based on the desired action of the user. I can understand the logic behind what you were saying, but was initially hoping that the Update a record or create a new record would fit my use case and not require the workaround.
2 Likes
Thank you for sharing your workaround! 
1 Like
Paulo,
Can you provide an example of how to use the "Update a record or create a new record if it doesn't exist" GUI query when using a form? I have a form and i am trying to leverage the same form for either editing existing records or inserting new ones. Not sure how to leverage that query for both when using the form. Thanks!
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:
- 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
idof the selected row. If there is no row selected, it's a new record.
- A button to 'Create a new user.' Basically, all this button does is clear the table's row selection with an event handler (No row selected -> new user, empty form).
Here is an app for you to check these settings:
Update or Create record.json (27.8 KB)
1 Like
Paulo,
Thank you for the great example!
1 Like