Duplicate the preceding month in Coda on due date
Perfect for recurring expenses!
My work as consultant is partly about analyzing profit and loss statements. I noticed recurring patterns in the data, especially in the order of the variables. Often, invoices keep a certain order for quite a while, which simplifies things. But, when they change that order, it’s important to create a new one for accurate data input.
To streamline the process, I created a button that automatically populates the current month’s report with the variables from the preceding month, saving me time and effort. This button also accounts for the order of things, making it even more efficient.
I recently shared this tip with the online community where someone was looking for a similar solution. My approach differs slightly, but it’s easy to implement, understand, and update. Let’s take a look at how to find the variables related to the preceding month.
This logic isn’t just for businesses and profit/loss statements! It works great for personal finances too. Whether you’re tracking expenses for yourself, your family, or even an organization you volunteer with, the principle is the same.
So, if you’re keen to get a handle on your expenses — whether personal or professional — keep reading!
Filtering on the preceding month
Instead of relying on a strict “previous month” model, we’ll focus on the “preceding” entry, which offers more flexibility for those times when you skip a month or have uneven data. This approach aligns perfectly with how tables work in Coda.
To find the preceding month’s data, we use a filter. We gather all the rows associated with each month. Then, we pinpoint the last month by asking for the rows that equal the last month. This sets the stage for our solution.
What is the filter we need?
In this table, each row represents a single transaction. To find the preceding value we need a filter.
We first order the table by costType and in our formula we sort on dueDate, below how that looks.
This formula first sorts the entire table by due date. Then, it filters the table to only include rows where the cost type matches the current row and the due date is equal to the most recent one, the max value. The outcome is a list of rows sorted by the due date.
Because the table is sorted, this cleverly finds the very last preceding expense that matches the current one.
For certain situations, it’s useful to add the Today() function to ensure the value is equal to or less than today's date.
The methods we deploy
There are two primary methods for duplicating cost types based on the most recent value:
- Table-based Approach: This involves adding a button to the table we press manually via a canvas button. The button in the table applies a filter, and if the filter output matches a due date, the cost type is duplicated. This method is ideal for smaller tables due to its simplicity and ease of maintenance.
- Automation-based Approach: This method utilizes automations to duplicate the cost type at a specific point in time, using the time based trigger. This is preferable for larger tables or situations where manual changes are undesirable. While slightly more complex to implement, it offers greater control and automation.
Both methods achieve the same result, but the choice depends on the specific needs and complexity of the table
The button in the table
We'll start by creating a button within the table itself. This button will house the function we just defined, which includes a reference to today's date. As part of this function, we've specified that the due date must be less than or equal to today's date. This ensures that when we duplicate a value, the new due date is set to today.
While setting the new due date to today is a practical choice, it's not the only option. We could also use relative dates, such as one month after the original due date, or even specify a fixed number of days, like 14. The flexibility of the button setup allows for various configurations based on your needs.
You’ll notice the function utilizes the NoAction() fallback function, a recent addition to the Coda toolbox. This replaces the older, unsupported function and is essential for maintaining button functionality.
To make it clearer, I’ve filtered the table to highlight rows where there’s a correspondance between the due date and the outcome of the function. This shows how each cost type can be duplicated with a single click.
As said before, the main idea is to focus on preceding values rather than just previous ones. This approach ensures you’re working with the most relevant data. The button’s layout is based on the function we developed, which duplicates the row if there’s a match and takes no action otherwise.
The automation
Our second approach involves using automation with a time-based trigger. For instance, we could set it to run daily at 7:00 AM. When activated, this automation should duplicate the necessary rows. The key here is defining the criteria for duplication. This is our second method for duplicating preceding values, this time using automation.
A simple way to trigger the automation is by setting a condition that compares today’s date with the outcome of a formula. When specific criteria within the table are met, and the resulting date matches today’s date, the duplication function is executed. This approach allows for controlled duplication, as it might only occur once per day or not at all, depending on the criteria and frequency settings. You can adjust the recurrence interval to weekly, monthly, quarterly, or yearly as needed.
To make things more engaging, I’ve added some complexity to the table compared to the earlier button-based example. This demonstrates the versatility of automations in handling more intricate scenarios.
The screenshot below reveals a time-based automation, set to run daily at 7:00 AM. It’s important to note that Step 1 logic doesn’t apply to time-based automations in Coda, more about it in this blog post. This is because these automations aren’t triggered by changes in a table but are simply scheduled to execute at a specific time.
The automation evaluates each date in the expense table using the ForEach function. A SwitchIf() statement then adjusts the due date based on the interval (weekly, fortnightly, monthly, quarterly). This adjusted date, referred to as the "outcome," determines whether a new row is added. If the outcome matches today's date, a new row is created with the same cost type and interval, but with the due date set to the outcome. A NoAction() function handles cases where the outcome doesn't equal today's date.
This setup, while more complex than the button-based approach, demonstrates the power of automations for managing intricate, time-dependent tasks. It requires understanding key principles like the absence of Step 1 logic in time-based automations and the use of ForEach() for evaluating individual items.
The primary advantage of handling this logic within an automation is that it eliminates the need for a separate button within your table. This streamlines the user interface and reduces maintenance overhead. While the button-based solution offers simplicity, this automation-driven approach provides greater robustness and scalability, particularly for larger tables.
I hope this article shed some light on the topic and helped you solve a tricky problem! If you found it valuable, consider a donation to support my work.
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
