Skip to main content

Formula for product production

S

I am trying to come up with a calculation of the parts I need to batch-produce a product.
I currently Have tables for: Products, Product Variations, Parts, and Production Plan.
For example, If I need to make the product "Tea Light", this comes in small, medium, and large sizes, two different patterns, and two different wood types. These are in the Product Variations table.
Each tea light has the following parts: one bottom piece, two different top pieces, and then eight sides. These are in the Parts table.
In the Production Plan table, the "Quantity to Produce" field should take into account the current stock of that product which is in the "Product Variations" table.
I've used ChatGPT to get 90% of the way there. But for some reason, it can't figure out the formula needed to calculate the numbers of each part to produce given the quantity of product needed.

Hopefully, that makes sense. Thanks!

3 replies

TheTimeSavingCo
Forum|alt.badge.img+18

Hmm, depends on what you'd want the output to be I think.  If you're looking for a text output in 'Total Parts Needed' that just says something like:

1 x Bottom
1 x Top with Slots
7 x Sides
1 x Top Solid Ring

And if you're producing two, you want it to be:

2 x Bottom
2 x Top with Slots
14 x Sides
2 x Top Solid Ring

Then you could try doing that with either a script or with a formula.  A script would be less work I think, and as you're comfortable with ChatGPT it should be fairly straightforward to do.  You'd make an automation trigger when a 'Production plan' record is fully filled out, and its action would be to multiply the 'Qty Needed' value against the 'Qty Per Product' value', format it as text, then update that notes field

I think it's technically possible with a formula but would take a really long time to set up and would be more fragile than the script option

Adam, Consultant @ The Time Saving Co
S
  • scottengraving
  • Author
  • New Participant
  • 2 replies
  • January 20, 2025
TheTimeSavingCo wrote:

Hmm, depends on what you'd want the output to be I think.  If you're looking for a text output in 'Total Parts Needed' that just says something like:

1 x Bottom
1 x Top with Slots
7 x Sides
1 x Top Solid Ring

And if you're producing two, you want it to be:

2 x Bottom
2 x Top with Slots
14 x Sides
2 x Top Solid Ring

Then you could try doing that with either a script or with a formula.  A script would be less work I think, and as you're comfortable with ChatGPT it should be fairly straightforward to do.  You'd make an automation trigger when a 'Production plan' record is fully filled out, and its action would be to multiply the 'Qty Needed' value against the 'Qty Per Product' value', format it as text, then update that notes field

I think it's technically possible with a formula but would take a really long time to set up and would be more fragile than the script option


I'll try the script. I just thought this would be a relatively easy thing to do. Maybe this isn't the best app for the job? 

TheTimeSavingCo
Forum|alt.badge.img+18
scottengraving wrote:

I'll try the script. I just thought this would be a relatively easy thing to do. Maybe this isn't the best app for the job? 


Hmm...you could try using a new table for this instead of a script?  In that table, each record would represent a single part tied to a single production plan record.  That way you could pull the production plan quantity over and multiply it by the amount required of that part

This table would also allow you to track part usage in a structured way, and you'd have an automation to help you populate it without you needing to do it manually

 
Adam, Consultant @ The Time Saving Co

Reply


Most helpful members this week

Terms & Conditions