How to Archive in Coda?
Using a button to store a list of data in a cell
Our previous blog was about how to transfer the thisRow logic into a CurrentValue logic to make use of filters in a table on a canvas or in a button. In this blog we will use this logic again to show how you can store data in friendly format to reuse it later on again.
In the community you find various ways to archive data sets, the approach shown is thus not the only one, nor the best one, it is one of many. However I believe it is easy to understand and to replicate and does not use unsupported functions nor require pack logic.
Why do we want to archive?
The use case I am working on is a payroll management system. The Coda tables keep track of worked hours, holidays, sick leave etc. Early every new month the calculations are processed. However once in while you need to intervene and this is possible as long as the next month is not yet closed. In this window of about 30 days, HRM employees should be able to adapt for example planned but not consumed holidays at the end of the months. Meanwhile they want to keep track of what is going on this month. The solution is to store the data in a table and call it back only when needed. The HRM employee has access to or this month or previous month in her main table.
Step one: we create a filter
Well, the thing is we actually do not need a filter in this specific case. The table name always references the display column and this is the same one we use later on after we applied the filter. However using a filter will permit you next time and in other cases to apply this logic as well with only a few modifications fitting your specific needs. The filter outcome got a short name via WithName().
I used in this example the word ‘Root’ to replace the thisRow logic.
Step 02 getting the data to archive
We need to select some columns that contain per employee the data we want to keep. All these columns contain lists of data. We do not want all data in one column together but split per employee. In our example we have 4 employees and thus we need 4 lists with data that only relates to them and stored in such a way we can retrieve it.
In our source table the formula looks like below. A combination of a Filter() with a List() referencing all the columns (lists) we want to archive.
We need something similar in the button that first creates the data sets we need and second print it in the archive table. We follow the very same set up, nothing fancy, with one difference: we rewrote the thisRow into Root
Step 03 — bringing it all together
What follows next is so simple that it may confuse you. It does not happen often you have to store a larger data set into one row, but once in a while you need to set it up you have to restrain yourself not to over complicate it. It is what it is: based on a filter we have 8 lists within one list.
Again I make use of a handy practice I wrote about here to make use of the AddandModifyRows()
What is left are the few columns I need later on to restore the data. I need to know the period, therefore the first day of that month is good. I need to know the employee, maybe one day I only want to restore data for a few employees and not all and in that use case I add a check box and filter in my — still to create — restore button on the checkbox active. The employee logic here is a classical one, I only need the name of the employee once. Would I have left out the last part — Nth(Employee) — it would result in 30 times the same name. The alternative is what you see below, adding First() since all names per filtered group are the same and the advantage of First() is that it requires little processing power.
The data set containing 8 lists per employee goes to the column Archive.
In my next blog I’ll write about how to restore this data. I have never done this before so it is a little adventure for the days ahead in between other jobs.
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.
