Zapier Upsert Row not working as expected

I want to implement a solution where changes in a row from Doc A are synced to Doc B through Zapier. For that I have the following setup:

  • In Doc A and Doc B there’s the Data table in each. These I want to sync.
  • Since Zapier can only trigger on new rows, in Doc A I have a dedicated Edits table where I copy rows from Data whenever they change (via automation or with button, doesn’t matter).
  • Zapier is set up to listen on new rows in Doc A / Edits table and upsert those rows into Doc B / Data table
  • The key for the upsert operation is the column ID.

Expectation:

Both edited rows (that have rows in Doc B / Data with matching ID) and newly added rows (that don’t have matching rows in Doc B / Data yet) are correctly upserted.

Reality:

  1. Existing rows are updated correctly, but newly added rows that have multiple entries in Doc A / Edits table are inserted without overwriting each other. I.e., if I add a row to Doc A / Data and then edit it, it will appear in Doc B / Data as two separate rows. See in the video below.
    It seems like when Zapier does batch upsert on multiple rows, it does not take into account the rows it has already upserted in previous iterations of the same batch.

  2. Additionally, Zapier seems to fetch rows from Doc A in reverse order. This leads to a problem where when upsert operation is applied, the former edit overwrites latter edits to the same row, and data is lost.

  3. Additionally there seems to be some lag with either Zapier or Coda API: as you can see in the video below, sometimes it takes to perform the second manual sync to fetch the last lagging row.

Need some assistance with this please. I’m doing this for a client.

I agree your setup should work - but its hard to dig into further without looking at your zapier setup - you can always reach out to us at support@coda.io so we can take a quick look.

in meantime, you could also setup automation on both ends/inside both documents to fix up your data, similar to how you have DocA/Edits - you could have DocB/InComing and then use Coda automation to modify/transform/delete that incoming data and put it in appropriate table in Doc B.

Hey!

Here are the docs:

Both are open for editing. But feel free to copy and setup Zapier for testing yourself (I just don’t know how to share my Zapier trial with you).

Zapier config is simple:

As for automation in Doc B / Incoming — will automation be triggered on Zapier-incoming change and not user-initiated change? Because I remember seeing it in the community that only user-initiated changes triggered automation. But if that’s untrue, then building around that could be sensible.