Restaurants Inventory Template seems incomplete or perhaps needs better documentation

[Bruce]

I attended the Grist Webinar this afternoon, and the instructor suggested I leave an “ask for help” request here.

I’m new to Grist, but I’ve worked through many of the reference videos and templates, so I’m not a complete beginner.

A couple of the templates I’ve spent a good deal of time with are the Restaurant Inventory & Custom Orders templates since the project I’m working on is an inventory tracking relational database. However, when I work with the Restaurant Template, it feels incomplete since you can place and receive ingredients from a new purchase order, yet the inventory doesn’t adjust at all.

Similarly, in the Restaurant Custom orders template, you can adjust existing Bills of Materials (BOM) or create new ones – yet again, the inventory levels don’t adjust.

Perhaps the reason the inventories aren’t impacted by received purchases, completing recipe production (BOMs), and selling goods in these templates is that I am improperly entering new transactions or adjusting existing entries. If that’s the case, please add documentation so these templates’ entire workings and functionality are apparent. Or, even better, do some instructional webinars for these templates.

That said, I suspect the real issue is that transactions like (1) received purchases, (2) producing finished goods from raw materials via BOMs, and (3) the sale of finished goods aren’t impacting inventories because key elements are missing from these templates. Can someone add these missing elements so we see a robust, complete, fully functional template? And if, for some reason, a relational database like Grist cannot deliver on these missing elements, please just let people know this (and maybe explain why) so we can stop spinning our wheels trying to do the impossible.

Personally, I think a robust inventory template and video tutorial would be a great addition to you educational library! I’d love to help make it happen!

Thanks,
Bruce

[natalie-grist]

Hey Bruce!

I apologize for the delay on getting this example to you! I modified the Restaurant Inventory template here: Restaurant Inventory (inventory counter) - Grist

First, you’ll need to add a table to track outgoing products. See the Outgoing Product page.

Note that when the product is outgoing, it is in its base unit (like bottles) since that’s usually how things are being used within a restaurant. The incoming orders shows QTY of cases ordered. For example, Order 503 included 1 case of ketchup. We’ll look at how we count this in the Inventory table.

The Inventory table in this document is a unique list of the items we have on hand so I thought it simplest to include the Stock On Hand calculation here. First, we need to calculate how much of a product has been received. The Total Qty Received column contains the formula

SUM(Order_Line_Items.lookupRecords(Product=$id).Received_Qty)*$Packs_per_case

First, we use lookupRecords to find all records in the Order Line Items table (our incoming orders) for this particular item. We find the value in the Received Qty column for each of these order line items then SUM() them together. This finds the number of cases received. We multiply this times the value in the Packs per case column to find the number of Pack Units.

Using Ketchup as the prior example, 1 case was ordered. 1 case includes 72 packs so we have received a total quantity of 72 bottles of ketchup.

Total Qty Used calculates how many bottles have been used using the following formula;

SUM(Outgoing_Product.lookupRecords(Product=$id).Qty)

We use lookupRecords to find all records in the Outgoing Product table for this item. Specifically, we want the value from the Qty column for each of those records. We use SUM() to sum them all together to get our total.

The last column, Stock on Hand, calculates the difference between Qty Received and Qty Used. All of these calculations can be done in a single formula. I split it out for a simpler explanation.

I hope this helps! Let me know if you have any follow up questions.

Thanks,
Natalie