Conditional rollup of array of names [SOLVED]

[zack_mezera]

Hello—

Not sure what to call this one. I have two tables. I am trying to make a list of names in Table B showing which students are members of our program at that school.

Table A: Students

• {First name} and {Last name} of students
• {Member} Checkbox field
• {School} linked record field
• {Name} function field, with an emoji
  (For example, a name would show up as "🐧 Robert Frost" if they are checked, and without the emoji if not)

Table B: Schools

• The linked student field is called {# Students}

The goal is to make a list in Table B of {Member}=1 students at each school. For instance, if Table A lists “English High School” having 10 students attending that school, but only 3 members, then the rollup in Table B should show: "🐧 Robert Frost, 🐧 Doctor Freeze, 🐧 Elsa Arendelle"

Any recommendations on how to structure the rollup field in Table B? I have tried and failed at two approaches:

1. Making the ARRAYJOIN contingent on whether the {Member} field is checked in Table A. For example, the Rollup is set to {# Students}, {Member} and the formula is if({Member}=1,arrayjoin(values, ", "),"") I guess this hasn’t worked because the {Member} field is in Table A, not the current table?
2. Making the ARRAYJOIN contingent on whether there is a “🐧” as the first letter of the {Name}. For example, the Rollup is set to {# Students}, {Name} and the formula is if(left({# Students},1)="🐧"),arrayjoin(values,", "),""). Honestly, I’m not sure why this doesn’t work.

I’m at a loss here, and maybe it’s time to just hand-type the names in. But I’m interested by the puzzle of it now, and wondering if the community has advice. @W_Vann_Hall, can you be my airtable hero?

Thank you!

[W_Vann_Hall]

I don’t know how I missed seeing this until today — but I’m afraid I don’t have good news for you…

Here’s a quick way to demonstrate the root of your problem: Define a rollup field in [Table B] that rolls up {Table A::Name} with the aggregation formula values – just values, all by itself. You’ll find it contains all 10 of the students’ names, both with and without penguin.

I find I get tripped up by this at times, thinking an aggregation formula can influence what gets aggregated, rather than simply defining what happens to the aggregate. As long as you are accessing {Name}, though, you’re going to get all linked records returned to you as either a string or an array. (And since Airtable currently doesn’t support any array functions other than ARRAYUNIQUE(), ARRAYCOMPACT(), and ARRAYFLATTEN() — and I’m not sure how one makes meaningful use of the last one, as I’ve not figured out how to combine arrays without all but one of them being converted to strings in the process — you might as well just think of it as a string.) This leaves you in the unenviable position of having to step through the string and extract everything between '🐧' and ',' — explicitly, up through the maximum number of students you anticipate…

…that is, if you base {# Students} on name.

You’ve probably twigged to this already, but the solution is simply to define both {Name} and {🐧Name} in [Table A], with {🐧Name} configured as

IF(
    {Member},
    {Name}
    )

You’ll probably want to roll that up using the aggregation function

ARRAYJOIN(
    ARRAYCOMPACT(
        values
        ),
    ', '
    )

as otherwise you may end up with something along the lines of

,,🐧 Robert Frost,🐧 Doctor Freeze,,,🐧 Elsa Arendelle,,,

I’ve yet to suss out when this does and does not happen, but even setting an explicit BLANK() for the ‘else’ part of the IF() statement doesn’t always suppress the concatenation of empty strings.

[zack_mezera]

Thanks for the response, @W_Vann_Hall! I really appreciate it, and your suggested solution worked perfectly without any empty strings and commas (for now). The solution (defining the valid members in Table A) was so simple and elegant—which is what I suspected the solution would look like—but it nevertheless always felt so out of reach, making this particular challenge all the more frustrating.

Big thank you again for the advice, and for your regular engagement on the forum. I’ve solved many other challenges by reading through your thorough responses to other folks.

[W_Vann_Hall]

And thank you for the kind words — especially during a several-week period when I feel I’ve utterly neglected the forum. Fortunately, there are a number of other highly involved Airtable users quick to help — @Jeremy_Oglesby, @Elias_Gomez_Sainz, and @Julian_Kirkness — all spring to mind — when I don’t beat them to the punch; the frequency with which I sometimes post has more to do with my peripatetic sleep cycle and uninspiring social life than to any special expertise… :winking_face:

—which is a good thing, as I’m not sure how deeply I’ll manage to remain involved over the coming months. To an extent, my participation here has been in part subsidized by rent control. Having had two residences sold out from under me in a little over four months, I once again find myself having to figure out what I want to be when I grow up…

[zack_mezera]

Sorry to hear times are tough. I think I speak for the entire Airtable community—and for the decent people of the world generally—when I say that I hope you land on your feet. Maybe time to become an astronaut?