Forum Discussion

ManuelAlvarez's avatar
ManuelAlvarez
Qrew Trainee
8 years ago

Is ti possible to autopopulate territory based on state field?

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
Formulas and functions

11 Replies

  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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
  • ManuelAlvarez's avatar
    ManuelAlvarez
    Qrew Trainee
    8 years ago
    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
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      In this situation where there is a clean native solution, what is wrong with staying native and just having a few tables where users can easily see what is going on.  In this case I don't think its a contest to get the functionality using the fewest number of tables and fields.

      The other native solution if there are few enough states is to just have an IF or Case statement to hard code a formula to map the states to the sales territories.  But I don't like that as much as it hides the assignment from regular users as they cannot see the formula.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      8 years ago
      > get the functionality using the fewest number of tables and fields.

      Fewer and shorter are generally better goals.

      Let's conceive of a metric called "noise ratio" which is defined as say the total number of fields in a table divided by the number of raw information bearing fields in a table. Using (1) summary fields, (2) lookup fields, (3) formula URLs and (4) the creation of extra tables just increases the noise ratio. It may not be a problem for the native implementation of any one particular feature in a table but over time hundreds of new fields get created that only inflate the noise ratio. I have seen client applications with dozens of fields that don't even get used but nobody dares delete them for fear the field may brake something if deleted.

      >just have an IF or Case statement to hard code a formula to map the states to the sales territories.

      As to your second point, if you use a formula what happens when the regions change their definition over time. If you use a formula the historic region associated with the state will change.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      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.