What is Upsert?
UpSert is a Coda.io function that permits to OR create a new row OR to update a row if there is a match. I had a working solution for a while based on a Coda Row ID. The logic was like this:
A form of Paperform was filled out and via Zapier the data was injected into a Coda table. When the subcriber wanted to update the info (via a link in an email sent via Zapier - > MailJet ) an Upsert took place based on a hidden field in Paperform that I had injected with a Coda Row ID.
The creation of the Row ID and how to prefill the Paperform is not the issue here, I guess the reader knows how set this up.
Coda and the RowID
When a form with a Row ID came back, the uspert took place. The handy thing was that inside Coda we had an automation running that created a duplicate of the RowID column. Due to this automation it was not a formula driven column so we could used it to set up a Match in Zapier. However after a while this solution did not work anymore.
I created two solutions as alternative:
- inside Coda a created a filter and an automation that removes once a week rows that received an update. This is the article in Dutch on the matter.
- with the great support of Zapier I created an alternative via Zapier that does the same as an Upsert. This solution does not require an automation inside Coda, instead you need two Zaps.
- one zap to create a new row (3 steps)
- one zap to update an existing row. (5 steps)
I focus on the most relevant parts:
- step 2 of the first Zap to create a new row : filter
- step 2 and 3 of the second Zap to update a row, the filter and the lookup
Below the main difference between the 2 zaps. If no row id returns from Paperform, create a new row, if we find an ID, we are about to update that row in the following steps.
Zap 01 is easy, we find no Row ID prefilled coming back from Paperform, we create a new Row. However if this field is not blank (it exists) then we have set up a match and to do so we need step 3, the lookup:
As Zapier support wrote me :
To fix this, I suggest using a Look Up Table in Formatter by Zapier before your Update Row in Coda step. This will allow you to match the IDs in Coda to their corresponding Row number. You can learn more about how to get started with a Look Up Table here https://zapier.com/help/doc/how-use-formatter-functions#using-the-lookup-table
The lookup enables the system to Find (step 4) the row, thus to set up the match. Once the match is found, we do the update, that is step 5 and we are ready.
Instead of one Zap, we now have two that do the job for us. It works fine, rows are updated easily and the tables to do not expand a lot.
The article in Dutch on this solution you find here
Best, Christiaan, Ghent, Belgium