Jump to content
Sign in to follow this  
WF7A

States/Provinces VL Set-up Question

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Why don't you simply define a value list using values from the ID field, but showing only the full name field?

Share this post


Link to post
Share on other sites

I'm sorry, I don't follow. Are you saying that I should relate the data table's ID field with the Value List's ID?

I've attached a quick-'n'-dirty file if you don't mind showing me what you have in mind.

Thanks!

State Example.fmp12.zip

Share this post


Link to post
Share on other sites

Thanks! Much simpler.

...and no, I didn't come up with (the Mars probe) Curiosity's crazy landing scheme.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.