This is one of the more frustrating limitations of FMP that I don't understand. For some reason, as you've probably discovered, when you hide the first column of a value list, you can no longer sort on it which leaves the list sorted in alphabetical order on the second column.
Other DB's such as MS Access don't have this limitation.
Filemaker Inc. has a feature request form where you can request a new feature (Look on the Company | Contacts page) that I've used to request this new feature. If you would like to add your two cents by requesting this also, feel free.
I've had to resort to manually sorting my values in the custom value window in order to get the values in the order I want. That's not ideal--I have to train the users to manually edit the list whenever they change the table values.
If you are using Filemaker 10 and choose to use custom values, you could create a script that uses the text selected from the drop down to look up the ID code from your value list table and set it as a script trigger.
I don't understand what you did.
I created a new calculated field with calc:
Char ( 9900 + ID ) & " " [ 3 spaces ]
and used it for the relationship and as the first field of the value list ( second being Status )
Sorry, I'm still confused. Are you trying to tell me how to make the ID numbers look like boxes?
Yes, Char(9900) till Char(9999) can't be displayed so FM trasform them into boxes, but leaving them in their order.
Cool Trick, thanks!!
"What plants into the ID after selection? It should be straight ID, no?"
the field status will contain related::status with an auto-enter calculation.
( But it may contain what anyone wish, even the related::ID )
"the field status will contain related::status with an auto-enter calculation."
So you are using a value list on a field and then expecting auto-enter calculation to change it? That wasn't mentioned.
Oh, so now you have another field holding an auto-enter ID? The purpose of a value list is to select the value list ID and THAT is what inserts into the status field. You should be able to then change the names of your status or even place the related status field directly on the layout.
I suggest that, if your suggestion requires additional manipulation, such as auto-enter of the Status ID based upon a relationship to the Status Name, then you be clear on your suggestion. As it stands, it does not work.
"Oh, so now you have another field holding an auto-enter ID?"
No, there is only one field: status
"As it stands, it does not work"
The picture shows that it work.
BTW: you are right that I forgot to mention that the status field contains an auto-enter calculation.
If I understand correctly, there is a calculated cID field in the Values table, and the relationship matches that instead of the "real" ID. So when you select the "enhanced" ID from the drop-down, there will be a direct match on the other side, with no auto-enter required.
"So when you select the "enhanced" ID from the drop-down, there will be a direct match on the other side"
"...with no auto-enter required"
It depends from what we want... if we need the status field value, than the auto-enter is required; same for the related ID... otherwise we left off with the "enanched" ID ( being the first field of the value list )
"there will be a direct match on the other side,"
If you related the new status field, use a global as the test if necessary (which has the selected 'calculationID') to the value list table based upon this calculation (also created in the value list table), then place the related valueList::status description directly on your layout, you have an empty description; at least on Windows, it does not work.
And if you base the value list on all values using that new calculation and second field of description, the value list will only show ONE description. The value list which is popped for selection must be based upon the ID and not the calculated ID.
Daniele, if we 'need the value list value' then we would have no reason to use IDs to begin with. The value list ID is used because THAT is what should be planted in the field. If you want more relationships, extra fields and auto-enters, then fine. But that is NOT what you are suggesting and this thread is getting greatly confused.
How about either of you post a demo file on FM Forums and like to it. Because, as it stands, it does not work as suggested.