Pull received value into total only once

[Shannon_Bradley]

Hey awesome community! Working on a new setup and cannot wrap my brain around something.

I have an inventory management setup, with a table for all Inventory items and a table for Purchase Orders. As items are received I would like the amount to add into the “Quantity On Hand” total…but only once.

Using the 12ga Sheet Aluminum as an example, I would like the On Hand to update to 10 units with todays (06/30/2022) received order, but I don’t want the formula to look at the received amounts every time…just pull it in the one time. We do a yearly inventory to update what we have and I don’t want to worry about the formula confusing things.

I hope this makes sense to someone who can help make it make sense to ME!

[TheTimeSavingCo]

Hi Shannon, hmm, in the Inventory Management table, what if we convert the Quantity On Hand field to a rollup, with the condition of Status = Received?

That way it would only rollup on records in the Purchase Orders table that have the status Received?

If you’re looking for a complete separation between the two, i.e. not a rollup or a linked field, then we’d have to look into automations to do a one off update perhaps

Apologies if neither of these are relevant ha, I don’t think I fully understand what we’re trying to do here. If my suggestions are off the mark, would it be alright if you gave me another example please? Sorry!

[Shannon_Bradley]

That is something we hadn’t thought about, looking into automations! Thanks for pointing in a new direction!

[Carl]

@Shannon_Bradley Easiest way is definitely an automation. But an automation doesn’t accept formulas. So on the PO table you’ll need to create two new fields. One is a lookup to Inventory to pull current quantity on hand. The second is a formula field to add Quantity (of the PO) + Quantity on Hand (from lookup).

Then for an automation you’ll set the trigger to be when record meets a condition, PO table, condition is Status = Received.

You’ll use a find record action and search the inventory table for a record that contains the PO Name in the Linked field. (Airtable only lets you pull RecordIDs it seems from a find action or from a trigger action. I had tried pulling it from the linked field in the trigger output + tried creating a lookup to grab a record_id field in Inventory, but they didn’t work. So this Find Records is a necessary extra step.)

The last step will be to update the record. Table is Inventory. RecordID is from step 2. Then update quantity with the New Quantity Formula field.

[Shannon_Bradley]

Never in a million years would have thought of that! Off to give it a try, thank you so much for taking the time! :star_struck: