Conditional Reference to a Field on Table

[Winston_Chang]

I’ve tried to work this both using just the formula and also a combination of the formula and conditional fields (which seems like how it was designed to be used).
I have an Inventory table that contains our inventory. I have an Orders table that contains our Orders. The Orders table references the Inventory to identify which item is being purchased. On the Inventory table I’d like to create a field that sums the amount of orders placed against an Inventory item so we can tell when we’re running low.

Here I do a “rollup” field that has two conditions and a summation of the values for the formula. The first checks if the order was actually purchased. When I only use this condition, the field sums correctly. The second condition is suppose to take the linked field on the Orders table and check if it is the same as the Inventory Name. I try to use the {Name} designation for the field in the record but that doesn’t work. I’ve also tried a string just to see if that would work and it doesn’t. The guide seems to reference the ability to choose tags but that option doesn’t appear. This shouldn’t be a circular logic issue.
Any help is greatly appreciated.

[Winston_Chang]

Just an update. If I set the condition to “is” and use the exact string in an Inventory record’s {Name} field, it will work for that one record.

This implies that the reference is correctly displaying the field as a text field. However, I’m still not able to use a field reference (e.g. “{Name}”) in the condition to point the “is” (equal to) condition to a specific record field. Does someone know the syntax for referencing a field in the conditions? Or a way to dot walk tables in the formula in order to essentially recreate the “rollup” functionality in a formula?

[Emma_MH]

Whoa. How did you even get those options? I see no options to add conditions to a roll up! I have a Pro account and I do not see that little toggle button that you have. I looked in their support materials and it doesn’t say anything about needing to have a certain level account…

[Justin_Barrett]

Welcome to the community, @Winston_Chang! :grinning_face_with_big_eyes:

Winston_Chang:

Does someone know the syntax for referencing a field in the conditions?

At this time, field references only work in formulas. For condition setup in view filters or conditional lookup/rollup options, etc., all text references are literal. In your screenshot above, the condition was searching for the literal text “{Name}”, not the contents of the {Name} field.

Winston_Chang:

Or a way to dot walk tables in the formula in order to essentially recreate the “rollup” functionality in a formula?

This is also not possible. Formula fields can only pull data from other fields in the same table.

There may be a way to pull off what you want, but I need to mull it over a bit more.

Emma_MH:

I see no options to add conditions to a roll up!

At bottom of the setup dialog for a lookup/rollup field, you should see a small “switch” (see below). Switch that on, and you can start setting conditions.

[Justin_Barrett · Accepted solution]

Winston_Chang:

On the Inventory table I’d like to create a field that sums the amount of orders placed against an Inventory item so we can tell when we’re running low.

I don’t know why this didn’t click earlier, but you shouldn’t need to compare the {Fabric} field contents against the item’s name. Airtable does record-to-record links, meaning that an order record that links to the Item A in the [Inventory] table will only connect to Item A, and the rollup for Item A in [Inventory] will only pull in order records that link to Item A.

In short, you should only need to check the purchase status in your conditions. The item match is already guaranteed. However, if I’m misunderstanding something about your setup, would you mind sharing further details to clarify?

[Winston_Chang]

@Justin_Barrett Thanks for the kind welcome and support on this. You are correct. The roll up automatically filters by the record on the current table (I guess it might only rollup linked records, so technically not a filter). This does do exactly what I needed… looks like I was over thinking this.

I would love to see the ability to reference other table fields in the formula, that would be a powerful tool. Currently I’m assessing the platform for suitability for a company I’m advising so just testing out different functional requirements. So far enjoying airtable!