1 2 Previous Next 21 Replies Latest reply on May 11, 2009 11:15 AM by LaRetta_1

    Sorting a value list

    mmccarty

      Title

      Sorting a value list

      Post

      Hello,

        I'm not sure if this is possible but thought that I would ask. I have a value list that show all the possible status types for a product.  The table this pulls from has a status ID from 1-15.  I want the value list to show all the status type in order of their ID's but I don't want the IDs included in the value list.  I can make it display in order like this:

      1 Not Started

      2 Data collected

      3 Briefing

      4 Editor review

      5 Complete

       

      But I just want:

      Not Started

      Data Collected

      Briefing

      Editor review

      Complete

       

      If I don't include the IDs in the value list then the status types are ordered alphabetically.  Anyone have an idea?

      Thanks

        • 1. Re: Sorting a value list
          philmodjunk
            

          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.

          • 2. Re: Sorting a value list
            raybaudi
              

            mmccarty wrote:

             

            Anyone have an idea?


            What about something like this ?

             


            • 3. Re: Sorting a value list
              mmccarty
                 I don't understand what you did.
              • 4. Re: Sorting a value list
                raybaudi
                  

                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 )

                • 5. Re: Sorting a value list
                  mmccarty
                     Sorry, I'm still confused.  Are you trying to tell me how to make the ID numbers look like boxes?
                  • 6. Re: Sorting a value list
                    raybaudi
                       Yes, Char(9900) till Char(9999) can't be displayed so FM trasform them into boxes, but leaving them in their order.
                    • 7. Re: Sorting a value list
                      mmccarty
                         Cool Trick, thanks!!
                      • 8. Re: Sorting a value list
                        raybaudi
                          

                        Hi LaRetta

                         

                        "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 )

                        • 9. Re: Sorting a value list
                          LaRetta_1
                            

                           "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.

                          • 10. Re: Sorting a value list
                            raybaudi
                              

                            "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.

                            • 11. Re: Sorting a value list
                              comment_1
                                 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.
                              • 12. Re: Sorting a value list
                                raybaudi
                                  

                                Hi comment

                                 

                                "So when you select the "enhanced" ID from the drop-down, there will be a direct match on the other side"

                                 

                                Yes.

                                 

                                "...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 )

                                • 13. Re: Sorting a value list
                                  LaRetta_1
                                    

                                  "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.

                                   

                                   

                                  • 14. Re: Sorting a value list
                                    LaRetta_1
                                      

                                    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. 

                                    1 2 Previous Next