Coda your most recent value
Use dates to filter and keep track of changes
In an earlier blog I introduced a method to keep track of the most recent value in a list using the ‘until’ value. Most recent:
- VAT value (Sales Tax)
- Salary (after promotion at date x)
- Fuel Price
- Job title
- Work Planning
- etc.
The idea was to find a blank ‘until’ value. Although it works, I believe it is time to propose a smarter solution based on the ‘from’ date. It is easier and works faster.
After reading this blog, your doc’s wont look the same again. Most your tables will have a date field you will use to keep your data aligned over time.
The Last() — VAT (or sales Tax)
I came across an interesting use case a few years ago. In the Netherlands the lower VAT became 9% on Jan 01, 2019, before it was 6%. Many companies did not know how to deal with this properly and paid 9% while they still collected only 6%. That eats away the margin. VAT collecting entities would have needed an approach like below. The VAT type related to a from date. I am not a VAT expert and for the ease of use I dated all VAT on Jan 01, 2018. The 9% has as starting date Jan 01, 2019.
To get the lower VAT values for the Netherlands we create a simple filter, this one is living on the canvas.
The lower VAT is applied to services like a hairdresser, fruit, public transport etc. Once you want to know the price of a good / service and the VAT related to that price, you need a date you can reference, like below.
It also shows a blank, we don’t have any VAT value before 2018, that is why. This logic avoids you a great deal of trouble.
The Last() — Schedule
In my blog on how to distribute values over years, you find the screenshot below.
and in the next screenshot you see how we get the most recent value
The Last() — Fuel Price
For clients active in professional people transport I keep track of the fuel prices in Belgium. The relevance is practical and relates to my role as controller for some clients. We (gu)estimate the Profit & Loss of projects. As you can imagine, the fuel price matters when you drive a bus.
Again we apply the set up we have shown before. The day of writing this part of the blog is Wednesday, Jan 04, 2023. I need to ask for all rows with a value, prices are updated once in a while, therefor the IsNotBlank() in our function.
Your table set up
Once you notice how important it is to relate data to dates, you create par default a date column in most of your tables. Your friends move to a new address and so in coda, the new address gets the from date telling us when they moved and this is true for all sorts of information we deal with. Maybe even for gender (male / female / x).
When you create a Bill of Materials, you add a date column, when you set up your contacts and email addresses, phones etc, you create a from date.
Maybe you first have to experience a bit of trouble (like I had) before you start doing what I promoted in this blog.
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.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
