Need Help with Automation that only links records based on rollup field sum

[missmeganj]

Hello- I’m looking for some help with an automation/formula in airtable.

I have a table called Inventory that has all of my products in it. I have a view that contains priority level of what needs to be produced next and the amount of space it takes up during production by product.

I have another table with kiln loads (production) that I need to link all of these records to automatically by priority level and not have the kiln space exceed 90% (I have a rollup field that sums the space taken by each product). Essentially, I need the automation to start linking the products to be produced on the first record, once that reaches a max of 90% space, start linking them on the second record, and so on until all of the products to be made on the first table are linked to a kiln load.

I’m pretty good at airtable but this one has me scratching my head. Thanks for any help in advance!

[TheTimeSavingCo]

Hmm, I'd recommend writing a script for this if possible.  Without a script, you're going to need to set up a system that will loop through all of the items you need to load up one by one, and for that I think you're going to need a helper table where you link all of the items to it and it helps you figure out which is the next item the automation needs to run on

You're then going to need to do math with an automation.  To do this you'd need to have a formula field set up somewhere that'll sum the Kiln's current capacity + this new item's % of space and see whether it's <90%, and within the context of the automation, you'd use an "Update record" step to link the current item to a helper record that's linked to the current active kiln, and then use a "Find record" step to get the output of the formula field

You'd then use a conditional group to set whether you're linking this item to the kiln (if it's less than 90%), or linking it to the next kiln and also resetting your helper record

Theoretically this should work, but would be quite a pain to set up.  Hopefully someone else has a better idea!

[Alexey_Gusev]

Hi,
I think despite this could be done by script, crafting a no-code automation for it is more interesting. 
I would create a view of records in Kiln with a load < 87%, for example (90 - maximum load of a single record),
sorted by priority.
Then you need to run automation (somehow. it's up to you to choose which trigger to use), loop through all records to be linked (like Find Records, then use list of IDs as Input for Repeating group), inside loop you should put Find Records based on a view I described above, with Maximum=1, and use this IDs of this Find Record result (actually always limited to 1 ID) as a value of linked field.