I am working on an app that will decrement inventory on hand each time we make a product. The app uses data from the BOM for the product to present a list of all components used to make the product. General flow of the app is as follows:
User scans a kanban card and the product SKU is loaded as a variable
A dynamic table presents the BOM for the SKU
The user enters the QTY of products made and presses a submit button
At this point, I want all components listed on the BOM to be decremented in the Components Inventory Table by Usage Qty x Qty of products. Example: User needs to record 2 shirts made. BOM lists 13 snaps per shirt. Inventory of snaps is decremented by 26 snaps.
We have many components (part numbers) and the BOM changes for each SKU. Is it possible to make dynamic Record placeholders?
hello @velogirlrides, thanks for posting your question!!
based on what I’m understanding, you should be able to decrement the Table Record Field by any mathematical expression you define (including by multiplying 2 values). for example:
Yes, that part of the challenge seems straightforward. But how can I do this for a set of 50+ components listed on the BOM all at once without making a Record placeholder for each of the 50+ components?
Okay. We current do not have “mass table record updates” as you highlighted, but we definitely want to build that in the future.
In the meantime, my best suggestion is to standardize the SQL output so that it sends the same columns to Tulip, no matter what happens to the BOM. For example, these might be the quantities:
part1: 0
part2: 3
part3: 0
part4: 6
Then, in the trigger logic, you would hardcode all the trigger actions so that they would deduct from a certain line in the inventory table no matter what. If the amount was 0, then nothing would be deducted.
And you would load every single possible part as a separate record placeholder as well.
This would still be cumbersome, but you at least would not need to update the app logic every time you make a change in the SQL database.
I don’t fully understand the process that you are describing.
As of today, I have 1,406 component parts and we add new ones every day.
Are you saying that I would need to add 1,406 record placeholders manually? Then add a new record placeholder every time a new component is added?
Do you mean that I would load every line in our BOM Data (for all SKUs) and somehow decrement only those applicable to the current SKU that I’ve just created?
My app currently displays an interactive table that lists all lines of the BOM for the current SKU. I think I can write trigger logic to decrement inventory if the user clicks on each row of the interactive table. That’s a lot of clicking (50+ for one SKU) and would be highly subject to error. Is there any way to select all rows at once?
Yes, if your BOM has that many items, then I would not recommend the solution above. Yes, manual clicking is probably the best option until we release “Mass Table Record Update” functionality.
Is it correct that you are maintaining your inventory levels in Tulip tables? If this is the only app using the inventory tables, I would consider moving those to MySQL, too. Much easier to work with dynamic information in your SQL connector functions.
hello @velogirlrides, as discussed here’s a quick video showcasing how you can subtract items from an inventory table given a BOM with parts & corresponding quantities that you can use until mass table record updates is available:
@gio One question. I need to add the feature to complete the app when all lines have been loaded. Would you do that with an If/Then clause that measures when the Index = zero?
hello @velogirlrides, depends on how you want the workflow should work. but I would actually do it when Index = BOM Length. that way all the items will be subtracted from the inventory table and then the completion will occur.
