i would like the current year to appear at the top of the list rather than at the bottom.
Do you also have data from future years - or is the current year always the highest year?
no, there are no records for future years so the highest year would always be the current one.
And what's your earliest year? (sorry, I should have asked that too from the beginning).
currently the oldest year is 1999. ideally the solution would accommodate the import of new records from a previous period (perhaps even with some gaps where there would be no records for a year somewhere in the middle of the list) and, it's probably asking to much, it would even work with a new clone file where the user starts from scratch inputting their own records. so, ideally, it would be just like the value list which shows only the existing unique values in the year field, but sorted in descending order. does that make sense? is this a crazy request? thanks for your willingness to help with this.
As a rule, a value list based on a field is ALWAYS sorted in ascending order. There is a workaround, but it only works for approximately 100 values - i.e. for about a century worth of data in your case. So let's say you picked 1980 as the earliest year:
1. Define a calculation field (result is Text) =
n = 2080 - YourYearField
Substitute ( 10^n - 1 ; "9" ; " " )
Note that the replaceString in the Substitute() function is a space.
2. Change your value list to also display values from the calculation field, and to sort values using second field.
Now you should be set for the next 70 years...
thanks comment. that is awesome. that is the kind of trick that i would NEVER come up with on my own! not that i am still going to be around in 70 years, but is there a reason you don't use an even higher number than 2080? why not 3000? i notice that it makes the pop up menu is much wider than normal to accomodate all those spaces (is that why you limit it to 2080, or will that window max out at some predetermined width?). i might try to tinker with the calculation to reduce the number of spaces necessary to make this work to a minimum. thanks again!
why not 3000?
Because Filemaker has a limit on the number of characters it will index. An entry with 120 spaces will be indexed the same as one with 150 spaces - and if you use the spaces as the sort field, only one of these will show up in the value list.
thanks comment. that makes sense. you have a truly encyclopedic knowledge of filemaker! i am at once impressed, but at the same time disheartened by the sheer volume of things yet for me to learn.