Forum Discussion
QuickBaseCoachD
6 years agoQrew Captain
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)]
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)]