5 Replies Latest reply on Jan 23, 2009 2:18 PM by olinder

    sort related value list



      sort related value list


      I have an Invoice database. The Products are listed in the Invoice table from related records in Products table as a drop down list which automatically sorts by alphabetical name. I want it to sort in a custom order since there are dozens of products. How do I do that?

      Also how do I add an "other" option to that list?

        • 1. Re: sort related value list



          Thank you for your post.


          When you create a value list based upon a related field, the order will always be sorted in ascending order.  At this time, there is no way to change that.


          To add this as a suggestion, please fill out the feature request form at:




          I could copy and paste this suggestion for you, but there are questions asked that only you can answer.  When entering the request, please explain why you need this implemented. 


          Also, there is no option to add an "Other" option.  You can get around this in FileMaker Pro 10 by having a dummy "Other" record, and then use script triggers to check for "Other" and jump to a layout where you can add the new related record.



          FileMaker, Inc. 

          • 2. Re: sort related value list

            howdy olinder,


            TSGal covered it all pretty well (I was watching and hoping she'd have a "Yes" 'cause I wanted to learn it too...shucks.).


            There are a couple of work-arounds I use that might be applicable...they're yours if you want 'em, or toss 'em out.


            1. To sort into a custom order, make another field in the same table as your product name {$sortfield}.  Number the records into the order you want them by adding numbers into {$sortfield}.  Show the product name field through a portal that you've made to LOOK like a dropdown by jumping between layouts, and have the portal sorted according to the $sortfield.  Slick? Not even close.  Functional? Yes.


            2. You can work via the script trigger in FMP10 if you have it as TSGal describes, or you can make the value list "custom" and allow "edit" as an option (This is dangerous as it relies on collective self-control of humans which is rare indeed).

             Or you do the way I do which is to leave the value list based on the product field, engage "autocomplete using value list", but leave the operator able to override the field validation.  This will NOT create a new record in your Products Table...but it will at least let the user type in the new product number.  It will also give a warning that it is a new number.  The rest is training folks to tell you when a new number needs to be added to the product table.


            Note that using both ideas above could be done by making a couple of records in the Product table $sortfield=999999999, Prodcutname= Other and $sortfield=999999998, Productname = Other


            That way folks could choose "Other" from the bottom of the list and overwrite it with the new Product number.  Choose carefully when giving the ability to overwrite, though...


            What do yo think?

            • 3. Re: sort related value list

              Thanks, I will try those.

              Here is another somewhat lame workaroud:

              Put a prefix on all the product names corresponding to my custom sort order, but change the prefix to a transparent color. It will sort by custom sort order, but will be invisible.

              Bad idea?

              • 4. Re: sort related value list



                Good idea in theory.  However, when you create a prefix to be transparent, it will not be transparent in the Value list.



                FileMaker, Inc. 

                • 5. Re: sort related value list
                     Ah, yes. Thanks.