Hello, I’m working on an inventory ordering base. for managers of various store locations to replenish items within 3 different categories - Ice Cream, Doughs, and Pre-made Sandwiches - each of which have about 10-20 different options at any given time. Right now, I have built a “Menu/Inventory” table with items and prices for each and an “Orders” table that houses the order form. On the “Orders” table, I’ve created 3 linked fields for each of the categories mentioned above (Doughs, Ice Cream, Pre-Mades) containing their related items and separate fields for quantity ordered and price total per item. The “Orders” table is where the form lives for managers to submit their restocking requests.
I’m looking for general insight into how to set this up so it runs efficiently and have a few specific issues to resolve below:
First, I’m running into issues with the price total per item fields - right now, I’ve manually entered in each item’s price to a simple quantity x price formula but am wondering if there’s a way to pull each item’s price directly from the “Menu/Inventory” table into the formula. Because multiple items can be ordered, the linked prices field pulls each item’s price in which I believe is preventing me from using it in the formulas.
Secondly, I’m wondering if it’s possible to create a view (and an invoice/order report through page designer) that would show only the items ordered so that on the back end, we don’t have to wade through all of the blank fields should those submitting orders only need a limited number of the items available.
Greatly appreciate any help I can get!
