Converting state address field into two letter abbreviation

Since I spent a not-insignificant amount of time on this, I'd like to prevent others from having to doing the same (though it was a fun exercising in trying to remember my state abbreviations!):

If (

[Address: State/Region]="Alabama", "AL",

[Address: State/Region]="Alaska", "AK",

[Address: State/Region]="Arizona", "AZ",

[Address: State/Region]="Arkansas", "AR",

[Address: State/Region]="California", "CA",

[Address: State/Region]="Colorado", "CO",

[Address: State/Region]="Connecticut", "CT",

[Address: State/Region]="Delaware", "DE",

[Address: State/Region]="Florida", "FL",

[Address: State/Region]="Georgia", "GA",

[Address: State/Region]="Hawaii", "HI",

[Address: State/Region]="Idaho", "ID",

[Address: State/Region]="Illinois", "IL",

[Address: State/Region]="Indiana", "IN",

[Address: State/Region]="Iowa", "IA",

[Address: State/Region]="Kansas", "KS",

[Address: State/Region]="Kentucky", "KY",

[Address: State/Region]="Louisiana", "LA",

[Address: State/Region]="Maine", "ME",

[Address: State/Region]="Maryland", "MD",

[Address: State/Region]="Massachusetts", "MA",

[Address: State/Region]="Michigan", "MI",

[Address: State/Region]="Minnesota", "MN",

[Address: State/Region]="Mississippi", "MS",

[Address: State/Region]="Missouri", "MO",

[Address: State/Region]="Montana", "MT",

[Address: State/Region]="Nebraska", "NE",

[Address: State/Region]="Nevada", "NV",

[Address: State/Region]="New Hampshire", "NH",

[Address: State/Region]="New Jersey", "NJ",

[Address: State/Region]="New Mexico", "NM",

[Address: State/Region]="New York", "NY",

[Address: State/Region]="North Carolina", "NC",

[Address: State/Region]="North Dakota", "ND",

[Address: State/Region]="Ohio", "OH",

[Address: State/Region]="Oklahoma", "OK",

[Address: State/Region]="Oregon", "OR",

[Address: State/Region]="Pennsylvania", "PA",

[Address: State/Region]="Rhode Island", "RI",

[Address: State/Region]="South Carolina", "SC",

[Address: State/Region]="South Dakota", "SD",

[Address: State/Region]="Tennessee", "TN",

[Address: State/Region]="Texas", "TX",

[Address: State/Region]="Utah", "UT",

[Address: State/Region]="Vermont", "VT",

[Address: State/Region]="Virginia", "VA",

[Address: State/Region]="Washington", "WA",

[Address: State/Region]="West Virginia", "WV",

[Address: State/Region]="Wisconsin", "WI",

[Address: State/Region]="Wyoming", "WY"

)

Using the Case function is more efficient:
Case([<reference field name>],
"Alabama", "AL",
"Alaska", "AK",
"Arizona", "AZ",
"Arkansas", "AR",
"California", "CA",
"Colorado", "CO",
"Connecticut", "CT",
"Delaware", "DE",
"Florida", "FL",
"Georgia", "GA",
"Hawaii", "HI",
"Idaho", "ID",
"Illinois", "IL",
"Indiana", "IN",
"Iowa", "IA",
"Kansas", "KS",
"Kentucky", "KY",
"Louisiana", "LA",
"Maine", "ME",
"Maryland", "MD",
"Massachusetts", "MA",
"Michigan", "MI",
"Minnesota", "MN",
"Mississippi", "MS",
"Missouri", "MO",
"Montana", "MT",
"Nebraska", "NE",
"Nevada", "NV",
"New Hampshire", "NH",
"New Jersey", "NJ",
"New Mexico", "NM",
"New York", "NY",
"North Carolina", "NC",
"North Dakota", "ND",
"Ohio", "OH",
"Oklahoma", "OK",
"Oregon", "OR",
"Pennsylvania", "PA",
"Rhode Island", "RI",
"South Carolina", "SC",
"South Dakota", "SD",
"Tennessee", "TN",
"Texas", "TX",
"Utah", "UT",
"Vermont", "VT",
"Virginia", "VA",
"Washington", "WA",
"West Virginia", "WV",
"Wisconsin", "WI",
"Wyoming", "WY")

yes, my client complained about that very thing.  There should be an option to configure the address field to abbreviate US address States, I suppose.

Possible ways:


1. If you use a standard field "Address", just right click on the field and choose: "Edit the field properties for this field". You will see "Address field options", where you can find "States" . So , just change the full name of these states to the abbreviation, like New York - NY, California - CA.

2.Second possible way- write a formula. Add a new Text-formula field . Call it "Abb"

If ( [State] = "New York", "NY"; ....) And write states what you need. And in the future, just use this field in your reports.

Have a good one!

Thanks. I am using the standard Address field. The problem with Solution #1 is that when you search for an address, and select one from the auto-suggestions (a very handy feature), the state field is left blank. Quickbase seems to require that the state options be the complete, spelled-out version of the state names in order for this to work. I guess if I like that feature enough I'm forced to do option 2.

Thx for sharing!

Where do I input this function? If I already have the list of states in the standard address field, how will changing this affect existing records?

Adding a calculated formula-text field with the above formula will translate all selections in that address state field in any new or existing record.  When you report on the record, you can then utilize the new formula text field in the report instead of using the field that spells out the state.

Let's assume that your current field that spells out the state names is called "State".  Add a formula-text field called "State Abbreviation".  In the field properties of that new field, copy and paste for formula above, like this:  

Case([State],
"Alabama", "AL",
"Alaska", "AK",
"Arizona", "AZ",
"Arkansas", "AR",.....

This will give you a new field, and since it is a formula field, it will work for both new and existing records (assuming that a state is selected).  When you need to report on an address, simply pull the new "State Abbreviation" field in your report.

Scott
Just FYI
I just had occasion to use this and it was most useful.

Glad you like it!  Very useful considering the QuickBase default US state selections are all full names of the states.