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 lTable 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.
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 lTable 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.
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.
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.
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…
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…
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?
