5 Replies Latest reply on Jul 21, 2012 10:50 PM by philmodjunk

    How do I get sort my pop Up Menu based on a related field

    DarrenFrancis

      Title

      How do I get sort my pop Up Menu based on a related field

      Post

      Hi guys, I would like to have my Pop up menu sorted by a related field instead of alphabetically.

      Please refer below to understand my issue more clearly:

      Person
      =======
      __kp_person
      _kf_dressSize
      name

      DressSize
      =======
      __kp_dressSize
      dressSizeDesc
      dressSizeOrder

      The two tables are related via dressSize. I have placed a pop-up menu in my Layout which is based on Table Person. The pop-up menu gets its values from a value list which I had created which users values from both __kp_dressSize and dressSizeDesc from Table DressSize. "Show values on from second field" is checked. With this configure, my pop-up menu shows the text from dressSizeDesc but stores the primary key number from __kp_dressSize into _kf_dressSize when the user makes a selection. By default, the values show in the pop-up menu is sorted by alphabetical order.

      In some situations, this would be fine. However, if the values stored in dressSizeDesc is "Extra Small", "Small", "Medium" and "Large", I would prefer it to be sorted by a related field. In this case, I had created dressSizeOrder in table DressSize to identify the order in which the records should be displayed. I have tried searching the web and the forum and I can't find a solution to force the pop-up menu to be sorted by that related field. Could somebody help me out with this?

        • 1. Re: How do I get sort my pop Up Menu based on a related field
          philmodjunk

          This can be one of the more aggravating limitations of FileMaker. Ascending order is the only order FileMaker permits.

          Here are two options.

          1) Change the data in your dressSizeDesc field so that alphabetical ascending order works:

          a. Extra Small
          b. Small
          c. Medium
          d. Large

          adding the a., b., c. directly to your desciption field cause alpha sorting to work. You can also use a separate field and define a calculation field that combines them for use in the value list if you need the original descriptions for other uses and don't want the extra letters to appear when used elsewhere.

          2) "break the rules" and use the dressSizeDesc field as the key instead of the ID number. This makes for a single field value list and you can then use a second field in your value list as an invisible sorting key:

          Define a calculation field set to return text like this:

          Substitute ( 10^dressSizeOrder - 1 ; 9 ; " " )

          This calculation produces a single space character when dressSizeOrder = 1, two spaces when it is two and so on. Set your value list up to use dressSizeDesc as field 1 and this new calculation field as field two. Set it to sort values by the second field.

          If you need to use the ID number (So you no longer break the "Good db design rules", you can use a script to use the selected description to look up the ID number and enter it into a field in your parent table.

          • 2. Re: How do I get sort my pop Up Menu based on a related field
            DarrenFrancis

            Thanks for the quick response!

            Yes, that was what I did as a work around but it doesn't look very pretty with a running number or alphabets as a prefix. I hope that filemaker can work out a better solution for this in the near future.

             

            Thanks again!

            • 3. Re: How do I get sort my pop Up Menu based on a related field
              philmodjunk

              It's an oft requested feature over many years but no indications are out there that this will change anytime soon. You can add your voice to those who have already requested it: http://www.filemaker.com/company/contact/feature_request.html

              Don't forget the other option for avoiding this that I described.

              Another option:

              This would appear to be a table of values with a fairly number of records. If so, it may be more practical to eliminate the table altogether and just use a custom values value list. Custom values list in the order that they were entered in Manage | Value Lists so you can display values from a custom value list in any order that you want.

              • 4. Re: How do I get sort my pop Up Menu based on a related field
                DarrenFrancis

                Thanks for the response and suggestions once again!

                Yeah, its surprising that they didn't add this feature through the years as it doesn't sound like something really hard to implement. I'm suspecting that the reason why it hasn't been done is because access can do it and there are some legal implications to have that feature as well? I hope not.

                I will explore both options but I'm leaning towards option 1 over custom value list because there is a lot more information that I want to keep track other than what was stated above for each record.

                e.g.

                HairColour
                =====
                __kp_hairColour (number) - primary key
                hairColourDescription (text) - value to be displayed in pop-up menu etc
                hairColourGuideline (text) - guidelines on how to differeniate this color from others. e.g. difference between auburn, chestnut and blonde
                hairColourOrder (number) - the order in which I would like to display the list in
                hairColourSample (container) - a sample picture of how each different hair color looks like

                By storing the info this way, not only do I have a list of all the colours available for hair, I can store more details related to it for each record. Also, I can add new options if any in the future. This is just an examples, I have other tables which have more records that share the same issue.

                Thanks for helping me out along the way. I'm very new to the product so I still need more time to figure things out. In fact, I just purchased the full version of this software yesterday evening and only have a few days of experience with the trial copy.

                • 5. Re: How do I get sort my pop Up Menu based on a related field
                  philmodjunk

                  I'd be extremely suprised that there is any such legal barrier. Access uses an SQL expression to generate the values that appear in its version of a value list that is quite generic so there's nothing there sufficiently unique that it could be treated as intellectual property.

                  Table based value lists in FileMaker access the index of a field to generate the values listed. The values in the index are already ordered in ascending order (and do not contain duplicates) and so the value lists generated from them are automatically in this order. This explains the how, but not the why--why FileMaker hasn't invested the resources to provide more options for value lists. Displaying more than two columns of values, not only sorting in a different order, but using a field not visible in the value list for determining the sort order are all much needed improvements in this part of FileMaker's feature set.