Clearing Sync Table when Query Changes

Making Packs
1

I have a pack which accepts a query to a database.

The pack returns the data to a Sync Table. This table additionally has several AI columns with prompts related to the retrieved data.

When I change to a new query, the database content changes right away but the AI content can take a while to change to reflect the new query.

I don’t mind at all if it takes a bit of time for the new AI queries to populate. The problem is - the old AI responses still remain during that time. It would be much better if the AI columns were blank after I refresh the table and before they get repopulated. Is there any way to achieve this?

2

Hi @Richard_Kaplan - I suspect the cause of this behavior is that the row IDs are the same regardless of the query, so Coda see’s the values in row #1 changing, vs seeing a new row. If you want Coda to see a new row than you’ll need to find a way for the two queries to produce different row IDs (but that are consistent over time).

The pattern we use for the Snowflake Pack is to have the user specify a list of column values that will form the unique ID of the row. That way the row IDs are based on the data returned vs being hardcoded.

I used a similar approach in my BigQuery Pack, which you can take a look at here:

3

Thanks @Eric_Koleda - I think that is getting close to what the problem is but the situations are not totally the same.

Every query I do is unique and in fact the actual sync table columns all change just about simultaneously - so that is not a problem.

The problem is that I have AI columns which are based upon the sync table contents. Those columns were added via the Coda front-end UI; they are not populated by the pack as they are AI queries. But since it is a sync table there seems to be no front-end formula which can be used to clear the contents of those AI columns before they have a chance to re-populate.

Is there some front-end formula to clear these AI columns that I am missing and which works with a sync table?

Or even better - is there a way when I change the query that the pack can first completely clear the entire sync table of all values - including the AI values that the pack did not populate?

4

Or even better - is there a way when I change the query that the pack can first completely clear the entire sync table of all values - including the AI values that the pack did not populate?

That’s what my answer is providing a solution for. If the new query returns new rows, then the other attached columns will be blank for those new rows. What I suspect is happening is that each row is re-using the ID from the previous query, so Coda sees updates to the existing rows, and therefore retains the values in the attached columns.

Can you provide more information for what you are using as the idProperty of your schema and where that value comes from?

5

@Eric_Koleda

I see what you mean - I think you are exactly right

I have 3 similar sync tables. Two have a unique database key and the change in queries works perfectly as you suggest.

The other does not have a unique database key set up so the keys are just incrementing starting with zero - and that one has the problem I described.

Your explanation makes total sense.

I will revise this.

Thank you.