Using an App to update a Table with dynamic Record Placeholders

#1

Hello,

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?

Jen

User

  • List item
Tulip Office Hours 04MAR2021
#2

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:

Screen Shot
Screen Shot3104Ă—1910 390 KB

would something like that work?? let me know if I’m misunderstanding something.

#3

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?

#4

Hi Jennifer, where is this BOM being stored? Is it in a separate SQL database or ERP, or also in a Tulip table?

#5

The BOM data is accessed via a MySQL Connector.

#6

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.

1 Like
#7

Kevin,

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?

Jen

#8

Kevin,

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?

Jen

#9

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.

#10

Forecast for the “mass table record update” functionality?

Thanks for walking me through this issue.

#11

No forecast at the moment, but I will update this thread when we have one!

#12

Kevin,

Thank you for your help. It will be a good feature.

Jen

#13

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.

1 Like
#14

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:

let me know if you have any questions!!

#15

Thank you Gio. I will watch this video and try to put it into action. I will post any questions that arise in this string.

1 Like
#16

@gio It’s working!

Next step is to create a MySQL connector that will update the Inventory Table each time we add a new part number.

#17

happy to hear it @velogirlrides!! let us know if you have any questions while setting that up!!

#18

@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?

#19

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.

#20