How to determine a field from two (or even more) other fields?

[Mark_Wallace1]

Alright...I think I'm being super dense here, but I can't figure out how to do something that is probably super basic.

In this base : https://airtable.com/shrKq03xsOCwHUmbL

What we have:

• Projects table; a list of projects
• People table; a list of people, with some links where I can assign them to a role, a team, and a project. 
• Lifecycle table; here is where I want to be able to automatically assign task ownership, due and actual dates (although I haven't bothered putting the date fields here yet), etc. This is the table where I am struggling, as I will describe in a bit.
• Teams table; here is where I am creating project teams, assigning them to specific projects (via links to Projects), and putting people on those teams.
• Tasks table; this is a preset list of tasks and which role on the team owns the task. For example, Task 1 is always owned by whomever on the team is the Product Manager. Task 2 is always owned by whomever on the team is the Program Manager, etc.

What I am trying to do is to establish logic in the Lifecycle table that (for instance, on Row 1) autopopulates the name of the specific person who owns all of the tasks that their role is defined as owning in the Tasks table.

So, for Row 1, I want to be able to have a column/field that autopopulates (I presume through a Lookup?) the name of the person who owns Task 1 being done on Project 1. I am doing a lookup to pull from the Tasks table that Task 1 is always done by a Product Manager. In the People table, I have established that John Smith is the (or a) Product Manager on Project 1, but I am not getting how to automatically have John Smith's name show up in what I am calling the Owner By Name field in the Lifecycle table. (Note : Owner By Name is a link field right now, but I'm sure it isn't supposed to be. I just don't know what to make it instead).

The use case here is that I am running multiple projects with many team members, each playing a role in which they own a preset set of tasks. In that Lifecycle table, I want to track each and every task and have it autoassign to each team member depending on their role and which team they are on. Then, when that is done, of course I can group and track tasks by the name of the particular individual on the project than just by their role.

I think I may have kinda wrapped myself around the axle with the links that I have created, in the process maybe creating too many and/or forgetting a key one here or there. I also figure that I am just missing something really easy and don't have to do this through either a script or an automation, but there again maybe I'm wrong.

Any help/suggestions/modification would be appreciated!

Thanks,

Mark

[Mark_Wallace1]

Note : I know I didn't mention usage of the Teams table at all. I did that only because I doubt that it is relevant to my goal of being able to autopopulate the owner of a given task based on his role and which project he is on. If someone sees a use for that though, I'm all ears.

Thanks again.

[Mark_Wallace1]

Apologies to anyone who may be interested in helping with this. I just realized that I am sitting here monkeying around with the base and have undone much of what I just described in the original post. Working on fixing it. 

In the meantime...hoping that just my text description is useful for anyone who wants to help.

[Mark_Wallace1]

Any suggestions on this? Thanks!

[TheTimeSavingCo]

Hmm, did we rename the "Lifecycle" table to "Task Tracking"?  Also, is your end goal to be able to go to the "People" table and see all the tasks that are assigned to them?

If so, I think you could try adding a formula field to the "Task Tracking" table that will output the name of the person in charge of the task based on the task type (i.e. a formula that will output the name in the "Product Manager" lookup field if the task's "Roles" value is "Product Manager")

You'll then need to add a linked field to the "People" table, and then copy and paste the value from the formula field above into the linked field

Now when you go to the "People" table you'll be able to see that person's assigned tasks

You can also create an automation that'll do the copy pasting for you once you've set everything up

[Mark_Wallace1]

I don't know that I would have called that (seeing the assigned tasks per person in "People") a goal, but it absolutely wouldn't hurt.

(Yeah, I did some renaming and mucking around with the base after my OP, hoping to figure this out on my own.)

Let me think about what you said and see if I can do it on my own. I'm not sure if it will give me what I need though in only outputing the name of the actual Product Manager (or whatever) assigned to that particular project. Maybe it will though. 

Thank you! Let me see if I can make that work.

Mark