Formula To Identify Relationship Between Multiple Rows

[Ian_Kuhn]

Hi all,

I am looking to identify if a team is onboarded. We define onboarded if at least one of the designated people on the team participated in a session. Take the example below (columns hidden to simplify the example). The first column (physician team name) is the name of the team. The team consists of three people including John Doe, William Smith, and Sarah Johnson.

We can see that Dr. Doe onboarded and he is considered a “live” user. His two colleagues are not, but because he is live that means the entire team is live.

The ultimate goal is to group by Live vs Not Live and then Team Name like the screenshot below. This doesn’t work though because Neuroscience Team shows in the live section because Dr. Doe is live, but then also shows in the not live section because the other two did not participate in training. How do I create a formula so the Neuroscience team only shows as live? We cannot filter out the other two individuals from the grouping.

Does anyone have any clever ideas on how to handle this? Thank you!

[Rupert_Hoffsch1 · Accepted solution]

Hi Ian,

1. Why don’t you make a table, where each record is a team and then you link team members from a separate table? That way, you wouldn’t have multiple records with the same name.
2. That would also get rid of multiple records for the same team and hence showing the team multiple times in a group view.
3. I think the logic behind the team members and “live” is: As soon as one team member has done the onboarding, the team is live. You could change the formula to “isLive?” and only show “Live” if “Onboarding Date” has a date. You can then go to your teams table and use a rollup field summarizing unique values. If the summary leads to “Live”, the team is live.

Let me know if that makes sense :slightly_smiling_face:

[Ian_Kuhn]

Great idea - let me try that out. Thanks Rupert!

[Ian_Kuhn]

This idea works well - thanks again Rupert. One other question that comes to mind.

I have a new table with the team name in the primary column and the original table with the team member in the primary column. You mentioned I need to link team members to this new table. I was hoping there would be a vlookup type formula where I could say “if the team member is part of the neuroscience team” (I could keep a similar column like my first screenshot) then automatically add to the new team table row. I don’t see a way to do this besides manually entering in each team member. Is there an automated way to do this / does that question make sense?