Sort a dynamic Value List in a hidden order

[Ward]

I created a Value List based on the values in a field (say "color"). FM8 sorts this list in alphabetical order (blue, green, orange, red, violet, yellow).

Since I want the Value List in rainbow order, I added an "order" field and manually assigned numbers to each color. Then I changed the Value List to use both fields, order + color, and specified "Sort by first field."

That works fine, except I don't want to see the numbers in the menu that uses the Value List (1 red, 2 orange, 3 yellow, ...).

So I checked the "Show values only from second field" option. That seems like it would do exactly what I want. HOWEVER, checking that option (1) changes the sort to "Sort by second field" and (2) disables the two sorting radio buttons.

I'm now back where I started, with the colors sorted in alphabetical order. Sigh. So close and yet so far.

-- Ward

P.S. I used colors for the sake of simplicity. My actual database field is domain-specific and not relevant to this discussion.

[IdealData]

How about...

1. Create a static value list which has most of the known "colours" in the correct order.

2. Create a relationship to support your dynamic list and sort the relationship based on the static value list

3. Create your dynamic list using the relationship from 2.

Not perfect, but close.

You could extend the idea to have your value list as separate records in another table and have them sorted in the appropriate record order - then you can maintain the value list by adding new records.

[mav]

I'd like to sort (ascending or descending) a value list of dates, coming from an existing date field, and the sort method needs to be automatic, as the dates are being added, well, every day . . .