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.