Coda.io Cleaning up Rows
This article is based on a text I first wrote in Dutch, the language I mainly use to write about Coda related issues .
What is it about?
I have integrations between Paperform and Coda via Zapier. Submissions are injected into Coda. When a new Row is created, an email is sent (MailJet) . This email offers a link to a prefilled paperform that itself contains the RowId. Users can adapt their data in the Papeform and the updated info is sent to Coda. Based on the returning RowId we can relate the update to the subscriber in the table. Previously I used the Upsert logic to keep data aligned in Coda. Early October 2020 however, I noticed that Upsert based on the RowId failed (however it still worked and works with email as match value). In response, I decided to create two solutions:
- Keep data aligned via a more advanced usage of Zapier to end up with an Upsert alike solution using two Zaps instead of one. This works fine.
- An easy Zapier integration based on Create New Row’ and a clean up of outdated rows in Coda via a button. This blog is about this solution.
The set-up of the automation between Paperform, Zapier, Coda and MailJet is not discussed in this article, I explaind the set-up overhere. In this article we work only with the RowId, however when you send out a link with prefilled data, you’d better add the Paperform ID as second Match value to avoid that creative people emptying your database by playing around with the RowId in the URL.
The path we follow:
- Check 01 — Rows with updates , we select the last update
- Check 02 — Rows without updates — we select this not updated row
- Check 03 — Rows linking to deleted rows, keep the most recent version
- Solution — integration of previous steps. Unchecked Rows are ready to be deleted via a button or an automation.
thisRow.[check 01] OR thisRow.[check 02] OR thisRow.[check 03]Below the table we work with. It contains the columns required to find the solution. The button contains an easy delete function, nothing special. The name of the table is TT (ThisTable) to allow the reader for an easy copy paste.
DeleteRows(Filter(TT, Solution.Containsonly(false)))The Rowindex is not used to calculate . It shows simply the amount of rows we have in this table, it uses this Find variation:
Find(thisRow,thisTable)
Check 01 — last updated row
We split our solution into two columns. First we show what the number is of the last Row (15) and second we turn that value into a checkbox on that row.
Last Value
thisTable.Filter([Retour ID].Contains(thisRow.[Row ID])).[Row ID].FormulaMap(CurrentValue).Last()Check 01
thisTable.Filter([last value] = thisRow.[Row ID]).IsNotBlank()
Check 02 — keep not updated rows
A formula to check the rows that have no updates elsewhere:
thisRow.[last value].Contains(thisRow.[Retour ID])Check 03 — relate to deleted rows and keep the updated version
For me this was the most complicated one and it took me quite some time to figure out how to deal with rows that are no longer part of the (sub)set. I solved the puzzle by breaking it down into three parts:
Step A
(thisTable.Filter([Retour ID].Contains([Row ID])).[Retour ID]).Contains(thisRow.[Retour ID]).Not() AND thisRow.[Retour ID] < thisRow.[Row ID]Step B
thisTable.Filter([Step A]=true and [Retour ID] = thisRow.[Retour ID]).Last()Check 03
thisRow.[Step B].Contains(thisRow)
Step A is about two things. Relating to rows no longer present via the Contains.().Not() logic and second via the part after AND we make sure we solve in this step only the problem of previous rows. Hence, the Retour ID must be smaller than the RowId. In step B we look for the latest version of rows that link to deleted rows and last but not least in Check 03 we convert this knowledge into a checkbox.
The solution
Easy peasy way of bringing the pieces together:
thisRow.[check 01] OR thisRow.[check 02] OR thisRow.[check 03]Below the result. In a first edition of this post, I did not take into account deleted rows. Their relevance did not appear to me until the moment I hit the button. The result was a correct working table, but with some rows unchecked that linked to a previous and now deleted row. As you can imagine, a second push would delete rows with valuable data…. The solution in this blog takes this ommision into account and solves it in Check 03. After we hit the button, all remaining rows are checked, unless we add new rows with updated information.
This has been the most complicated solution I created in Coda so far. It shows that one like me without any background in coding can get the job done. Maybe others have more elegant, shorter and simpler solutions. I welcome their feedback and suggestions very much!
Maybe we even have one day inside Coda a function that does the job like UpdateTable() with a formal structure that identifies the RowID, the Returning ID and other elements. Everything is possible.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done.
