Inventory Management in Coda
How can you keep track of your stock after sales? Orders arrive in one table and stock lives in a separate table. How to make them talk? This article explains the bridge using a button.
Before I got started, I first had a look at templates in the gallery. It gave me a bit of inspiration on reporting, but not on the formula logic. Next I remembered a worthwhile to share course from Paul.
Step 01 — databases
In this video of Paul you see the basic structure of most documents that have data tables. You set the database apart from the rest of the data you work with on a more frequent basis. Paul created an interesting course, have a look at it.
We create tables that contain data that work in the background. The client can add a new product or new parts, the code logic will remain the same. This part of the doc is not frequently visited.
Step 02 — order forms
With the right tables in place our next step is to set up an order form. We need to have the details of the clients and the products they want. The products we look up in the product table. We directly have all elements at hand to complete the order. The form is a Coda form, thus based on a table. No integrations with Zapier or the likes necessary.
Step 03 — linking tables
Once we have orders (thus product requests) rolling in our doc (via the form that is put on the web site via an i-frame), we subtract the components related to each product. Products and the quantity per product we want to align with a stock table.
How does the code work?
For a single product the procedure is easy. We create a button that is or pushed by an automation or by a human. This button updates the stock for one product. Below the order, the product and the stock. This example is from my previous tea business.
To make it work, we apply the logic below:
(thisRow.Product.ModifyRows(
[Tea Stock].Stock,
thisRow.Product.Stock -
product(thisRow.Gram,thisRow.units))That is it. Only a few lines. The hardest part is to see that you have to put the product reference before the name of the column to simulate a thisRow logic. The result is a stock value we can update accordingly via a button. This is a nice start.
Next we need to find a logic to directly update multiple products (or parts that make up the product). The example below is about a Tea Box that contains 3 or 4 gift boxes. Each gift box is a different tea and has a different weight and thus for each sale (Tea Box) we have to adjust 3 or 4 teas with the right values. This is a bit more complicated.
We start again with referencing he products we sold, we give this reference a name and place it before the ModifyRows() Next we select the column to update ‘Stock’ in the table [Tea Stock]. The hard part is updating the rows with the right info. So far it was copy — paste of the first version.
Below is how the above function plays out. We apply again a reference logic by putting the outcome of the FormulaMap (since we have more than one product in the box) before the table and before the destination value (stock). The tricky part is the chaining to find the grams per gift box. It might look confusing, but once you see what happens, it can no longer be unseen!
thisRow.[Giftboxes in TeaBox].FormulaMap(CurrentValue.WithName(
teabox,teabox.ModifyRows(Stock,
teabox.Stock -
teabox.[Gram Per Box].[Gram Per Giftbox])))
Understanding this logic, you can create any basic inventory management system in hours. You might need more time to get the look & feel right, than putting the formula logic in order.
Once you can reduce your stock in line with your sales, you might come across issues that require a profound analysis and discussion on how to deal with:
- Resupply and order full filment when you have a back log. What principles guide your decisions. Will you for instance let some one with a big order wait while one with a small order gets served first?
- Archiving successful orders and keeping track of open orders with room for manuel intervention by the operator.
- How to use QR codes intelligently?
- Defining rules that help you to know when to resupply
- etc.
All of this is possible with Coda. I have set up systems like this and will template solutions for those who do not have time to gather the insights and knowledge to create a tailor made inventory system from scratch. These templates focus on business users in need of tailor made processes.
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 Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I mainly rely on Coda, Mailjet, Zapier & Paperform to get the job done.
