Advanced inventory management

AppSheet AppSheet Q&A
, ,
1

I have been trying for dozens of hours to create more advanced inventory management app.

Simple overview:
Tables:
Products - List of products that are ready for sale.
Parts - database of all parts for products assembly.
Recipe - Separate recipe table for each product.

Idea is to produce a product and calculate used parts with one click to subtract used parts. Currently the calculations happen in “recipe” table and an automation task takes the remaining quantity and sends the new quantity back to “Parts” table.

I am using methods from this tutorial: https://www.youtube.com/watch?v=IKqm5OzyLZQ&list=PLMf2Af_xkzRVzEFysCaNTWZMDy9xpbHyf&index=3

Maybe my approach is totally wrong. Should i make an action instead?
I tried an action which made the calculation but then I had to run the action per every product. For 10 products i had to run the action 10 times.

1 Like
2

It would be more helpful to write down what you’ve done instead of watching a 1-hour video.

3

I have tables “release stock” and “add stock” with a form view where i enter the product and quantity to add/release.

“release stock” table columns:
product name - it’s a “valid if” field, the product name is referenced from “products” table.
Item code - referenced from “products” table according to which “product name” i choose.
quantity - where i enter how many products i have sold/released.
quantity available products - the value is referenced from “products” table.
remaining products - formula: [quantity available products] - [quantity].

Next step is my automation task with this formula:
“Item code”: “<<Any(Select(Release stock[Item code],[_thisrow].[Item code]=[Item code]))>>”,
“Remaining products”: “<<Any(Select(release stock[remaining products],[ID]=MAXROW(“release stock”,”_Rownumber")))>>"

It take the result of the “remaining products” and take it back to the “products” table.

Now i am trying to create similar function but instead of changing the quantity of one product i need to change the quantity of many products.
For that i have a tables “parts” and “product recipe”.
“Parts” is a database with all parts in the inventory .
“Product recipe” contains a list of parts needed to build one product.
Im struggling to create a function that subtracts the quantites given in “product recipe” from the database “parts” table.
I tried to use the same method as with the “release stock” function.

4

  • Okay for the function to calculate the quantities of many parts simultaneously I created another table “Release parts” with the following columns:

    Product name - Select(Products[Product name], true)
    dropdown menu to choose products.

    Item code - Select(Product recipe[Item code], [Product name]=[Product name]
    This should list all part codes that are used to build the chosen product.

    Quantity - how many products have been built

    ID - Uniqueid()

    Parts available - Select(Parts[quantity], [Product name]=[Product name] )
    This should select the quantities of the parts used for the chosen product.

    Parts required - !!!This is the part that i do not understand !!!
    “Product recipe” table has a column “parts required” which has listed all parts along with quantities to make the product. These quantities should be referred to this “Parts required” column in table “release parts”.

    If it would work by this far then next step would be:
    Parts remaining - [Parts available] - [Quantity] * [Parts required]
    and then i would use the same automation task as for “release stock” to update the
    parts quantities in database “Parts” table.