Simple Inventory: sub-items

owenfink1 · 02-24-2022 08:07 AM

I would like to modify the Simple Inventory App. I add items individually, but I deduct them as products, with each product containing specific quantities of several items. Is there a way I could do this?

Thanks for any help.

Owen

WillowMobileSys

@owenfink1 wrote:
I add items individually, but I deduct them as products, with each product containing specific quantities of several items.

Hi Owen! You'll have to elaborate on the comment above. What do you mean by "deduct them as products"? Can you give an example of "Product containing specific quantities of several items"? Is this like a recipe?

owenfink1

Hi Willow. Thanks in advance for you help. A recipe is a good example. Say I sell three products: a pancake, an omelet and a sandwich. Each one is made of components: eggs, flour, salt, water, etc. When I add stock I would like to enter the raw components (I add 1 pound of flour, I add 24 eggs). But when I deduct stock I would like to enter "-1 pancake" and have the app deduct the appropriate amount of each ingredient. Does this make sense?

WillowMobileSys

Yes, I understand now. Yes it can be done. The functionality would need to be custom built and would not be simple.

First you need to establish the separate tables for the Products and the Components that make up each Product. Then you need your Construction table - a table to describe how much of each Component makes up each Product.

Second, you'll want to decide on how you wish to track Inventory for the Components and maybe even the Products if they are pre-assembled items that are not consumed right away. If you want simple Inventory tracking, Quantity on Hand, then you might just add a couple of columns to the Components and Products tables. But if you wish to track Ordered Quantity, Allocated Quantity, etc, I would recommend separating Inventory Management from Product Management.

Third, you will need to create the actions and automation to keep the Inventory numbers updated. If there 3 items of Product X consumed, you will need to create the actions to read the Construction table to get the list of Components that make up Product X and then reduce each items Inventory by 3 X the amount to make Product X.

This last part is where you'll spend most of your time. The implementation will not be straight forward. But there are workarounds to get the results you need. Just come back here to the Community when you need more help.

owenfink1

Thanks so much. This is all new to me so I will have to struggle around
for a bit. But this gets me started. Thank you.

Owen

LMathew

We're currently trying to do the same thing. Is it recommended to have a Construction table for each product (listing the components that make them up) or would all product components be on one Construction table, and just list the components for each product below each other? I hope that makes sense. Just trying to understand how the layout of the spreadsheet needs to be.

WillowMobileSys

There are several ways you can handle this problem in software. The tried and true way is to apply Object Oriented Programming (OOP) principles. OOP attempts to apply the real world to data and program design.

Basically, for data, OOP just means to have a table for each physical object. For instance, if you have components that make up a product then you would have a Components table and a Products table. If there is a need for a parts list for each Product then you have a Parts List or Construction table. If you need to know the vendors for the components then you have a Vendors table. Customers who purchase the Products require a Customers table, etc.

Note that the tables based on the Object Oriented approach are the CORE table structure. You will need additional supporting tables to help in the app implementation. These are often realized as you build the app.

Take some time to think through all of the "things" the app needs to track or deal with and what properties about each of those the app will need to know about. The properties become columns in the table OR a relationship to another table.

I hope this helps! Good luck and don't hesitate to come back here for help when needed.

PhilGarrett

I agree with WillowMobileSys.

If I understand WillowMobileSys correctly, you would have a single Product table with one row in the table for each product. It will have a unique ProductId key value for each row in the Product table.

You would have a single Component table with one row in the table for each component of any product. It will have a unique ComponentId key value for each row in the Component table.

You would have a single Construction table with one or more rows for each product. It will have a unique ConstructionId key value for each row in the Construction table. You might use a Text field with InitialValue UniqueId() for the key. The Construction table would also contain a Ref to the Product table and a Ref to the Component table. For example, if a Product was composed of three Components, then the Construction table would contain three rows, one for each component. All three rows would refer to the same Product. Each of the three rows would refer to the appropriate Component. You might need to include a Qty field in the Construction table if a product uses more than one instance of a component.