Inventory (IN / OUT) with a Products made of sub-products

Hello guys,

I am new to AppSheet and I love it!!
I already looked several Webinar and video tutorials —very helpful and well explained!

What I am trying to figure it out, is how to achieve an App with IN and OUT Stock inventory made of a Product that is made of other sub-products. I attach here a small schema here to understand it better.
Schermata 2020-03-03 alle 08.03.17

So basically my clients buy the Products A and B which are made of other sub-products, in this case: P-A is made of 10xScrews, 1xHammer, 2xGlue, 2xTape and P-B of 4xFork, 4xSpoon, 4xKnife. But actually I need a stock counter for both, so I can know how many P-A and P-B I have left and also how many sub-products. The thing is that I would like that when I decrease the stock level of P-A also the level of its sub-products goes down. So the idea would be define how a P-A or P-B are made of (visible on the right part of the image I uploaded) in order to subtract the quantity of the sub-products related to the product.

I hope to be clear in the explanation.
I would be really interested to know if this is possible to build with AppSheet and if there is something like this already build, or if somebody can give me a brief idea on how to structure this.

Thanks in advance,
Simone

I would recommend looking at some of the sample apps. At top of this page under the Eduction menu there is an option for Sample apps. Then once there you can filter the apps by Inventory Management. You’ll be able to download/copy apps and play with them.

2 Likes

Hello John,
thanks for the answer.
I´ve already looked to all the Inventory App presented in the Sample apps, but there is no App that suite my scenario. They are basically all perfect for the IN / OUT stock level, but I didn´t find none that concatenate a Product to other sub-Products, or in general, chaining a product to another one.

I am very curious about how would you achieve this!
Thanks again

Fairly tough situation there, but should be possible, to a degree. It is similar to creating “recipes” for items. Which I’ve done for determination of amounts of raw ingredients needed to order.

I’m mostly just commenting for now so I remember to come back to this. But you could determine the inventory amount of Products A & B by virtual column expressions. Something like
MIN( screws inventory / 10 , hammer inventory / 1 , glue inventory / 2 , tape inventory / 2)
Subtracting those sub-products when A,B are ordered will need solved still though.

Hey @Marc_Dillon,
thanks for answering!
I feel what you mean… Basically you would have a normal Inventory database with all the sub-products and then for every ´mother´ Product A, B, C you create a virtual column in which the expression would take off the inventory level of the specified sub-products and the related quantities, right?

@Marc_Dillon, I tried to think about it and doing some tries, but no resulta so far… I am not really sure how could I develop it :confused:

Hi @Simone_Ellero
Do your “Products A” and "Products B " contain the same lists or do they vary for every order? I did make a sample App that used “Bins” containing various items. I am not sure if it made it to the sample page as it got a bit complicated but it was doable. I will see if I can find it. There have been a lot of new features added to AppSheet since I made it. I may be able to improve on it. If it looks like what you are looking for I will update it and let you know. It may take a few days.

1 Like

Hello @Lynn,
yes, Product A and B can contain the same sub-products list. If this is what you mean.

For example:
Product A:
Screws (3)
Glue (1)

Product B:
Screws (2)
Hammer (1)

Please let me know if you find the App you made!
Thanks a lot!

Hello there,
anybody on this?
@Lynn? @Marc_Dillon?

Thanks a lot!

Yes sorry, I have not forgotten this, just busy.

What are all possible transactions that users will need to do in the app?

Here’s a list to start from:

  1. Purchase Products (reduce product & sub-product inventory)
  2. Add sub-product inventory
  3. ??
1 Like

@Simone_Ellero

Just wanted to let you know, for faster responses, use the Reply button inside of the post you are responding to. This will notify that person of the reply.

Otherwise, you can do what you did above, use the @ name to “signal” those people.

If you always use the Reply button at the bottom. No one is notified and your response will likely go unnoticed until the people you have been chatting with just happen to scroll through the Questions to see your replies.

Hi @Simone_Ellero
Sorry i didnt get back to you. The app I was thinking of was the “Quote and Proposal” sample app.
https://www.appsheet.com/samples/Produce-job-quotes-and-proposals-Workflows-email-quotes-and-proposals-to-customers?appGuidString=6d56de4d-bf5a-43c8-952e-912ca4a75598

1 Like

Hello Lynn,
thanks for sharing the App, but actually I don’t think it’s exactly what I need - or maybe I didn’t get the App properly. As I wrote at the beginning I need to define a Product which is made of other several Sub-products, so that the App automatically understands how many quantities of any Sub-products it needs to make the Product itself. Hope it make sense…

I would like to be able to have stock count of both Product and Sub-products, and that these are in some way connected.

In the next days I will define the App and all the functionalities I need with my team. But this would be a definitely a must feature :confused:

You would need a recipe table that can translate the Product to the sub-products for knowing how much you can sell of a product and knowing how much sub-product to reduce your inventory by when a product is sold. This would look something like
RecipeID
ProductID
Quantity
Sub-ProductID

Doing Joined calculations in Appsheet is a bit odd but probably just do something like ProductAvailable as a virtual column in this recipe table that is Number of Sub product in inventory/Quantity then when calculating the amount of Product left you MIN(SELECT(ProductAvailable WHERE ProductID=ProductID))

Hello Austin_Lambeth,
not really sure what you mean for Recipe table. Would it be the one where I define relationship between the Products and the Sub-products? If yes, how would it be structured? Not sure if these that you mentioned (RecipeID, ProductID, Quantity, Sub-ProductID) should be the columns of the table.

Sorry, but I am really at the beginning with AppSheet and absolutely not a Pro :grimacing:
But hey, thanks a lot!!!

Yes, so the recipe table would sit inbetween the products and sub-products. Those would be your column names. The RecipeID would be the key for the recipe table, can be anything that is unique so you have a way to identify rows in this table. ProductID would be a reference to your products table. The Quantity would be a number column with how much of a sub-product is needed by the Product. The Sub-ProductID would be a reference to the Sub-Products table and would tell you what Sub-product you’re recording.

Thanks a lot Austin! I will try to make sketch it out in the next days and see where I can get.

One question: is it possible here to share an App not finished? I mean would it be possible for me to share the App I am doing do I can get better advice or hints?

1 Like