Help

Count the number of unique values across 4 linked record fields

Topic Labels: Data Formulas
1784 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_Ophoff
6 - Interface Innovator
6 - Interface Innovator

Hello - hoping someone can help! I feel (hope?) the solution is simple, but I am totally stumped!

GOAL: I am trying to summarize the total number of unique values across 4 linked record fields. 

CONTEXT: I run a coaching business that hosts group coaching sessions (called cohorts) for clients. These cohorts sessions can span across 2-3 months. I currently have a table that summarizes the total number of group sessions for each month / client. 

In addition to tallying up the total number of group sessions, I also want tally up the total number of unique cohorts that are meeting monthly. 

ATTEMPTED SOLUTION: I thought that maybe I could create two additional fields (1) that combines all the linked records for the month / client into a comma separated list and (2) a field that then counts the number of unique values within that string of text... but I can't get that to work. Maybe because its completely wrong! Should I be using a Roll-up field instead? I am totally stumped! 

 

VISUAL: Here is a visual of what I am trying to accomplish. The left represents the month / client (different clients are represented by different colors) and the 4 columns from the right are the linked records representing the cohorts (cohorts are represented by different colors) 

Any recommendations for what I need to do to create the "Count of Unique Linked Records" column? 

Screen Shot 2023-08-18 at 11.39.10 PM.png

Thanks in advance for your help! 

10 Replies 10
Sara_Ophoff
6 - Interface Innovator
6 - Interface Innovator

@pressGO_design scripts and I haven't even met yet 🤣 but I feel the intro is coming haha.

This is super helpful - going to play around with what you shared here and will report back if I get stuck again!

Really wishing there was a simple formula that could look at a string of text that is separated with commas and pull out the number of unique values. That would really save me here!