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