Converting state address field into two letter abbreviation

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.

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"

)

Thx for sharing!

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")

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.

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.

Agreed

Thanks guys, very helpful. I ended up converting the state/region to upper and used Contains within an IF. Maybe that's possible with Case but I couldn't seem to get it to work.

If (
Contains(Upper([Ship To: State/Region]),"ALABAMA"), "AL",
Contains(Upper([Ship To: State/Region]),"ALASKA"), "AK",
Contains(Upper([Ship To: State/Region]),"ARIZONA"), "AZ",
Contains(Upper([Ship To: State/Region]),"ARKANSAS"), "AR",.........etc.

Just in case someone wants the reverse, here it is.

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

Is there a way to take the formula field & automatically change [Address:  State/Region]?  

The formulas above changed the [Address:  State/Region] to the format I needed, but I have over 10k records where the [Address:  State/Region] fields needs to be changed to the formula field.