Custom Row ID, or Row Upsert based on a specific column entry?

1

This may be more like two questions in one. Firstly, I suspect this is not doable, but is it possible to provide a custom row id for a table?

If not, my bigger question is whether it’s possible to provide a certain row field as the “upsert column key”.

I’m sync data from a db where my data is indexed by creation-time, and I’d like to be able to properly upsert by providing that index since I presume to have no way to know the row id.

If I’m doing bulk data conversion to Coda, the only way I can think of is to upload all the data, wait until it’s done, then do ANOTHER api hit to lookup all the data on the table and then read the row-id’s so that my local DB has extra data about each document’s row-id for later upserts. This, however, is obviously not idea.

2

Just reuse whatever unique index you have in your existing DB. Is it an SQL autoincrement primary key? A unix timestamp? Just put it in a column in Coda and upsert based on that.

It’s doable by Zapier (you choose a column / multiple columns to identify the row to upsert to), hence that should be doable with vanilla REST API calls as well.

3

They’re unix timestamps. I’m confused by the syntax. I have a column called ID that I was putting the timestamps into.

If I do this:

“keyColumns”: [
“1457577441421”,
“1457722873514”
]

I get this a 400 error

“message”: “Could not find column "1457577441421" within the table.”

If I provide the column ID of the “ID” column, It just inserts, but doesn’t upsert. How do you link the upsert column TO the actual unix timestamp data?

4

That field should be the column name or id, not the value. You have to pass the timestamp value to the actual row being inserted. Check Al’s tutorial (esp. lines 49 and 52)

5

Unfortunately I wasn’t able to divine the proper json from that article and so i’m still confused on the actual syntax, at least when you say “pass the timestamp value to the actual row being inserted”.

The following json just adds a row where the ID column is 12 every time as an insert, not an upsert:

{
  "rows": [
    {
      "cells": [
        {
          "column": "c-J-YhZPLVAH",
          "value": "12"
        }
      ]
    }
  ],
  "keyColumns": [
    "c-J-YhZPLVAH"
  ]
}
6

What I meant was that the timestamps should go in the "value", where you put the "12". But you probably figured that out by now.

The code looks good to me, hard to say why it’s not upserting without seeing the rest of it. The only thing that stands out is maybe the fact the only column you’re setting a value is the keyCoulmn itself, but that shouldn’t be a problem.