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

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.

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)]

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� :).

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!  

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.