4 Replies Latest reply on Apr 18, 2016 11:21 AM by SteveMartino

    Get a State Abbreviation from the State Name


      Hello Forum.

      As a mental exercise and to prevent errors, I was wondering if there was an 'easier' way to get a state abbreviation from a state name (American database only).

      I have a text field State, with all 50 states, and a text field State_abr.  When a user is creating a new record, they can only select the state from the drop down list.  However, I would then like to auto enter the abbreviation for the state.  I obviously can't do this by parsing as the abbreviation naming convention isn't the same based on the state name, i.e. first 2 letters, or first and last letter.

      The only way I can think of is to hard code a calculation using Substitute (which of course I could've already accomplished in the time it takes me to type out this question and wait for responses).

      I was thinking if it were Excel, I would have a list of States with the abbreviations in the next row and use the lookup function (but that is also hard coded).

      I also thought, but did not try, a separate table of abbreviations, or a separate table occurrence, with a conditional value list.  I think this would get me the proper abbreviation, then have it auto entered.

      I'm more concerned with best method because I feel it could be used in a different, but similar type of situation, should a need ever arise.

      Any thoughts/help or guidance is always appreciated.



        • 1. Re: Get a State Abbreviation from the State Name

          So, if they entered: California, you want the other field to auto enter CA, is that correct?


          If so, I would implement a lookup into a separate State table (with state and abbreviation) to populate the other field with "CA".


          Not sure if this is what you mean.


          - m

          1 of 1 people found this helpful
          • 2. Re: Get a State Abbreviation from the State Name

            I would use a table. But you could also use a separate value list of abbreviations and get the corresponding abbreviation

            like this, i.e. by position, which is similar to your Excel scenario:

            Let ( [

            theName = YourTable::stateName ; // user entry from ListOfStateNames VL

            nameList = ValueListItems ( "" ; "ListOfStateNames" ) ;

            abbList = ValueListItems ( "" ; "ListOfStateAbbs" ) ;

              thePos = ValueCount ( Left ( nameList ; Position ( nameList & ¶ ; theName & ¶ ; 1 ; 1 ) ) )

            ] ;

            GetValue ( abbList ; thePos )


            1 of 1 people found this helpful
            • 3. Re: Get a State Abbreviation from the State Name

              +1 I use a lookup table for this. I often have Canadian Provinces as well as states, so the "country" & country abbreviation may be a part of this table.


              1 of 1 people found this helpful
              • 4. Re: Get a State Abbreviation from the State Name

                Thanks for all your replies.  I do like Oliver's idea of doing it via a value list.  The only problem is a value list for States, and a Value list for abbreviations don't match up when sorted-unless I'm missing something simple (usually the case).

                I'm going to give the separate table approach a go.




                Edit:  I went with separate table/Lookup.  I'm giving the check mark to Morkus because he/she was first to respond.  Thanks again for all the help