When you see a spreadsheet logic
You might want to replace it by something smarter
Last week I worked on a project that related months and employees to keep track of their paid holidays. The set up I received in Coda looked like below and the maker asked me if this was the way to move forward.
One of the appealing aspects of this approach for any maker responsible for filling out such tables is speed. You only create 12 columns and you select per row the employee. That is it. Second you have an overview that speaks to you. Our visual system deals very well with this kind of data presentation. Both reasons make it understandable that makers apply this logic, besides the fact that in any spreadsheet this is ‘how you do it’ and we all are biased by this logic.
The alternative you see below and this one feels like a lot more work to set up and is visually less appealing. This table is only for 4 employees, what if you have 40 or 400. 40 → 40 * 12 rows and 400 → 400 *12 = 4800 rows. A better doc and table design should not result in more manual work…
The solution is — as you might have guessed — the usage of a button. This time I wrote a short & speedy code snippet that references the DB People and DB Months tables.
In this button that live outside the table. I create two virtual lists, one with the numbers for each month (1–12) and one with a number that references each employee (1–40).
The next step is to ask for each row in the tables I reference. The CurrentValue as outcome of the FormulaMap () I renamed using two times WithName() replace each time the thisRow logic in the tables I reference. Below the button and the table I fill out.
The main reason for keeping the button outside the table is speed. You won’t notice the advantage with a limited number of rows, but once your tables grow, inertia bit by bit increases. Coda is a wonderful solution, but performance is one of the major chapters they have to keep focusing on to make the product loved by SMB (besides Permissions and Print (Pdf)).
The next step is keeping track of all the holidays per employee. We won’t do this by hand. A separate table will deal with this issue per month per employee and the outcome of that process will be integrated in this table. Our starting point is zero holidays per employee, that is the default value that can be overwritten by granted paid leave expressed in days as a number. That means a few interventions per month and goes fast. The table keeps track of the past and helps to plan the future.
This blog shows that once you go for the Coda approach you should not be afraid of the extra workload and rely on Coda tools to free up your time instead.
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.
