Is ti possible to autopopulate territory based on state field?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Good day evreyone, 

We have divided the country in 4 geographical territories for our sales people, we want whenever we fill the state field automatically fill the territory, is it possible?

someone can help? 

ex. State= Jalisco then Territory= Occidente, State=Oaxaca then Territory= Sureste, and so on.

Thank you
Photo of Manolo

Manolo

  • 214 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
You can do this without any extra tables if you use script and may well obtain a better solution if the States and Territories do not change frequently (they don't).

I assume your Territories are not political subdivisions but rather groupings assigned by marketing/sales. Would you mind posting your 4 subdivisions for the 31 states for Mexico?

(1) Chihuahua
(2) Sonora
(3) Coahuila
(4) Durango
(5) Oaxaca
(6) Tamaulipas
(7) Jalisco
(8) Zacatecas
(9) Baja California Sur
(10) Chiapas
(11) Veracruz
(12) Baja California
(13) Nuevo León
(14) Guerrero
(15) San Luis Potosí
(16) Michoacán
(17) Campeche
(18) Sinaloa
(19) Quintana Roo
(20) Yucatán
(21) Puebla
(22) Guanajuato
(23) Nayarit
(24) Tabasco
(25) México
(26) Hidalgo
(27) Querétaro
(28) Colima
(29) Aguascalientes
(30) Morelos
(31) Tlaxcala
Photo of Manolo

Manolo

  • 214 Points 100 badge 2x thumb
Thank you for your answers,  exactly there are not political subdivisions, sorry I mistake there are no 4 but 7

1. Bajio
2.Centro
3. Noroeste
4. Noreste
5. Occidente
6. Oriente
7. Sureste
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
States And Regions ~ Add New Record
https://haversineconsulting.quickbase.com/db/bm6zr9qak?a=nwr

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=609

Notes:

(1) The States and Regions are unlikely to change short of a new government forming or a change in your Sales Regions so there may not be any good reason to store the State or Region data in one or more related tables. Doing so will just crowd up your application with superfluous fields and tables.

(2) Using table relationships would coerce historic data to map to new states or regions when this probably is not the desired behavior.

(3) This is a very generic solution that can be adopted to any type of lookup scenario.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
The native solution to your 2nd issue, which is valid, is to use a snapshot field for the lookup. Yes, yet one more field.

For the 1st issue, how does the sales manager go into to make a territory change under your solution? how does he stand back and look at he current territory assignments and then make changes?  He can't see the logic of the assignments as they are buried under the covers and he can't just go in himself to make a territory change when say the new fiscal year rolls around and he needs to hire a new sales rep and re-jig the territories.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
> use a snapshot field for the lookup

I concede this and I knew you were going to mention snapshot. But there is a lot of cognitive overload keeping all these fields types straight in your mind:
  • formula field
  • summary field
  • lookup field
  • proxy field
  • snapshot field
  • record picker field
  • key field
  • reference field
  • base field - term shows up in some API methods
  • virtual field - term shows up in some API methods
  • built in field
  • composite / compound field (addresses, phone numbers with extensions)
  • custom field
  • columns sometimes used when referring to a field in a report
  • connected field
>how does the sales manager go into to make a territory change under your solution?

You just change the mapping like so:
var map = {
  //"Aguascalientes": "Region1",
  "Aguascalientes": "Region2",
  ...
}
Admittedly a sales manager may not feel comfortable changing even one line in the script but there are all sorts of ways to make this easier including sending themn to a re-eduction camp or encouraging QuickBase to build more script hooks into the product.

Anyway I think this is a fruitful discussion of the pros and cons.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
:) I don't think that I want the sales manager to have admin access to the app.  They are usually really good with selling and schmoozing people and really bad with computers.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,512 Points 20k badge 2x thumb
Yes, Sales People will be the first we replaced with bots.

Photo of Manolo

Manolo

  • 214 Points 100 badge 2x thumb

Thank you for your comments! It ́s been very helpfull