Grouping states into Regions 1

[towntopic]

I'm trying to accomplish the following.

I have a customer database which includes their state (string) and I'd like to put in a group formula field that looks at the state of the customer and puts them into a specific group i.e. West, South, Midwest, North East.

I think the way I'll have to do it is with an array, but I've never used an array before so I really don't know where to start.

I tried a formula field using

If {customer.state} = "CA" or {customer.state} = "OR" and so on
Then "West"
Else If....and so one and on but I get an error that the result must be a boolean.

Any ideas? I can't seem to find any info on others doing this which I found strange.

Thanks.

 

[vidru]

Create formula called RegionGroup like this, then group on it:

//@RegionGroup
If {customers.state} in ["CA","OR","WA","NV"] then
"WEST"
Else If {customers.state} in ["ME","VT","NH","MA","NY","RI","CT","NJ","PA"] then
"NORTHEAST"
Else If ....

-dave

 

[synapsevampire]

The reason why you won't find too much activity here along these lines is tht the database should already have this in it.

Another table would exist with:

RegionID
Region
State

Join that to your data and you'd get the region for each.

An alternative to Dave's solution is the Switch function:

Switch (
{table.state} in ["CA","NV","OR"], "West",
{table.state} in ["NY","CT","MA"], "East",
true,"N/A"
)

-k