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.

Coda Tricks by Paul

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.

The OrderForm linked to the stock

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.

Updating multiple values afte sales one product

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.

Huizer Automation on useing Coda for your Inventory Management and Stock follow-up

--

--

--

Christiaan uses Coda mainly for planning & calculations of all sorts. Follow me to learn how to Coda with numbers. I blog at least once per week.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

[AWS] Simplify SDDC Connectivity

Top 5 Benefits of Low-code (2022) | Low-code Application Development Platform | Zoho Creator | LCAP

Zano Wallet Guidance

Code Refactoring | S01E0 1: The Chanel Principle

Cloud Computing Is Important and Not Optional Anymore

Cloud based services are here to stay and are not optional anymore

Anatomy of a Streaming Data Engineering Pipeline

Set Up & Create Your First Python Program in VSCode

Wordpress vs Drupal: which should I choose?

fist bump

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christiaan Huizer

Christiaan Huizer

Christiaan uses Coda mainly for planning & calculations of all sorts. Follow me to learn how to Coda with numbers. I blog at least once per week.

More from Medium

Coding Look-Ups in Coda as a Pro

How to sync data between two CRMs

How to get Linkedin profiles in Google Sheets with a formula

How to Coda with start and end times