Conditional Look Ups across Tables in 1 App
I have a table with a record for each Cohort. Within a Cohort Record are fields for identifying the Director, Manager, Analyst assigned to that Cohort.
For example:
Cohort A; Director=Tom, Manager=Jane, Analyst=Mary
Cohort B; Director=Angela, Manager=Glen, Analyst=Stef
I have a second table with tasks by position.
For example:
Task 1; create kickoff presentation; Director
Task 2; Schedule presentation; Manager
Task 3; Present; Analyst
In the second Table, when I select the Cohort for a task, I want to pull in the appropriate person based on the Position that owns the task.
For example:
I assign Cohort A on Task 1. Since the Position is Director I want to pull in Tom.
I assign Cohort B on Task 2. Since the Position is Manager I want to Pull in Glen.
I have created a table to table relationship that allows me to Choose the Cohort for the task in Table 2 from Table 1. But I can't figure out how to make the person field populate based on the combination of Cohort and Position.
In reality I will have more than 50 positions and the people in those positions can change from Cohort to Cohort.
I suspect there is some sort of Get Values type formula needed here but I am stumped.
Any guidance would be appreciated.
------------------------------
Nick Bellows
------------------------------
For example:
Cohort A; Director=Tom, Manager=Jane, Analyst=Mary
Cohort B; Director=Angela, Manager=Glen, Analyst=Stef
I have a second table with tasks by position.
For example:
Task 1; create kickoff presentation; Director
Task 2; Schedule presentation; Manager
Task 3; Present; Analyst
In the second Table, when I select the Cohort for a task, I want to pull in the appropriate person based on the Position that owns the task.
For example:
I assign Cohort A on Task 1. Since the Position is Director I want to pull in Tom.
I assign Cohort B on Task 2. Since the Position is Manager I want to Pull in Glen.
I have created a table to table relationship that allows me to Choose the Cohort for the task in Table 2 from Table 1. But I can't figure out how to make the person field populate based on the combination of Cohort and Position.
In reality I will have more than 50 positions and the people in those positions can change from Cohort to Cohort.
I suspect there is some sort of Get Values type formula needed here but I am stumped.
Any guidance would be appreciated.
------------------------------
Nick Bellows
------------------------------
