Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lookup values from multiple tables

Hi,   I have the below Structure where "Table 3" is the main table connected to other 3 sources with details. I'd like to create a formula (calculated column) that will lookup the "Code" value from...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Greg_Deckler Thanks for your suggestion. However, the full data model is pretty big and I'd like to avoid any bi-directional relationships. I was thinking of a formula like :

    Lookup Column = SWITCH(
    TRUE(),
    LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count])>1,LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count]),
    LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count])>1,LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count]),
    LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order])>1,LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order]),
    BLANK()
    )
     
     It seems that it works fine, but I'm not sure if I'm taking into account all possible implications....

    For your testing:

    Table1
    CountCode
    11234
    21235
    31236

     

    Table 2

    CountCodeDate
    212379/11/2020
    512389/12/2020
    612399/13/2020

    Table 3
    OrderCountusernameGender
    12432AAM
    24433ABF
    35434ACM


    Table 4
    OrderCodeDatestatus
    112399/13/2020Completed
    212409/15/2020Completed
    412419/16/2020Completed
    Thanks.
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - To ammend this, maybe try changing your relationship direction to both on Table1 and Table3? So, thinking in your Table:

 

Table3[OrderID]

Table3[Count]

Table1[Code]

 

This should work without any calculations if you change that relationship direction to Both

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Greg_Deckler Thanks for your suggestion. However, the full data model is pretty big and I'd like to avoid any bi-directional relationships. I was thinking of a formula like :

    Lookup Column = SWITCH(
    TRUE(),
    LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count])>1,LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count]),
    LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count])>1,LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count]),
    LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order])>1,LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order]),
    BLANK()
    )
     
     It seems that it works fine, but I'm not sure if I'm taking into account all possible implications....

    For your testing:

    Table1
    CountCode
    11234
    21235
    31236

     

    Table 2

    CountCodeDate
    212379/11/2020
    512389/12/2020
    612399/13/2020

    Table 3
    OrderCountusernameGender
    12432AAM
    24433ABF
    35434ACM


    Table 4
    OrderCodeDatestatus
    112399/13/2020Completed
    212409/15/2020Completed
    412419/16/2020Completed
    Thanks.
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous You could use LOOKUPVALUE. How are your Table1 and Table3 related? What columns? And is it 

       

      Table1  1->*  Table3 

       

      ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Greg_Deckler  Table 1 is related to Table 3 as One to Many by Count columns. Thanks.