Hm, I can't think of a way to do this without a script I'm afraid.
You mentioned all the records in "Master Tasks" were linked to the right Project record before being copied over to "All Tasks", so I figure this is what it sort of looks like?
If so, I think I'd recommend a script extension that you clicked a button to activate, and it'd figure out where there was overlap between the people linked to the Project AND linked to the Working Group that would then update the "People" field for you
Hey Adam,
What you've shared is pretty close but with a few additional complexities. I've added screen grabs of a simplified demo base for more context, thanks for the idea.
I don't want to link manually Person records to each task because I want to copy/paste the tasks from [Master Tasks] into [Current Projects' Tasks] and only have to manually link the [Project] to these tasks. There are over 700 tasks to be copied each time there is a new project and over 100 Person records across all projects. I want to be able to keep tasks associated to [Roles] only and have [Person] records associated depending on the variable of [Role] and [Project].
Would a formula field in [All Project Tasks] using a combination of the FILTER, LOOKUP and JOIN functions work?
In your reply you mentioned a 'button' to run a script, would this need to be done for every task record?
Thank you thank you!
Hmm, yeah, I'm not sure I fully understand your flow either, apologies. I'm going to give you my understanding of what you're trying to do, could you let me know where I've got it wrong?
Let's say you've got two people in the "Finance" role, Person B and Person C.
And project wise, Person B is linked to P1 and P2, and Person C is only linked to P2
At this point, you want to create the tasks for the project P1, and so you go to "Master Tasks" and it looks like this:
You know link all the tasks to project P1, so it looks like this:
And since only Person B is linked to P1, that's the only person that should be linked to any tasks that are marked as "Finance" and for the project "P1", so it'd look like this:
Note as well that the task "T1" is left empty as no people with the "Operations" role are assigned to P1
And from here, you'd copy all of these records and paste them into the "All Project Tasks" table, and then, in "Master Tasks", clear the "Projects" and "People" fields in preparation for the next time you needed to use it
If this is sort of correct, then the workflow would be:
1. You link all the "Master Tasks" records to the right project
2. In the script extension, you click a button
3. The script figures out the comparison and links every record as needed
4. You copy the records from "Master Tasks" into "All Project Tasks"
At this point you could get whoever's writing the script for you to also create the records, really
Or even just have a button field in the "Projects" table that you clicked to activate the script extension, and it would look through all the "Master Tasks" records, create them in "All Project Tasks" and then link'em to the right people
Hah, this is the dictionary definition of feature creep though, so I figure just go with the "Link the right people in the 'Master Tasks' table" script
