Discussions

 View Only
Expand all | Collapse all

Converting state address field into two letter abbreviation

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training03-07-2015 00:47

Matthew Matthew

Matthew Matthew09-27-2017 16:35

  • 1.  Converting state address field into two letter abbreviation

    Posted 08-08-2014 19:11

    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?



  • 2.  RE: Converting state address field into two letter abbreviation

    Posted 08-08-2014 20:13
    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!


  • 3.  RE: Converting state address field into two letter abbreviation

    Posted 08-08-2014 20:49
    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.


  • 4.  RE: Converting state address field into two letter abbreviation

    Posted 03-07-2015 00:43
    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"

    )


  • 5.  RE: Converting state address field into two letter abbreviation

    Posted 01-19-2018 18:28
    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".


  • 6.  RE: Converting state address field into two letter abbreviation

    Posted 01-19-2018 19:27
    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]) 


  • 7.  RE: Converting state address field into two letter abbreviation

    Posted 01-19-2018 23:55
    I wish my name was Ringo!  Your suggestion worked like charm - Thanks!  


  • 8.  RE: Converting state address field into two letter abbreviation

    Posted 03-07-2015 00:47
    Thx for sharing!


  • 9.  RE: Converting state address field into two letter abbreviation

    Posted 04-23-2015 17:49
    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")


  • 10.  RE: Converting state address field into two letter abbreviation

    Posted 05-03-2016 17:44
    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?


  • 11.  RE: Converting state address field into two letter abbreviation

    Posted 05-03-2016 17:59
    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.


  • 12.  RE: Converting state address field into two letter abbreviation

    Posted 11-03-2016 17:04
    Scott
    Just FYI
    I just had occasion to use this and it was most useful.


  • 13.  RE: Converting state address field into two letter abbreviation

    Posted 11-03-2016 18:06
    Glad you like it!  Very useful considering the QuickBase default US state selections are all full names of the states.


  • 14.  RE: Converting state address field into two letter abbreviation

    Posted 11-03-2016 18:25
    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.


  • 15.  RE: Converting state address field into two letter abbreviation

    Posted 09-27-2017 16:35
    Agreed


  • 16.  RE: Converting state address field into two letter abbreviation

    Posted 11-29-2017 02:24
    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.


  • 17.  RE: Converting state address field into two letter abbreviation

    Posted 07-16-2018 21:10
    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")


  • 18.  RE: Converting state address field into two letter abbreviation

    Posted 03-06-2019 14:50
    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.