Database optimisation for BOM-Stock system

Hi there! We use a pick and place machine in our production line. Our machine can hold up to 122 unique components at one time. Different products require different components to be loaded onto the machine.

We’re attempting to tie a BOM list into our Stock system, to automatically balance our stock levels as our production process is run. Each of our component reels are uniquely identified, meaning that if we run out of a component we will replace it with another reel (with a different ID, but same internal part number). We currently track the components used for each product with a Batch ID, which contains a list of all unique component IDs.

Our BOM is currently laid out in Tulip as shown below. (PN is Part Number, RN is Required Number of that part).

To complicate matters, we also have a list of alternate parts that can be substituted in. We feel that the best way to deal with this is to list various product configurations, along with the required number of parts per panel. We’re happy to be proven wrong though!

ID Product Name Product Variation 01 PN 01 RN 02 PN 02 RN 03 PN 03 RN
BOM-prodA Product A Standard 01 2 02 2 03 2
BOM-prodA-1 Product A Alternate 02 2 03 2 04 2
BOM-prodB Product B Standard 05 3 06 1 07 3

We’re struggling with translating this structure into an efficient stock-deduction system. I believe the steps are along the lines of:

  1. Collect all values of RN and PN for a product configuration (perhaps using a Connector function) into an array.
  2. Check array for every PN that is loaded on the machine. Get index location of that part number, and then store the RN in Machine table (see below).
  3. Store current stock value of component (from Stock table) into 4th column of Machine table.
  4. On completion, use an aggregation count of the Executions table to identify how many units were produced. Take that number, multiply it by the RN for each component, then reduce “Current Stock” by that number.
  5. Update Stock table with new stock number.
Machine Location Internal Part Number RN Current Stock
F01 01 2 500
F02 02 2 200
F03 03 2 300

For this number of parts, I feel the number of trigger steps required to update the Stock table in real-time would be too time-intensive, thus doing most of the work on completion.

I’m unsure if the better approach is starting with the BOM and manually matching parts somehow. Happy to hear any ideas!

We have some experience with the Looper custom widget, connector functions, etc. but are yet to look too deeply into Objects, Automations, etc., so if there is a simpler way of doing this we’d love to know!

All the best,
Preston Fisher

Hi @Preston!

Thank you for such a detailed post to paint a picture of what you are trying to do here! I think it may be easiest to review the current approach you have taken and brainstorm any alternatives in one of our Office Hours sessions. During those sessions, someone from the Tulip team will be present and you can share you screen and we can walk through this together. If for some reason you can’t make it to one of those sessions, we can continue to troubleshoot here as well, but it might be best to work through this live :slight_smile:

Let me know what you think!

Thanks @Beth.

I’m in Sydney, Australia, which makes time zones a little interesting. The General Office Hours run from 11am EST, which is 3am here in Sydney. The New User Office Hours has a 4pm session (8am Sydney time) which is more doable, but I’m guessing those sessions may be more beginner friendly.

Would covering this in a New User Office Hours session be suitable?

We can cover this in new user office hours! Often there is a mix of experience levels in all the office hours, and things have been more quiet with the end of the year, so you may even have the session to yourself :slight_smile:

If the 8am Sydney time is too early for you, we can also try and find another time that better accommodates the time change to review this question.

Either way works for us!

1 Like