Get info from a linked record within a linked record

Question

I've got a table with People information

Name	Gender	Ethnicity
Sally	Female	Black
John	Male	White
Mark	Non-Binary	White

And a table with events:

Event	Date
Party	2022-01-02
Service Project	2022-02-03

And a third table with lookups that pair people with events:

Master List

ID	Event	Person
1	Party	Mark
2	Party	Sally
3	Party	John
4	Service Project	Mark

So, what I need is a way to summarize the attendees, like how many white people were at the Party.

Seems like I should be able to do that with rollup fields on the event (since it's linked to the people on the Master list) but while I can get a list of the "person"s who were at the event, and get a list of those on the "event" table, I don't see how to probe the people table to get the info I want...

Do I need another table?

Stephen_Orr1 · Accepted Answer

You can use roll-up fields with conditions applied to summarize/aggregate.

In your cross reference table, pull in ethnicity as a lookup. Then in the Events table create a roll-up with the condition you want on the lookup. For the aggregation formula use COUNTA(values) to count all text values.

Stephen_Orr1 · Answer

Also, I forgot to mention, you can use the Count field type for this as well with conditions applied. The roll up in my initial comment is doing the same thing as the Count field. If you want “X share of attendees” to show percentage of ethnicity, then you can create a formula field with a conditional count / total count and formatting set to percentage.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded