What is the recommended way to structure the relations and rollups in Glide to achieve this?

Ask for Help
1

Hello Glide team, I am building a production management app. In my system, there is a Production Order table, and each order uses a specific material. I want to create a workflow where, when I issue materials for a production order (create a material issue record), the inventory of that material in the Material Inventory table will automatically decrease. My current tables are: Production Orders Material Inventory Material Receipts Material Issues The goal is: when I create a material issue from a production order, the corresponding material quantity in the inventory should automatically be reduced. What is the recommended way to structure the relations and rollups in Glide to achieve this? Thank you for your help.

2

To handle this in Glide, you can think of your inventory as the result of two separate running totals: everything that adds stock, and everything that removes stock.

First, for the “minus” side (material issues).

Every time you issue material for a production order, you create a record in your Material Issues table. In your Material Inventory table, create a relation from each material to all of its issue records, then use a Rollup column on that relation to sum the issued quantity. This gives you the total amount of that material that has been taken out of stock.

Next, for the “plus” side (material receipts).

Whenever you add material into inventory, you should also create a record in a Material Receipts table. In the same Material Inventory table, create another relation from each material to all of its receipt records, and again use a Rollup column to sum the received quantity. This gives you the total amount of that material that has been added to stock.

Once you have both rollups, you can calculate the live stock level for each material. Add a Math column in the Material Inventory table that does something like:

Current Stock = Total Received - Total Issued

If you also keep an initial or opening quantity for each material, you can include that too:

Current Stock = Opening Quantity + Total Received - Total Issued

With this setup, your inventory is always up to date automatically:

  • Creating a new receipt record increases the “plus” rollup
  • Creating a new issue record increases the “minus” rollup
  • The Math column recalculates the current stock for that material in real time
1 Like