Filtered Rollup of Linked Records

[Alex_Carr]

I’m looking to show a subset of linked records based on a column in the linked records.

The following has a setup for the example: https://airtable.com/shrRWeYffJnFOYXE6

If we had a table of sandwich elements like ham, lettuce, tomato, etc…, we could then have a table of sandwiches, that used elements from the elements table.

In that sandwiches table, how could we then show what types of “Meat” are in this sandwich or what types of “Vegetables” are in this sandwich?

[Jeremy_Oglesby]

1. Create a Formula field for each “Kind” element you want to total in the Elements table with this formula:

IF(Kind="name_of_kind", 1, 0)

Put this in a field called “Meat” and replace name_of_kind with Meat, and the field will equal 1 when “Meat” is selected as the element kind, and 0 if anything other than “Meat” is selected.

2. Make your Totaling fields in the “Sandwiches” table Rollup fields like so:
   

Here’s your example base back with the edits - copy it to your workspace and take a look:

Airtable

Test - Airtable

Explore the "Test" base on Airtable.

Once you get them working, you can hide the 1/0 fields in the “Elements” table to reduce visual clutter - you just need them for the Rollup Calc.

[Alex_Carr]

Thanks taking the time with this @Jeremy_Oglesby. I’m actually not looking for the number of that type but the actual list of types.

For example, “John’s Sandwich” would list “Chicken, Bacon” for Meats and “Lettuce, Tomato” for Vegtetables.

[Jeremy_Oglesby]

Ah, gotcha.

Hmm… I don’t think you could do this easily. The only thing that comes to mind is to make a long, nested conditional where you’d have to manually declare each possible ingredient that falls into that element “Kind”.

CONCATENATE(IF(SEARCH("Chicken",Elements)," Chicken "),IF(SEARCH("Bacon",Elements)," Bacon "),..)

This works by searching the array in “Elements” for the string defined (“Chicken”) - if it finds the string it returns true to the IF() statement, and if it doesn’t find the string it returns false. If true, the IF() statements puts the string defined (" Chicken "), and if false, it puts nothing. CONCATENATE will smush all the strings that get returned together into a single string.

It’s not elegant - you have to manually define each possible element that you’d need to look for - so each time you add a new element to the elements table, it has to be added to the formula as well.

But, it works… I guess?

[Alex_Carr]

Thanks @Jeremy_Oglesby. Yeah that was the solution I was looking to avoid. Both for the reason you mentioned but also because some of these titles are longer and will make that tiny formula field pretty rough.

Thanks though.

[Jeremy_Oglesby]

If it’s any consolation/help, I use a free, external text-editor to write my formulas, and then copy-paste them into airtable.

Personally, I use VS Code to write them, which makes visualizing really long, hairy formulas much easier:

You don’t have to know anything about coding or how to use the text-editor in fancy ways. Just launch it, hit cmd/ctrl + N to create a new document, and start typing. When you type IF( it will auto-make the closing parenthesis for you - hit enter and it will auto-tab (indent) the next line for you - start typing the name of a field you’ve already typed earlier in the formula and it will suggest an auto-complete for you - etc…

Then, you can also save your formulas to your desktop or elsewhere, and when you need to add something, open it up and edit it in VS Code, and then copy and paste again to Airtable. It might make your life a little easier if you regularly write long formulas for Airtable.

Some other free options for text editors include Atom and Notepad++.

[Alex_Carr]

Thanks @Jeremy_Oglesby. I might switch to doing that.