Rollup sum needs to be filtered by name in record

[Jesse_Waters]

I am trying to filter my Rollup sum value by like items identified in the record but the conditional rollup option only gives me static filter conditions that would apply to all records, not the specific ‘Component Name’ that is in the record I am trying to rollup.

The Rollup function works great for summing the batch quantities for Rack R3, but I have 3 different types of items on Rack R3 and I need to filter the Rollup to the record ‘Component Name’.

Note: Batches are created whenever inventory is added or subtracted from the Rack.

[TheTimeSavingCo]

Hey @Jesse_Waters, when I hit this issue, I usually end up creating a new table so that I can rollup on that instead

Specifically, I end up creating a new formula field that results in a unique name, in your context it’d probably be:
{Rack # Linked} & " - " & {Component Name}

And then I’d have an automation that would trigger if {Rack # Linked} and {Component Name} aren’t empty. It would then paste the value of {Rack # Linked} & " - " & {Component Name} into a linked field to the new table I mentioned, and I can then rollup from there, does that make sense?

Primary downside would be that you’d end up using one automation run every time a batch is created though

[Jesse_Waters]

I created the new table (Rack and Batch Inventory Combined) and then created the automation to populate with the structure you recommended but I am not tracking how to link the records in order to use the rollup function. On my ‘Yard Tubular Inventory Batches’ table do I need to somehow use the ‘link to record’ data field to link to the corresponding record on the new table I created?

[TheTimeSavingCo]

Jesse_Waters:

On my ‘Yard Tubular Inventory Batches’ table do I need to somehow use the ‘link to record’ data field to link to the corresponding record on the new table I created?

Yeah that’s right. Once they’re linked up, you’ll be able to make a rollup in Rack and Batch Yard Inventory Combined to see how much is in each rack

---

Hmm, the primary field in the Rack and Batch Yard Inventory Combined appears to be a formula field when it shouldn’t be; with that setup I don’t think you’re able to make new records in this table actually

[Jesse_Waters]

Automation working well. I did go ahead and change the formula to make a better name for the record in the ‘Rack and Batch Yard Inventory Combined’.

I also figured out how to link the two tables with the newly created record on the new table. See linked column ‘Rack & Component Name Linked’

But I cannot use the Rollup function to rollup the new table because there is 1 record for each batch record on the new table. The automation is not checking to see if the record already exist, it is just adding the new record every time.

The current configuration seems to put me back into the same predicament of needing to filter to common ‘rack and component names’ on the new table in order to add those quantities.

[TheTimeSavingCo]

Hi Jesse, the automation doesn’t need to create records, it just needs to paste the value of Rack & Component Name into Rack & Component Name Linked

I’ve put something together here for you to check out that should do what you’re looking for

[Jesse_Waters]

But the Linked field has to reference to a table with that linked name as the record name correct?

The Rack record is a set number as they are physical locations in our yard, but component names are whatever our customers decide to send us therefore their are future unkown ‘Rack + Component Names’ records that will need to be added to that table.

I could go to the table for 'Rack & Component Name" and enter every combination I can think of, but that is setting me up for a future bug when a new component name arrives.

Thinking through it, I could create an automation to create a new record in ‘Rack + Component Name’ every time a new component name is created…but then I would have to create that for every rack combination.

[TheTimeSavingCo]

Jesse_Waters:

But the Linked field has to reference to a table with that linked name as the record name correct?

Yeap

When a value is pasted into a linked field, Airtable will create a new record in the linked table if no record exists with primary key having that value. If there is, it’ll just link it

That’s why the automation doesn’t need to create records, it just needs to paste the value of Rack & Component Name into Rack & Component Name Linked

[Jesse_Waters]

HAHA…Got you. Let me that a shot! thanks

[Jesse_Waters]

Yeap…that did it. Thanks again.