Converting state address field into two letter abbreviation

  • 1
  • 1
  • Question
  • Updated 2 months ago
  • Answered

The state address field displays the full name of the state. I would like to get the two letter abbreviation (i.e. "MI" instead of "Michigan"). Besides creating my own lookup table, is there another way?

Photo of Jonathan Heuer

Jonathan Heuer

  • 390 Points 250 badge 2x thumb

Posted 5 years ago

  • 1
  • 1
Photo of John

John

  • 0 Points
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!
Photo of Jonathan Heuer

Jonathan Heuer

  • 390 Points 250 badge 2x thumb
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.
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
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"

)
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb
What would need to be added to this formula to allow any value not stated above to be passed through? i.e. if the state field value equals "Whatever", that it passes through "Whatever".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,268 Points 50k badge 2x thumb
Ringo Is that you real name - love it),

It's easy

just change the last part to this'


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

[Address: State/Region]) 
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb
I wish my name was Ringo!  Your suggestion worked like charm - Thanks!  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,268 Points 50k badge 2x thumb
Thx for sharing!
Photo of Scott

Scott

  • 40 Points
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")
Photo of Jeff

Jeff

  • 186 Points 100 badge 2x thumb
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?
Photo of Scott

Scott

  • 40 Points
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,268 Points 50k badge 2x thumb
Scott
Just FYI
I just had occasion to use this and it was most useful.
Photo of Scott

Scott

  • 40 Points
Glad you like it!  Very useful considering the QuickBase default US state selections are all full names of the states.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,268 Points 50k badge 2x thumb
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.
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
Agreed
Photo of John Brindamour

John Brindamour

  • 80 Points 75 badge 2x thumb
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.
Photo of Lee Gilmore

Lee Gilmore

  • 790 Points 500 badge 2x thumb
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")
Photo of Amber Polston

Amber Polston

  • 582 Points 500 badge 2x thumb
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.