Forum Discussion

NickBellows's avatar
NickBellows
Qrew Member
3 years ago

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...
Relationships
MikeTamoush's avatar
MikeTamoush
Qrew Commander
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
------------------------------
NickBellows's avatar
NickBellows
Qrew Member
3 years ago
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's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    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
    ------------------------------