This can be one of the more aggravating limitations of FileMaker. Ascending order is the only order FileMaker permits.
Here are two options.
1) Change the data in your dressSizeDesc field so that alphabetical ascending order works:
a. Extra Small
adding the a., b., c. directly to your desciption field cause alpha sorting to work. You can also use a separate field and define a calculation field that combines them for use in the value list if you need the original descriptions for other uses and don't want the extra letters to appear when used elsewhere.
2) "break the rules" and use the dressSizeDesc field as the key instead of the ID number. This makes for a single field value list and you can then use a second field in your value list as an invisible sorting key:
Define a calculation field set to return text like this:
Substitute ( 10^dressSizeOrder - 1 ; 9 ; " " )
This calculation produces a single space character when dressSizeOrder = 1, two spaces when it is two and so on. Set your value list up to use dressSizeDesc as field 1 and this new calculation field as field two. Set it to sort values by the second field.
If you need to use the ID number (So you no longer break the "Good db design rules", you can use a script to use the selected description to look up the ID number and enter it into a field in your parent table.
Thanks for the quick response!
Yes, that was what I did as a work around but it doesn't look very pretty with a running number or alphabets as a prefix. I hope that filemaker can work out a better solution for this in the near future.
It's an oft requested feature over many years but no indications are out there that this will change anytime soon. You can add your voice to those who have already requested it: http://www.filemaker.com/company/contact/feature_request.html
Don't forget the other option for avoiding this that I described.
This would appear to be a table of values with a fairly number of records. If so, it may be more practical to eliminate the table altogether and just use a custom values value list. Custom values list in the order that they were entered in Manage | Value Lists so you can display values from a custom value list in any order that you want.
Thanks for the response and suggestions once again!
Yeah, its surprising that they didn't add this feature through the years as it doesn't sound like something really hard to implement. I'm suspecting that the reason why it hasn't been done is because access can do it and there are some legal implications to have that feature as well? I hope not.
I will explore both options but I'm leaning towards option 1 over custom value list because there is a lot more information that I want to keep track other than what was stated above for each record.
__kp_hairColour (number) - primary key
hairColourDescription (text) - value to be displayed in pop-up menu etc
hairColourGuideline (text) - guidelines on how to differeniate this color from others. e.g. difference between auburn, chestnut and blonde
hairColourOrder (number) - the order in which I would like to display the list in
hairColourSample (container) - a sample picture of how each different hair color looks like
By storing the info this way, not only do I have a list of all the colours available for hair, I can store more details related to it for each record. Also, I can add new options if any in the future. This is just an examples, I have other tables which have more records that share the same issue.
Thanks for helping me out along the way. I'm very new to the product so I still need more time to figure things out. In fact, I just purchased the full version of this software yesterday evening and only have a few days of experience with the trial copy.
I'd be extremely suprised that there is any such legal barrier. Access uses an SQL expression to generate the values that appear in its version of a value list that is quite generic so there's nothing there sufficiently unique that it could be treated as intellectual property.
Table based value lists in FileMaker access the index of a field to generate the values listed. The values in the index are already ordered in ascending order (and do not contain duplicates) and so the value lists generated from them are automatically in this order. This explains the how, but not the why--why FileMaker hasn't invested the resources to provide more options for value lists. Displaying more than two columns of values, not only sorting in a different order, but using a field not visible in the value list for determining the sort order are all much needed improvements in this part of FileMaker's feature set.