Filemaker Pro 10 display order

Is it possible to control the way a list of data from another table is displayed in a layout using the drop-down arrow.
Many records were added with a numeric AgencyCode. A change in status results in the AgencyCode becoming a 4 letter code. When using the drop-down arrow all the numbers come first. Is there someway to sort the list so that the alpha codes come first?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
Dropdown Lists are sorted alpha-numerically, which means that numbers are sorted to the top. There is no easy way to get the numbers to sort below alpha characters that I'm aware of. However, if codes that contain numbers only will never be selected as an agency, you could simply filter them out of the list.

You said that the agency values were in a related table, so do the following:


If you don't already have one add a calculation field called "Constant" to the table that had the field containing the drop down list. Turn off "Do not evaluate if all references are empty."


In the Agency table called Flag_AlphaCode, add the following calculation:  Case( not IsEmpty( Filter ( Code ; "0123456789" ) ) ; 1 ). this looks at the Code to see if the value contains anything other than numbers in it (meaning it has an alpha code, not numeric). If it does it gives it a value of "1".


Add an additional predicate to the relationship between the main table and the agency list/code table that you're basing the drop-down list on, from Constant to Flag_AlphaCode. This sets the relationship used for the drop down list to display ONLY alpha based codes and not numeric ones.


You're drop down list should now show only the alpha values.

There are some other variations on this technique, this is just the most explicit on and makes some assumptions about how you have things setup already. You may need or want to create a separate relationship for the value list but if the relationship to the Agency Codes table is only for creating the value list then the above change should work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maryj152Author Commented:
Thanks for the answer. We need both numeric and alpha. It is just that there are about twice as many numeric and they are used the least. New people are starting to use the database and I was hoping to make it as easy as possible for them. I think I will make it as easy as possible for me and just leave it as is for now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.