Multiple Variables Record Linking

[Rachelle_Richar]

Hello community!

I have a base I created to manage all tasks across multiple real estate development projects (>700 task records per project). There is a table that holds the Master template of all tasks required for each project, they’re linked to a project record and copied into another table [All Project Tasks] that holds all projects’ tasks (Linked to (1) [Projects]record, (1) [Roles] record as 'Owner' and (multiple) [Roles] records as 'Support').

Context:

Tables:

1. [Projects] - Summarizes each project (Geographic Location, Milestone timelines)
2. [Roles] - Records identifying 'Working Groups' associated with tasks (ie. Finance, Sales, Operations, etc.) linked to task records and [Person] records.
3. [Person] - Records linking individual users (Name, Email, etc) to [Working Group] and [Project]
4. [Master Tasks] - Master template of all tasks required for each new development project, linked to [Working Group] records. Records are linked to a project record before being copied into [All Tasks]
5. [All Project Tasks] - Records from [Master Tasks] are linked to a [Project] record and [Working Group] records. Project Manager is able to review all tasks across concurrent project by working group owners by timeline/due date.

Issue: [Person] records need to be associated with individual tasks in [All Project Tasks] depending on [Project] AND [Working Group] variables.

For example, Person A works in [Working Group] - ‘Operations’ on [Project] A and C and should be associated with [All Project Tasks] records linked to ‘Operations’ AND Project A and C.

Person B works in [Working Group] ‘Operations’ and ‘Finance’ and [Project] B and C and should be associated  with[All Project Tasks] records linked to ‘Operations’ AND ‘Finance’ AND Project B and C.

Is this possible? 

Thanks in advance community comrades.

[bruceconsulting]

Hi Rachelle, I would be happy to help you with this.  Best way is to schedule a (free) half hour appointment with me so I can look at your setup.  Here's my Calendly link:  https://calendly.com/bruce_consulting

[TheTimeSavingCo]

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

[bruceconsulting]

Hi Rachel, I am waiting for you on Google Meet.  But if you got side-tracked, here is some help with what I believe is your question:
- You want to associate a person to a task within a project.  Right now, you have roles/working groups assigned to tasks.  Since it appears multiple people can be in each working group, you in effect have associated multiple people to a project.
- Do you only want one person to be the owner of one task within each project?  If so, you could have a "task owner" field in your Master Task Template linking to the Persons table.  I'm assuming, however, that Person A may be the owner of Task A in Project A, but Person B may be the owner of Task A in Project B.  This can be done by linking that person from the Persons table to the Projects table and pulling that person into the All Projects Table as the task owner by using an automation.  Here's more info on how to set this up:
- You will need to add fields to your Projects table equal to the number of task owners.  For instance, you may need an Operations owner, Finance owner, etc.  As you create projects, you would populate those fields with 1 person from each of those roles who will be assigned to that task in the project.  In essence, you are creating fields in the projects table equal to the number of your working groups.
- You can then tweak your automation to populate the task owner field in your All Projects Tasks to pull in the person identified in the record from your Projects table.

Hope this helps!  I have set up projects for many clients, so please let me know if you would like to discuss using my services.
Regards,
Jody Bruce
https://calendly.com/bruce_consulting/30min

[Rachelle_Richar]

Hi Jody! I'm so sorry, the Calendly confirmation went to my junk and I missed it.  I've rescheduled time and am further along in my trial and error so I'm hopeful we may be able to solve it together. Thank you!

[Rachelle_Richar]

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!

[TheTimeSavingCo]

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