How to Coda Monthly Installments?
Based on payments in previous months — Part I
My use case is about a table that contains employees that have pay back duties to the government. This table shows their name, the file number (provided by the court that issued the order), the total amount and the monthly installment. The employer is responsible for withholding the issued amount.
As you can see it happens that one employee is subject to more than one withholding and that is the case when the employee has pay back obligations towards multiple parties. For the people involved it is a sad story, for the employer additional non paid work.
The employer has to keep track of the withhold amounts per employee per file taking into account months that the employee does not earn enough to justify a withholding. It is fairly complex and in this blog I explain some basic rules so you can set up your own calculation.
We base outcomes on previous data
The above screenshot shows the table that contains the information as the employer gets from the court. In a second table we keep track of the monthly installments. Court orders work with monthly deductions that are more or less the same for everybody and file independent. It would not matter for the calculation, but it explains why you see the number 60 everywhere.
This table has a critical column named ThisMonth
We have to run a few checks before executing the calculation. Is the employee still active and thus did the employee receive an income via the employer in this month? This is relevant in case somebody is on maternity leave. Most of the time there is an income and only then we can proceed.
Imagine there is pay back duty of 300 for a file and the pay back is 60 per month, you don’t need a computer to see that it takes 5 pay back periods. Indeed not 5 consecutive months, because if somebody got ill for 2 months, there is no withholding in these months and the total run time is 7 months.
Main Trick To Take Only Previous Months
In this case you have to make sure that you only catch previous months. There is a rather easy way to get the sum of the already paid amounts you have to check if you need to apply a deduction of 60 or less (when the open amount is maybe 40). To make sure we have no mess up with the month for which we have to set the calculation straight, we make sure this month cannot live in the table when we run the calculation.
A well working solution (and I am not saying — the only solution) contains a filter and a delete function. You check if the column ‘ThisMonth’ contains the value equal or greater than the active month, thus the month you set up the calculation for. If that is the case, then you delete the corresponding rows. The next step is the calculation. This calculation only takes the previous months into account.
We use a canvas button to execute the calculation in the table DB Salary Deductions.
The first step is to take out this month (which is in my case July, so delete the last three rows). Once we have done this, we can run the calculation.
I numbered the steps to make it easier to see how the set up runs. Indeed basic because in next blog I have to elaborate on certain complexities related to the actual salary and multiple files.
Step 01
Delete the rows (if they exist) that match the active month or a period after this month (which can happen because we have an archive logic in place that is about this or the previous month). We don’t want future data to mess up our actual calculation.
Step 02
Look into the base table that contains the summarized information that is received from the court house and count the amount of active employees. The outcome is a list of numbers — sequence results in a number range.
Step 03
Get all the files related to active employees. One employee can have multiple files active at the same time.
Step 04
Look into the table where we execute the calculation and get the sum of all the amounts paid per file and give it a name. Note we focus on files in this stage not on employees.
Step 05
Look back into the summary table to get the initial amount and subtract the already paid amounts. You notice that the sum function has * -1 at the end. This is to turn the amount paid into a negative number in order to make use of the Sum().
We fill out the columns with the values we prepared
Step 06
The monthly deduction is a kind of tricky and one of the reasons I write this blog. Although it is documented in my principal file, writing improves understanding significantly, even when you Coda all day.
What you see in this part is first that we check if there is still something open, if not it stops. We are don’t save money for future cases. Second we ask if there is enough money to pay out — greater then. Not equal to or greater than, only greater then to avoid that somebody gets nothing. Maybe an additional threshold should be build in here as well. I only notice this while writing and I have to ask the company about this.
The next line checks how the amount open relates to the standard amount. In case you pay 60 each month and there is 40 open, you pay 40 and not 60. This rule makes sure that the employer pays only what is due and not more.
As mentioned earlier, my next blog on this matter is about how to relate the salaries to the amounts the court issued. When there is not enough money to be paid out, this month is skipped, but how to deal with various files when there is money for one file, but not for file 2 and 3. Something to check before setting up the calculation.
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.
