Help with inventory app <> trebuchet method

Ask for Help
1

Hi Glide!

I was looking for some guidance on an inventory app that I am working on.

Context: The inventory app should have a “Product exit” table that logs every time someone takes products out of the inventory for a marketing event, etc.

I used the trebuchet method so that each “Exit” contains a JSON array with:
{product: 123, qty: 1}, {product:456, qty: 4}

Problem: While the trebuchet method is amazing, I am hitting a blocker because I am not really sure how to calculate the number of products in stock. i.e., how many products are out vs. the ones in the warehouse?

Does anyone have a recommendation on how to tackle this issue?

Problems to solve:

  • Understand how many products are in stock vs. outside of the warehouse.
  • Once products are back, how to make the calculations so that the products come back as available.

Thanks all!

PD: big thanks to @Robert_Petitto for the trebuchet method. Here is the link in case someone is not familiar with it.

2

You might run into character limits at some point, but you might need to

  1. Create a helper table that looks up ALL json exits. Use a JSON Object column that create an array from the lookup.
  2. Bring that array into your products table
  3. Create a JSONata query in a template column that you’ll use in a Query json column that will roll up how many product has exited the system. (I use ChatGPT for this)
  4. Use a math column to see how much product is remaining