Is ti possible to autopopulate territory based on state field?

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.

> 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.

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.

> 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.

:) 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.

Yes, Sales People will be the first we replaced with bots.