Hi Benetta
Yes, this is possible, although a little convoluted to implement. It also has pros and cons, which I’ll talk about later.
First, you determine your master field (based on your grid, I assume this is “State”). You create a dropdown for this field. Click on the “Options” button and select “Show Values”. This gives you something that looks like: Choice 1 Choice 1
Choice 2 Choice 2
Change the first box in each set to whatever you want to display for the user (e.g., AL, AK, etc.). In the second box in each set, use consecutive numbers (you’ll end up with 1-50 for the states): AL 1
AK 2
Now for the fun part…. The fields you want to control with the change of state (WHPOA, EFT, UNPOA) are all created as custom fields with calculations. In these, use a “CASE” function. The first argument of the CASE function is the master field that controls the choices (State); the subsequent arguments correspond with the consecutive numbers you assigned. So, if AL=1 and AK=2, your formula for the WHPOA custom field would be: CASE(State, “Yes”, “NA”)
Since the formula doesn’t give you an intuitive way to know what number corresponds with what value, it’s probably best to have a spreadsheet where you keep track of this. (There are also ways you can build the formula based on the spreadsheet if you’re savvy with Excel.)
Once this is all set up, just by changing “State” on your custom form, your WHPOA, EFT, and UNPOA fields will all populate based on the State selection (though you need to save the form and reopen it to see the new values…they don’t switch over on the fly in the view).
Now, for the pros and cons:
Pro: 1. Obviously the best part is maintaining data integrity.
Cons:
1. You REALLY need to understand how this works and how future updates will impact what you already have applied in your projects. Say you’ve already added all 50 states in alphabetic order, and you now want to add PR (Puerto Rico). You need to add this as #51 and then use the option to re-sort the dropdown alphabetically. The values for PR will go at the end of each of your CASE formulas (because it was assigned the last number in the chain (51). However, when you view your dropdown, #51 will no longer be at the bottom, so you need to ensure that a subsequent addition gets #52 (this is where using a spreadsheet to manage the whole thing and assemble your formulas could be helpful).
2. The way this is set up doesn’t provide for a reference table in the background. The translation of “1” to “AL” occurs because of WF “magic”. Thus, you will occasionally come across a more advanced reporting situation where you end up with a # value rather than your desired state abbreviation, and I have not come up with a good way around this. This usually only happens with a more advanced calculation (it’s been a while, but as I recall, I had issues with custom formatting where I wanted to use a “contains” option and was unable to use the text values). You will also see the numeric values if you are using the API to get at the data directly.
Hopefully I didn’t leave anything pertinent out of this description (it’s been a while since I worked through this initially). My suggestion would be to start small (with 5 states or so) with test fields and run through every scenario you can think of after using the field(s) in some test projects (changing your state names, changing your CASE values, etc.) so you can see exactly how it behaves and get a feel for whether it works for you.
If you can live with the cons, this really is a great solution. We’ve been using it for certain data elements for a while now, and we are really happy with the data integrity as a result.
Good luck! Kathy
