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, Manage...
Forum Discussion
Can you create 3 lookup fields for the director, manager, and analyst, and then just create a formula user field on your task table?
Formula on task table would be: If([Position]="Manager", [Manager Lookup Field], "Director", [Director Lookup Field],....)
------------------------------
Mike Tamoush
------------------------------
Formula on task table would be: If([Position]="Manager", [Manager Lookup Field], "Director", [Director Lookup Field],....)
------------------------------
Mike Tamoush
------------------------------
- My concern is scale. My example is a much scaled down version of my actual situation. In reality I have something like 50 positions and will eventually have dozens of cohorts. I don't want to the field to be selectable from a list of Managers to choose. I want it to populate with the name for that position in Table 1.
I am starting to think that because of the conditional nature i.e. Task 2 is assigned to Cohort B and the owner of that task is Manager, I need to create a query to the First Table. Not sure I know how to do that.
------------------------------
Nick Bellows
------------------------------- MarkShnier__You
Qrew Legend
I suggest that you make a Child table of Cohort Positions.
One Cohort has many Cohort Positions. Set a Custom Key field to be the hyphenated value of [Related Cohort] and Position.
Make a formula to calculate the key field and then use a form rule to populate the scalar Key field.
Then this Cohort Positions table can be a Parent to Tasks. The field for Related Cohort Position on the child can be calculated.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
