Create a table relationship that joins on the concatenation of two fields and autofills lookup value

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered

How do I create a table relationship that joins on the concatenation of two fields in the MASTER and DETAILS tables, then autofills the lookup value in a field in the DETAILS table.  (See attached for illustration).  I reached out to QuickBase support last week and he wasn't able to resolve for me.  Help, anyone? :) 

Photo of Angel Sweatt

Angel Sweatt

  • 90 Points 75 badge 2x thumb
  • frustrated

Posted 5 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
Great question and nicely explained.  There is an easy answer that I will post later today unless someone beats me to it.  Just tied up now.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
You will need to set the Key field of VENDOR table to be a field like

State-Product

Then on the relationship to the ITEMs (really these are Order Items, you will make a formula field like 

List("-", [State], Item]) 

 and use that as the reference field on the right side of the relationship to lookup for Vendor for that state for that item.

So, now the issue is to how to get the Key field of the VENDOR table to be that concatenation.

There are two methods.

The traditional method would be to make a formula field like List("-", [State], Item]) called perhaps

[State-Text (formula)]

 and using grid edit copy paste or another means, populate a text new field called [State-Text (key)] and use a form rule or an Action to maintain that going forward.  There is a trick I can explain on how to get an Action to edit the record you are on, if you like.  The limitation with a form rule is that it will not work in Grid edit.

The other choice is a bit more obscure and should work fine for you unless you do a lot of importing into that Key field - and that is that you can set your Key field of the table to be a the formula field.[State-Text (formula)]

To do that you hand type an API to change the Key field.  First turn off Application tokens in the Advanced Properties for the app.  *you can turn them back on if you like right after you do the API

just go to the VENDOR table and observe the URL.  get rid of everything after the ? but retain the ? and type

API_SetKeyField&fid=99

where 99 is the field ID# for 

[State-Text (formula)]
Photo of Angel Sweatt

Angel Sweatt

  • 90 Points 75 badge 2x thumb
Thanks so much! I’m out of town for a few days but will try your instructions on Monday at the latest. I love to hear “easy” :).
Photo of Angel Sweatt

Angel Sweatt

  • 90 Points 75 badge 2x thumb
So sorry for the delay...  I was finally able to try your instructions, and was able to get it to work with a slight tweak on the VENDOR table.  I hadn't mentioned that the table is linked to a GOOGLE DOC (.csv) source, so I wasn't able to create a formula text field and then use the API to set the primary field.  However, I simply added another field in my source table called "Key" that is the concatenation formula per your instructions and was able to follow all other instructions and it's working BEAUTIFULLY!!  

So, now wondering if you could help me with my last step which is to link either the VENDOR or the ITEMS table to my PLANNER table to pull in the Lead and Support resource based on the VENDOR that was just pulled into the ITEMS table.  See attached diagram?  

I wasn't sure how to create another relationship based only on the VENDOR field as the join when the primary key for the VENDOR and ITEM tables are concatenations. 

THANKS again for your help!!  I'm SO GRATEFUL!  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
From your diagram, it looks like Vendor on the Vendor Table is just a free form typing field. That table should be renamed to what it is, which is Vendor State Assignments. The column for Vendor should come off the Planner table via a lookup as that table has the unique list of Vendors. The planner table should be renamed to be Vendors and before making the relationship to Vendor State Assignments, the key field should be changed to Vendor.

Then look any fields you nee from Vendirs (formerly planners) down to Vendor State Assignments, such as the Planner. Then from there lookup the planner lookup field down to Item. So a double hop to get there.