Value List Sorting - I need a creative solution
Ok, this is the situation:
I've got a table of Projects with the project ID and a project name. I've got another table with orders and on the layout I have a drop down list field containing the project ID to link it to that project.
This field uses a value list - ProjectNames - that is made by:
First Field: ProjectID
Second Field: ProjectName
This is great, except that we have hundreds of projects, and I want to sort the field DESCENDING by ProjectID, so that the most recent projects show up first (The most recent project would be 995525 and oldest would be 993128)
So I'm needing either:
- A clean solution that will just let me sort the value list by descending, yet still retain the ProjectID as the value transfered to the field
- Those great Filemaker coders to update that feature to include some solution for this problem.
I was thinking maybe a solution would be to make the first value a text calculation of ProjectID + ProjectName and the second would be a calculation like 1000000 - ProjectID, and if I add in 0s it should sort correctly. But if I did that then the field would not receive the correct ProjectID to link it. Maybe I could cut the number out of the list using something like Left ( CalculationField ; 6 ), and having that fill in the Quote's project ID using a script trigger for on modify... But that seems really sloppy and maybe tricky to implement. Thoughts?