How to restore your archive in Coda?
Using a button to retrieve your data smoothly
Why retrieving payroll data?
In my previous blog I’ll explained how you can archive data. We had 8 columns with data per employee and stored this set of data in a single cell per employee related row. Once the payroll data is archived, the HRM employee has max one month to make changes, while meanwhile she wants to keep track of events happening this month. It implies she has to be able to select the data set she works with: this month or the archived previous month. It is one or the other.
I wrote this blog two times.
My first approach was to generate a table with dates, holidays and percentages to modify in the last step the rows to put the archived values in place. It was almost up and running, but I noticed a specific type of failure that made me change my mind in how to approach this challenge. I abandoned the combi Addrow() & modifyRows() and made it work with only AddRow in even less steps and I took with me the learning of how to filter out the data per employee per row.
The inventory of the archive
Below you see the archive table and we count 8 elements in the column Archive. These 8 elements represent 8 lists.
Element 1 up to 8 corresponds with the information in the button that generates the archive, see also my previous blog.
Transposing Data
We have to respect this order and second we have to find a way to unpack the items in each of the 8 sub lists.
We start with a filter and this time we take rows that have a checked check box ✅. For your information, I created a button that relates to the active month and permits the user to (de)select all employees for the active month. On top, the checkbox get a reset via the Restore button to avoid complications, more about this at the end.
The filter logic that only takes the checked rows and lives outside the table, contains parts to replace the thisRow logica as described here.
The above screenshot shows the core of the solution we work with. Manually we write the numbers 1 up to 8 to define the positions in the main list. And next we distribute the values of the sub lists over the rows using DayNumbers. Only the first part won’t bring you far, see below
To get the position in a list per column we count the rows per employee and this count equals the days in the selected month, in our example 31. We get this number range via the function below. We take the date value for the month we selected (I use a named formula for that), and use it to get the last date in that month and we take the day number of that date via Day() and Sequence() does the rest.
The double Nth() you see in the formula permits us to transpose the data. As written before, first we ask for the sub lists via the first Nth() — and we use the numbers 1 to 8 — and via the second Nth() we distribute the items of the sub lists over the rows based on the days of the month. This all executes rather fast.
The last part in our case is to add percentages and holidays (both relate to an overtime calculation). This is a bit tricky. In the set up that creates the table DB Attendance we use the dates to create the table, thus to add rows. In our table the dates are part of the lists we unpack and via that route they help to create rows. We cannot have this job executed again to create rows. What we need are these already “available” dates. With available I mean present in the calculation, not yet printed on the screen. We get there in a — once you see it — rather easy step. We copy — paste the value that generates the dates into the formula we previously developed in the context of the blog on Over Time calculations.
The last part of this button is a ModifyRows() to un check all the check boxes in DB Archive to avoid any confusion later on. We thus have two actions and that requires a RunActions() in our button.
Practical Next Steps
Once we want to unpack all selected data to manipulate it in the main table, we need an empty table to start with. This implies we may have to delete rows, but not before we archived the data living in this table. Unpack means first pack the available data and bring it to archive, then clear table and finally unpack the selected data living in Archive. These kind of processes I document in my doc. I use the canvas column to store notes and keep track of implemented processes so I can tell my future self where to look. There is so much possible with Coda that documentation is the only way to keep oversight.
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.
