Coda the prices of products prudently
Get the most recent price applying a BOM logic
We all know that prices change. Promotions make things (temporarily) cheaper while inflation or market demand shows itself in higher prices.
When you want to review an invoice from a while ago, you need for that invoice the prices valid at that time. This is only possible when we timestamp prices.
In this blog we explain how to do that in Coda.
The properties of products
Mostly products are grouped by categories and sub categories and it happens that one product fits several (sub) categories. The allocation of a product to a (sub) category is based on its properties. We can group all things blue, but likewise all things that have wheels or are cost more than $17 per unit.
Properties as I said define the product, some are fixed, others change. Availability can be seen as a property and so can price. Both can change. When we have orders but no products the property stock is a negative number. We have a so called back-order.
When the color of a product changes, it happens that it becomes a different product, with a unique product ID. When the price of a product varies, most of the time, we do not create a new product ID, we keep the product ID as it is and we change the property price.
In this blog the suggestion I make is to keep track of your price by adding a time stamp and retrieve the most recent price via an easy operation.
Products in Tables
Products are most easily defined by a number or a string (numbers and other symbols), but we as humans like product names and this begs the question if the product name can serve as the unique identifier. Is a product in Dutch the same as it is in French. Certain parts are for sure, but the product description is already in a different language and ‘the product’ contains also the variable ‘product description’. Put differently, they have much in common but are not the same. Nevertheless for us as humans to keep things easy we make a choice and this is for example that the product ID plus the English name defines te essence of the product.
Below some tea products I related to the harvest year. In the tea trading business this a regular practice. Some teas get more expensive over time (pu-erh for example), other loose their specific flavor when getting too old.
Once we have the teas we can link them to prices and timestamp them. Not all teas have price changes on the same moment. We get the relevant price via a filter. the FromDate column is a relation that links to a table in which we keep track of the dates we change prices.
We use two controllers. One for the date and one to relate to the tea products. Please note that I apply the USA date notation.
The date picker permits the selection of any date and you cannot limit the date range. There are no options like we have in relation controller. This results in a suboptimal user experience. You are forced to work around it. That introduces complexity and as you know, I don’t like that. I hope Coda has a doc that contains all these little frictions so they can fix them one by one.
In our case it does not make sense to have dates before Jan 3, 2023 since that is the first time the tea product got a price and for the sake of this blog I assumed that 3 months after the last price update, the tea is no longer available. The alternative is that when you ask for a price in 2028, you still have the latest price from 2023. Again, you cannot set limits on the date range and that is confusing, meanwhile you don’t want resort to a table with dates because than you miss out on the UI of the date picker.
Conclusion
As you have seen, it is not too difficult to apply a BOM logic when you deal with prices. I actually believe it is simpler than most people imagine.
I noticed nevertheless suggestions ín the community to work with buttons that modify rows with the latest price. That approach fails to go back in time, because you no longer have that data set, you have overwritten the price.
My advice is to set up your own doc and start working with it. I use this logic already for a while and I am rather happy with it and I hope you will say the same soon!
This blog is part of small serie of 3 blogs in which I show the advantages of a BOM approach related to:
- addresses
- product prices (this one)
- contact details.
My name is Christiaan and blog about Coda. Since the summer of 2023 mainly about how to Coda with AI to support organisations dealing with texts and templates. Why I focus on Coda AI you can read here: ⤵️
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. You find my (for free) contributions to the Coda Community and on Twitter.
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.
