States/Provinces VL Set-up Question

August 2, 201213 yr

Hi, all:

I'm probably making this far more complicated than it needs to be but hey, that's my trademark.

I have a text field in my solution, States_Provinces where it looks to an external VL table for their full names and two-letter identifiers. To make the user experience easier/better, what I want to happen is this:

1) Tab or click into the States_Provinces field and be presented with the states/province's full names via a drop-down menu.

2) They can either: (a) Type the first few letters of a state's name to bring up the full name, (b ) Click the state's name from the list, or (c ) enter the two-letter identifier.

3) A calculation or custom function will then automatically populate the field with the two-letter identifier.

The problem is, I don't know how to go about setting this up, especially if the user decides to add a new entry to the value list for a not-yet-listed state/province. Since the user won't permission to edit the custom function, it has to be smart enough to figure out how to grab the two-letter identifier from the Value List table.

I tried setting up a custom function that looks like this:

Case (

state = "Alaska" or state = "AK"; "AK";

state = "Alabama" or state = "AL"; "AL";

state = "Arkansas" or state = "AR"; "AR";

...

state = "Saskatchewan" or state = "SK"; "SK";

state = "Yukon Territories" or state = "YT"; "YT";

) &

Case (state ≠ "AK" or "AL" or "AR" or "AS" or "AZ" or "CA" or "CO" or "CT" or "DC" or "DE" or "FL" or "GA" or "GU" or "HI" or "IA" or "ID" or "IL" or "IN" or "KS" or "KY" or "LA" or "MA" or "MD" or "ME" or "MI" or "MN" or "MO" or "MS" or "MT" or "NC" or "ND" or "NE" or "NH" or "NJ" or "NM" or "NV" or "NY" or "OH" or "OK" or "OR" or "PA" or "PR" or "RI" or "SC" or "SD" or "TN" or "TX" or "UT" or "VT" or "VA" or "WA" or "WI" or "WV" or "WY" or "AB" or "BC" or "MB" or "NB" or "NL" or "NS" or "NT" or "NU" or "ON" or "PE" or "QC" or "SK" or "YT" ; state

)

...but it's not behaving the way I envisioned. Any guidance would be greatly appreciated!

Thanks,

Rich