Forum Discussion
MCFNeil
8 years agoQrew Captain
You will need to have a "State" table and a "Territory" table.
Make the Key field on the "State" table be the state. And related them together so each Territory may have many States.
Also make the relationship that each State has many "Clients" (assuming your 3rd table is Client, or customer).
Then you can make that "State to Client" relationship a formula-text reference field, that looks at the "Address: State/Region" field.
Thus anytime you add an address that has a state and it matches your "States" table, you can automatically have the Territory populated. (passed via Lookups from Territory -> State -> Client)
Side note: I do this in about 30% of the apps I build, and I usually go as granular as zip code for defining territories.
Make the Key field on the "State" table be the state. And related them together so each Territory may have many States.
Also make the relationship that each State has many "Clients" (assuming your 3rd table is Client, or customer).
Then you can make that "State to Client" relationship a formula-text reference field, that looks at the "Address: State/Region" field.
Thus anytime you add an address that has a state and it matches your "States" table, you can automatically have the Territory populated. (passed via Lookups from Territory -> State -> Client)
Side note: I do this in about 30% of the apps I build, and I usually go as granular as zip code for defining territories.