I'm stuck with using GUI mode to "update a record, or create a new record if it doesn't exist".
As per the screenshot, you can see that if the pottery_image_id is null, I'd expect an insert, whereas if it's not null, it would perform an update. Updates work, but inserts don't.
What's the proper value in the Filter by settings that would trigger an insert rather than an update?
pottery_image_id of null is what's happening now (and what I expected to work). Using a different id that isn't in the data (like -1, for example), just inserts a new record with -1. The pottery_image_field is set to auto increment and is using the Retool database.
@sammyw commenting to hopefully get some attention to this! I just posted something about this myself over here. All the other threads I've seen about this don't seem to have a good solution.
The workaround that I've been using so far is to use a transformer to parse out updates vs inserts, then do two separate database queries to do updates and inserts. Annoying, but it does the job. Hoping Retool fixes this soon.
Appreciate your response! Since I wrote the question, I abandoned the approach because I couldn't make it work and found another post that suggested using bulk upserts instead. Seems to work ok, although I'd rather get the above approach working as it seems to be the exact use case for it.
And in order to make use of the insert query by both an add and edit form, I'm updating a dict variable on submit that then the bulk upsert query uses. A little awkward, but it works.
Hey @sammyw Looking into this, as I believe your first use case should work. I'll keep you updated on the status as I confer with the eng team responsible.
