How to remove inventory count from multiple records

[Abraham_Crowe]

I have a Table for Product Creation that is linked to individually tracked Inventory on hand with a rollup to count down available inventory left as it is used in production. So, when I get to a point in production, I need to select multiple matching ingredient records with separate record ID’s because, let’s say, I need 2.5lbs of Flour but I have a 1 Lb bag remaining and a ten Pound new bag in inventory. Using multi-select you can choose booth ingredients, but the rollup removes the value from both instead of using the last of the 1 lb. bag and then removing the remaining amount from the 10 lb. bag. So instead of having 1 bag left at 9.5 lbs. I have 2 records 1 showing -1.5 lbs. and the other showing 8.5 lbs. left. Do I want to combine first so when they are removed in the process tab there is only 1 linked record to rollup, or is there a formula to cascade a value between records?

[TheTimeSavingCo]

Hi Abraham, I don’t think there’s a formula to cascade a value between records I’m afraid, and I’m not too sure what you mean when you talk about combining first and a process tab. Could you share some screenshots perhaps?

It feels like the cleanest thing to happen here would be to have two separate “Usage” records, one 1lb usage for the bag with 1lb left, and one 1.5lb usage for the new 10lb bag, but this means we’d need a third table for this that would have links to both the Product Creation and Inventory tables. Without it, I don’t see how we could get around the problem of the -1.5lbs and 8.5lbs displaying

We could probably accomplish this with formulas and automations alone if you’re open to the idea of the third table. Let me know if that’s a thing you want to explore

[Abraham_Crowe]

I will send some pics and a better explanation as soon as I get into work today thanks. I just need to have a running inventory automated by the process in each base. Something that is simple in excel but now seems overly complicated for air table. Our company is forcing us to switch to this system. It may not last.

[Abraham_Crowe]

Here are screen shots of what I have. I can break down the formulas and automations if needed. There are linked record to 2 separate Inventories. I for the Base product and 1 for the terpenes. As I use the terpenes for production, they have to be tracked separately. I have completely automated the entire Table to auto populate all bases as each required process is completed. This may be my lack of experience. But I believe I have started in the direction you are mentioning.

[Abraham_Crowe]

[TheTimeSavingCo]

Hi Abraham, that looks like a pretty intense set up, and it does seem like you’re moving in that direction

Here’s an example of a Usage table where each record is linked to a single record from Inventory:

And here’s the Inventory table

And so each Product record could have multiple Usage records for the same type of item, as each usage record would be tied to a different Inventory record. Does that make sense?

[Abraham_Crowe]

Thank you I will try another table linked to the terpene inventory as a usage log. Would that use an Automation to create as well as update the record until inventory is zero then us an identifier to create a view in which I sort by inventory with amount > then 0? Then link the rollup field back to the production log?

[Abraham_Crowe]

ok I have that linked but I am having difficulty cascading the remaining total of the Automated record into a new record for production. It will not let you duplicate it to create separate records based on the amount remaining from production. So could an automation linked to the record Id and a create/ update condition be used in its place?

[TheTimeSavingCo]

Hey Abraham, yeah that might work. There’s a bunch of ways we could tackle this and I’ve created one that should work for you to look at here

The workflow here would require you to set the amount needed for the product, and select the Inventory records that were used for this. After you set this information, the formula field will output text in a specific format and an automation pastes it into a Link field, automatically creating records in the Usage table

From there, the text is parsed and then linked to the correct Inventory record via another automation

To view the setup (formulas, automations etc), you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

I’m also available to be hired to set it up in your base for you if you’re interested

[Abraham_Crowe]

ok I have everything changed and the automation shows they work but when I use airtable the only time the automation updates the record is if I go into automations and press “run as is’”. Why won’t it update automatically on its own as yours have as soon as I complete the conditional formatting?