Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Get info from a linked record within a linked record

Topic Labels: Data
Solved
Jump to Solution
802 3
cancel
Showing results for 
Search instead for 
Did you mean: 
ECTA
5 - Automation Enthusiast
5 - Automation Enthusiast

I've got a table with People information

NameGenderEthnicity
SallyFemaleBlack
JohnMaleWhite
MarkNon-BinaryWhite

And a table with events:

EventDate
Party2022-01-02
Service Project2022-02-03

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

Master List

IDEventPerson
1PartyMark
2PartySally
Party John
4Service ProjectMark

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?

1 Solution

Accepted Solutions
Stephen_Orr1
10 - Mercury
10 - Mercury

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.

See Solution in Thread

3 Replies 3
Stephen_Orr1
10 - Mercury
10 - Mercury

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.

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.

ECTA
5 - Automation Enthusiast
5 - Automation Enthusiast

Got it... Was hoping it wasn't as clunky as having to surface the information in the intermediary table.