For a few different Packs now (Copper, QuickBooks), I’m running into the following workflow issue:
I sync-table some data from an outside SaaS tool
I make some changes in the outside SaaS tool, that I want to update more or less instantly in Coda (so that users can take further actions, or so that Automations can be triggered)
This isn’t possible, because you can’t…
Tell Coda to refresh a sync table via formula, automation, API…, nor
Push data into a sync table via Zapier/Integromat
So you’re kind of left choosing between Zapier and Packs, and accepting tradeoffs:
Sync table is guaranteed not to be missing anything, but doesn’t update fast
Zapier updates fast, but may miss changes, fall out of sync without warning, etc.
But I just thought of somewhat of a workaround:
Create Actions in your Packs that do not in fact take any action, but instead just return a sync table row. You’d call them something like RefreshCustomer()
Users could attach this to a Button column if they want to be able to manually refresh a certain row
When something changes on your outside data source, have Zapier/Integromat fire a record into a support table (could be a single-column table with Customer ID), triggering an automation to call the action on the relevant row.
Thoughts?
*Important caveat: This doesn’t get us around the problem that Actions cannot return objects with dynamic schemas. So if you have rows with partially dynamic schemas, you’re still out of luck, in that even actions that have impacts on your data will not show updates to dynamic columns until you refresh the entire sync table
Edit Sept 2022: Dynamic schemas are now supported in actions! However see below for a new caveat: there is currently a bug preventing references (Lookups) to other sync tables from working correctly
@Nick_HE, I’ve trying to update a specific row, and I’ve followed the approach described here (Approximating two-way sync - Coda Pack SDK). The issue I see is that the updates rows stop matching any filters - i.e. they all of a sudden disappear from a filtered view.
Is what you’re describing different than what’s in the link above?
@Dan_Guberman That shouldn’t be happening. What data is in the relevant cell before and after the update? I think it’s more likely that you have a different issue going on (e.g. the update formula isn’t returning quite right or something). In theory if you have a button that updates “Status” column from “In Progress” to “Done”, but its date is still “June 17th”, it should still appear in a table filtered to “June” for example. (Note: you don’t even have to send “June 17th” again as part of the update; if you send null/undefined for that property, or you don’t specify that property, it’ll just keep the “June 17th” value it had from before)
I was digging a bit deeper (was just about to post a new topic).
This happens when it’s filtered on a reference field. So, say, if I have 2 tables: Employees and Departments, and an Employee has a reference field to Department, then when I update the Employee record, it breaks the reference, even though I set the same reference data (id and name for the department).
That’s causes the filter to exclude that row.
So, the real issue is how to keep the reference field during an update without breaking it.
If I explicitly delete that reference property from the updated object - that works, but that’s a pain to do across the board, and it doesn’t allow updating the reference itself (like changing departments) - I’d much rather return the full object that originally created the row.
There is indeed a bug with references. I’m doing exactly this in my Sync Tables Pro pack (the RefreshRow() function) and I just omit the references for now. It’s not ideal but it’s better than not having the sync action at all, and hopefully Coda fixes this soon enough.
@Paul_Danyliuk - thanks for confirming.
Do you know if this issue is being tracked, and is there a way to upvote it?
EDIT: I understand now what this warning from documentation means - I think it literally tells us that reference fields cannot be updated by a formula, even if it returns the same object.
EDIT2: Apparently, it doesn’t do a deep-merge.
I have an array items of objects that each has a reference to other tables. I can’t just delete the reference field in each item; I need to delete the entire array items. Pretty big limitation on refresh, unfortunately .
// cc: @Eric_Koleda
@Dan_Guberman and @Leandro_Zubrezki - We don’t currently have a public issue tracker, but we are tracking this internally and I just pinged the team about it. The fix is unfortunately blocked by some larger infrastructure changes. Those changes are making some progress, but it’s been slow and had some setbacks. Unfortunately I can’t provide an ETA for a resolution.
@Eric_Koleda, do you have any update on the potential timeline for the fix for this? It really make it difficult to work with Coda when any update to the table requires a full refresh - which is often very slow - or else the references are broken.
Hi @Dan_Guberman - Unfortunately there is no news on that front. Unfortunately the fix we had planned for the references wasn’t viable, and the work has been put on pause for now.
In general though, are you more interested in making references work after an action updates a row, or are you just after some fully functional way to refresh individual rows of a sync table?
Thanks for the reply @Eric_Koleda. As for your question, yes - mostly it’s when some action updates the row - for example, creating an Account for a User, the User table has to be fully refreshed before it properly references the new Account. But also when updating externally, a partial refresh of individual rows temporarily breaks in sneaky ways all the filters that use the reference field.
I’m currently using the ConvertKit pack. When somebody submits a Coda form, a Coda automation triggers the ConvertKit pack to send a welcome message to the user. ConvertKit then adds the subscriber automatically. I now get stuck because I can’t refresh the ConvertKit subscriber table using formulas and automation. I will have to wait up to an hour for the table to automatically refresh in order to tag the users, as I need the subscriber ID.
The current workaround is to create a time-based automation that checks every hour to continue the tagging action, which could lead to a two-hour delay. On top of that, I have unnecessary automation running every hour every day.
I’m also running into this limitation. I get that this could tax the server running the sync, and would understand if I had to do something special like call ModifyRows after parsing the webhook body, understanding I run a risk of de-syncing until a full update occurs.
“Refresh pack table” is available as a button action, which can be “pushed” by automation:
create a named button somewhere in doc with the onClick action of “Refresh pack table”
from the automation, add an action step to: “Push Button”
If you need to wait before “Push button”:
create another automation with trigger “row changed” (ideally convertkit pack would add a modified timestamp), with action “Push Button”
In my case, when the “Push button” refreshed my syncTable:
In that table, I have a column that uses another pack formula with some parameters. I need this to re calculate after pack table refreshed. Like “onRefreshComplete” trigger (which does not exist).
So I updated my sync table formula to return a “last_modified” result, which I then provide as input parameter to the column formula. The “last_modified” value is always different, so forces recalc.
.