Tip to Coda AddOrModifyRows
Work smarter due to a clean approach
In many of my larger projects I use canvas buttons to update rows or if the match value is not yet present, to add a row. In the Coda Community it has been argued that one should better not use AddOrModifyRows because:
The reason for that is that you’re more flexible with what you can write in an explicit conditional than you can in an AddOrModifyRows clause; e.g. in the example above you can make use of first pulling in the row to edit (if exists) first and then reusing it, rather than doing the full filter over the ever-growing dataset of All Orders each time you press a button.
This argument makes certainly sense in a context whereby you add a serious amount of data to your table on a regular basis. The suggested approach will first check if the data is present, if so it modifies and if not it adds.
In this blog my focus is on smaller tables (that remain often for a long time the same and only now and then you add a few new rows. In this scenario it is okay to use AddOrModifyRows.
The Match Value
The function has a a formal logic as described by the Coda team and the key element is what they call the expression. I call it the match value.
The match value is the key. Does the match value exist, an update is executed and in case not, a new row is added.
In my example I have a table with employees and an other one with attributes like salary or holidays. I need to update the attributes for employees every month. The match value is the employee for a certain month. All these values live in the DB People. Based on the the month (like Sept 2022) and the name like Jessalyn Jansen we can construct a match.
Setting up the match value
Below a bit of context to make it easier to to follow. Some workers enjoy a prepayment and we keep track of that in a specific table.
The match value is not visible in the above and lives in a hidden column. We first define the match value and next we apply it. In the above it is a mix of the Row Id (hidden) living in the source table, the name of the employee and the month we work with. We use the month also in the Name value. Below how this looks like.
As you can see in the above, I created the Match value via format and named it using WithName().
The named function I used twice. First as key and second as value to check for. This looks clean because the wording corresponds with the meaning and functions and because I defined the function separately. In case I need to alter the key, it is easy done and only at one place. The first part of the match is .Contains() and this function permits me to keep the lines readable . I noticed other people writing Match = Matching, which will work as well, but I try to limit the use of these logical operators to specific filters. A short note on the side, once you start using “ = “ you see only one option, while Contains() opens up your mind. I’d rather have an open mind.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
