Populating a list of States

[Ron Cates]

Hi All,

I am working on a filtered portal with a global field to select a state. I would like to include an option for "All" so I have a field that the relationship is based on with a calculation that looks like this so far

xstate = Case ( xstate_select = "All" ; ? ;xstate_select )

So I need some help on how to populate the field with the list of states, either from the data or from my states value list.

Actaully, now that I think of it. I would also like to create a value list of only the states I have records for. Any help on this would be appreciated as well.

Thanks in advance

Ron

Edited July 23, 2009 by Guest

[LaurenKuhlman]

Sounds fun. Question, your filtered portal/global field. Is that a portal where you click/toggle states to add them to a hidden global field?

[Ron Cates]

That's correct. I have a field on the layout above the portal called xstate_select for choosing either a single state or "All". When the state is selected it populates the hidden global xstate field which the relationship is based on. If "all" is selected it populates the xstate field with all the states and therefore shows all records.

I just thought of something else.

When creating a value list based on a field e.g. a list of states based on the state field. Is it possible to somehow ad in an option for "All"?

I have several value lists where this would be a huge plus but I have not seen any indication that this is possible.

[LaurenKuhlman]

Picklists are such fun.

There are 2 ways to add "All" to a value list based on a table that spring to mind. One is to create an actual record with the value "All" and ensure it doesn't appear by tricks with finds, adding filters to your portals, etc. I don't like this one very much.

The other is to create a dynamic value list (or it could be custom values) based on a field with all your states. Then, create a calculated field that looks like this:

"-All-"  & ¶ & 

ValueListItems ( Get ( FileName ) ; "stateNameAll" )

The "stateNameAll" is the name of the value list which now becomes hard-coded and if changed will break this calculation. Note that I added hyphens before and after the word all to ensure that Alaska and Alabama don't get sorted before it which can be disorienting.

Then, make a dynamic value list based on this field. This is all done in the attached sample file. This second value list is called stateNameAllPlusAll.

I would also like to create a value list of only the states I have records for.

This should be easy enough by basing your dynamic value list on Orders::State instead of using custom values.

I was not sure whether you were using a dropdown or a filtered portal picklist so I made examples for both in the attached file.

I copied some code from one of the many toggle picklists I have built into the attached sample file. Enjoy!

A note about the sample file: it uses conditional formatting so FM9 or above is best. It could be done with TextFormat functions if you are using below 9.

If you have any questions, feel free to ask away. I have subscribed to this post.

StatesPickList.fp7.zip

[Ron Cates]

Lauren, you are awsome! Everything is in place and working perfectly.

Thanks So Much

Ron