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? :)
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
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
where 99 is the field ID# for
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!
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.