14 Replies Latest reply on Feb 27, 2014 6:33 AM by tomswell

    Sort A CUSTOM Value List



      Sort A CUSTOM Value List


      Is there a way to sort a CUSTOM value list so that when the drop down menu is clicked the values appear in alphabetical order rather than the order they were input in?

        • 1. Re: Sort A CUSTOM Value List

          Yes, by hand.

          This assumes you have the necessary privileges to create/edit Value Lists.

          Go to Manage > Value Lists...

          Double click the list you want to change.

          Rearrange the items to be in the order that you want them to be. Cut and Paste works or if it is a long list copy the whole list, paste it into a program that sorts, alphabetize it, copy it and paste it back into FileMaker.

          When through click OK and click OK and your value list should be sorted.

          • 2. Re: Sort A CUSTOM Value List
            But it's an edit list. Users can add values as they go along thus messing up any sort done your way.
            • 3. Re: Sort A CUSTOM Value List

              There is another way...

              Create a new value list NOT custom, but coming from values of the field that you used to insert the custom values.

              • 4. Re: Sort A CUSTOM Value List
                I thought of that but then users can't edit the list and make additions to it. That option is dimmed when the value list is based on a field.
                • 5. Re: Sort A CUSTOM Value List

                  Sorry it never crossed my mind. I never let users "edit" a custom value list, your experience is the norm. It will get messed up.

                  Ray is correct, you can use the values from the field itself to make your value list from a field. and they will always drop down in alphabetical order.


                  Create a new table for just your value list, just a couple of fields, and ID and text for the values; and relate it to your original table then create a value list from fields. This will allow users to create new values the new table and they will always drop down in alphabetical order. But you can at least put some constraint on who gets to create the new records. 

                  • 6. Re: Sort A CUSTOM Value List

                    If you do it via the new table, then all you have to set up a UI to allow the user to create a new record in the value list table and then it will be available for entering in to the main records field. PopUp windows are quite handy for situations like this. The user clicks a NEW button, a small window pops up the user adds the new data, clicks a button to create the record which dismisses the popup window and the user has the new value ready for entering from the value list.

                    • 7. Re: Sort A CUSTOM Value List

                      The "Edit" box may be greyed out, but you can still type a value in the field itself...which will subsequently show up in the value list since it's now a field value.

                      • 8. Re: Sort A CUSTOM Value List

                        "The "Edit" box may be greyed out, but you can still type a value in the field itself...which will subsequently show up in the value list since it's now a field value"

                        That's true only if the value list is set up to display values from the same field being formatted with the value list. It's often much easier to manage such value lists by using a separate table to store the values.

                        • 9. Re: Sort A CUSTOM Value List

                          Are there threaded replies here? I was responding to Jeff's reply to raybaudi.

                          Using a drop down on a field using a value list based on the field itself is the simplest method to accomplish the OP's goal. It takes less than three minutes to add for the developer and five seconds for the user.

                          Adding an extra table and creating a UI for it is like 20x the work for the developer and 5x the work for the user.

                          • 10. Re: Sort A CUSTOM Value List

                            David, Not if you have 300 values in your table. The separate table with one record per value can be much easier to work with when you need to review and update what values are used in the list. I do use what you suggest but am also aware of the draw backs to doing so. Finding and eliminating typos to clean up your value list can be quite a chore as the number of records in your table grows.

                            Since replies aren't threaded, a point of clarification was needed as your reply suggested that any table based value list would automatically update.

                            • 11. Re: Sort A CUSTOM Value List


                              If you have 300 values, a drop down list is a terrible choice for a UI.

                              We're trying to solve a specific problem here, right? We don't know how many values are present in Jeff's list, but value  lists with more than a dozen or two values are rarely effective. If there are 300 values, there  are better tools.

                              We also don't know how strictly users are supposed to follow the value list. Typos in a "self-defining" value list indicate typos in the data, making it a good flag.

                              In the end, you need the tool to fit the job, and there's a few considerations that aren't addressed before we can say which is the "best" way.

                              • 12. Re: Sort A CUSTOM Value List

                                I just recently suggested to another use with a similar issue putting the value list table in a portal on the layout (would require a relationship which could be cartesian) and hiding it behind a tab control with a button  labled adjust Drop Down values to navigate to it. (you could open a new window  or a number of other ways to handle adding the new values.

                                 You could even script trigger an "other" choice to a value taken from a custom dialog to the table and back to the field.


                                • 13. Re: Sort A CUSTOM Value List

                                  David, This is a point you raised, not me.

                                  I was just trying to add a point of clarification on how table based value lists work so that someone who reads the post you made does not make an incorrect conclusion.

                                  There are times when large value lists are unavoidable. Granted, one does what one can to make them more manageable by using conditional value lists, auto-complete and scripted support, but you still end up with a separate table of values to manage and where using the database tools for managing records comes in very handy. Consider how many invoicing system are in use where the products table serves as the value source for a value list used in the invoice's line items portal.

                                  Even with very short lists of values, I often find the effort needed to create a separate table still makes managing the value list easier than drawing the values directly from a field in the table being edited. I did fail to post as clearly as I intended. That "300 values" was a reference to the table being edited, not the table serving as the value source.

                                  • 14. Re: Sort A CUSTOM Value List

                                    I've spent several hours experimenting with an indexed text calc field for the purpose of ordering a drop down list in such a way as to keep people records void of first names always popping up at the top of the list.

                                    What is the scope of filemaker sorting with regard to Unicode values beyond alpha CHARS ?  I'm guessing case is ignored?



                                         c_lname_fname >
                                         Case((IsEmpty(fname) and not IsEmpty(lname));lame &"    some CHAR() or text to push record to list bottom   ";
                                                  (IsEmpty(lname) and not IsEmpty(fname));Char(110)&Char(111)& Char(95) & Char(110)&Char(97)&Char(109)&Char(101)& fname;
                                                  (IsEmpty(lname) and IsEmpty(fname));"• " & company;
                                         lname&" "&fname

                                         note:   Char(110) & Char(111) & Char(95) & Char (110) & Char(97) & Char(109) & Char(101) evaluates in data viewer but doesn't seem to work in calc ?

                                                   I also fooled with textSize("no name";8) to no avail ?

                                    I have seen several TO implementations which seem more complex than nessecary, what am I missing?
                                    Any suggestions will be most appreciated