Counting unique linked records across two or multiple fields

[SaS_AT]

Hello Community,

I have a layered table where I have a Staff Table that is used to assigned Projects and Tasks.

I am connecting three (3) tables: Projects, Tasks, Staff

In the Projects Table, Staff can be assigned as a Project Lead or as Part of the Project Team

In the Tasks Table, there is one field, "assigned to" for any Staff to be assigned to the Project.

In case a user assigns a task to Staff not already on the Project Team, I have an automation set up to add them.  However, when they do, it means that someone who is a Project Lead may be duplicated in the Project Team.

This is an issue because I have a simple count field set up to count the total number of projects they are on 

SUM({Project Count: Responsible (Project Lead)}, {Project Count: Supporting})

With the automation, their project count is counted twice if they are in both the Project Lead and Project Team fields.

I have considered new automations, rollups, and formulas, but the complexity of having the linked records in TWO fields is the complication.  If automations or conditions allowed for dynamic connections, this could work.

Happy to explain more.  Does anyone have any thoughts?

[TheTimeSavingCo]

Could you set up your automation to check whether the staff member is set as a project lead for that Project before adding them to the Project Team field?

Apologies, if this is off base, I'm finding it hard to visualize how your base is set up; if you could provide some screenshots and example data that would be really helpful

[SaS_AT]

Hi @TheTimeSavingCo - thanks for the thoughts!

Yes, I think I can try that, although conditional automation only allows me to choose "contain" and not "has any", however, I need the automation to run no matter what because the "Assigned to" field may contain other new people, I simply want to single out the "Project Lead" and either not add them or add them in the automation and then set another automation to remove them.  I feel close but am running in circles.

I have three tables the matter: Projects, Tasks, Staff

The automation is set-up in staff, below is a dummy example of how it's set up.  For Task 1 P1, the automation would run to add Jane to the Project Team, however, I do not want her there since she is already the lead.  If I had another person such as "Bob Smith" there as well, but not already on the team, I would still want Bob added, but not Jane. 

Let me know if that helps - I know this is possible, but not sure which order to set up the automation.

[TheTimeSavingCo]

Yeap thanks for the details!

> conditional automation only allows me to choose "contain" and not "has any", however, I need the automation to run no matter what because the "Assigned to" field may contain other new people,

Yeah, if I were you I'd use "contains" and check whether either the "Project Lead" or "Project Team" contained the name of the project lead, and if it didn't, add that person to the "Project Team"

I'm a bit worried about an edge case where two people have the exact same name, and so would advise you run the "contains" check against a unique identifier like their email or something

[SaS_AT]

Thanks! Ah ok - this makes some sense. 

First, I want to make sure to update the project team with the "assigned to" names and simply not include the Project Lead.  How do I set it up to still update but only with names that are not the project lead.  Multiple steps?

Do I even need a find records?  

I have not used conditional functions for automation yet, but trying now.  I find they can be confusing when figuring out where to pull the info from (which previous step) and also what to link it to.  I am having an issue with grayed-out options when trying to connect dynamically, and I assume it has to be due to the data string since the Project Team is a multi-select. If you're still with me, see if the screenshots make sense.  What is the best workflow? Remember, I still need it to run even if the project lead is on the project team and simply only move over any new people, omitting the Project Lead.

Let me know if that makes sense.  Thank you!

[SaS_AT]

Not sure if my most recent post helped, but to aid in possible solutions, I either need to remove the Project Lead for the Project Team or somehow figure out how to adjust my total project count.  Ultimately, the issue is that the Project Count is off when the Lead is in two fields.  

I have three fields for staff records, Project Team count and Project Lead count then a formula that adds them up.  Super simple.  Can I adjust the total Project formula to recognize the duplicate names, and only count once?  I have attempted roll-ups and unique arrays, but no luck.

[TheTimeSavingCo]

Yeap, what difficulties did you face while attempting to set up the automation I mentioned?  Once you've got that set up it will no longer add them to the team if they're already set as the leader