How to Coda rounded payouts
When cash is king and you need rounded numbers
In countries less oriented towards the digitalisation of the local currency people are (partly) paid out cash, thus in coins and banknotes. To make it easier for the HRM department to follow-up on the payments, it has been decided to only payout rounded amounts. However when it comes to salaries people have to paid out every cent in the end. To keep track of the residuals we make use of a table in Coda to help us out.
Our starting point is a salary that is already free of all sort of duties like advances and installments based on court orders. See this blog ⤵️
The table we need
Every month we bring in a table all the amounts to be paid out by the company to start the calculation taking into account the history and to prepare for the next month.
To do so, we filter this table on our key value : employee. Since we work with a canvas button and not directly in the table, we have to replace the so handy thisRow logic for something alike as explained in this blog:
The code I created as a starter (on the canvas for fast editing) looks like what you see below and if you look good, you see the alike name in brackets. These are the sub lists in the main list using the value living in the display column. This means that changing the display column will effect what you see. Below theThisRow I developed for this table living as a canvas formula.
As you may recall, when you use a thisRow reference in a table it is or the display column (thisRow) or it is the function thisRow.columnName you use to references the item (in this row) living in the list as described by the column name. Here the column name is Employee.
Once we have these sub lists, we can start calculating to get the result you see below. Note that all the calculations take place outside the table. This is on purpose to keep the doc fast and light.
Below the steps:
- Mimici the thisRow logic
- Ask for the Balance values via a filter (leaving out this month)
- Take the last one of the values living in the list Balance (since we update this value each time, it is always the most up to date)
The second step shows also how important TheThisRow formula is.
Once we have these values on our canvas we need two extra steps and frankly speaking, it took me far too long before I got here. Let me explain why it took me so long.
Step one is to see that Coda cannot create a new row based on an empty list item, thus on a list item that only contains a comma. I first and wrongly assumed it would generate an empty cell, because the item was empty. Then I tried to solve it using an Ifblank(0) in the previous step, but that failed because you cannot manipulate empty values in sub lists this way. This is only possible if have a single value like here via Last() or when you use Sum() or Count() .
Without the part FormulaMap(Currentvalu.Last.Ifblank()) you only have new rows in your table for existing employees, thus employees that make this list not empty and in case you have new employees (for which we have no history, thus no data yet), you have nothing. This is not the way.
I understood that the item should be something and I went for obvious reasons with a zero. However if I replace every empty list item with a zero, I end up with too many zeros. To solve that problem we slice the list into the amount of employees we are dealing with.
As said before it took me quite a bit of time before I saw this solution, actually too long according my standards. What did not help was that the obscure red notifications (warnings) you get are not explicit, they show something goes wrong without telling what goes wrong, nor do they contain advice. I assume that an experienced developer would have noticed this earlier, but I don’t have a coding background and learned myself how to Coda.
I could have solved this puzzle in less than a minute would I have created a function inside the table. That goes really fast once you are used to the more complex manipulations via buttons and named canvas functions. This approach automatically takes care of empty values, you see then the light grey brackets that indicate that no value is available for ‘thisRow’ and it all works fine.
Practical Next Steps
Once we have the balance values per employee from previous months and the zeros for the new employees, we can start calculating. With these 6 steps you can copy this approach.
step 01
Since we have an archive function in place that allows to shift between this month and previous month, it is important not to have future data in your table if you go back one month. So we delete everything that is this month and older (in practice, next month) via the delete function that filters on date.
Step 02
The EmployeeeNbr as virtual index we need everywhere to place the item on the right row
Step 03
The initial amounts are calculated elsewhere (in a table that holds named canvas formulas). Here you see the function Remainder at work and resulting in a decimal outcome named Residu
Step 04
The residu of this month + the value of previous active month per employee tells us if we go above one or stay below. This is the number to evaluate.
Step 05 and 06
When you look good you see that we evaluate the number and print a 1 or a 0 according the logic that the number is greater or equal than 1 or not and in case it is smaller we take the fraction. On purpose I applied a SwitchIf(), while it could be done with an If statement, this just helping my future self. The more explicit, the faster the pick up later on.
This blog is already long, so I skip the part that deals with the situation that this month is the last month for an employee. In that case, we pay out the full amount and not a rounded number. We wrap the pay out function in an If statement. In case this is the last month, then we pay the balance value + the salary of this month and we put balance value on zero (who knows, the employee might come back one day), if this is not the last month we proceed as described.
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.
