Skip to main content

Pull received value into total only once

  • July 1, 2022
  • 4 replies
  • 69 views
Shannon_Bradley
Forum|alt.badge.img+15

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!

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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!

Adam, Airtable Consultant - thetimesaving.company
Shannon_Bradley
Forum|alt.badge.img+15

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!


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

TheTimeSavingCo
C
Forum|alt.badge.img+4
  • Carl11
  • Participating Frequently
  • July 2, 2022

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


@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
Shannon_Bradley
Forum|alt.badge.img+15

@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.



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:

Terms & ConditionsAccessibility statement