Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 09, 2023 05:04 PM
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?
Solved! Go to Solution.
Jun 10, 2023 03:20 AM
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.
Jun 10, 2023 03:20 AM
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.
Jun 10, 2023 03:30 AM
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.
Jun 12, 2023 04:32 AM
Got it... Was hoping it wasn't as clunky as having to surface the information in the intermediary table.