5 Replies Latest reply on May 20, 2015 6:41 PM by BarryYarkoni

    Sort Records By Field (more than 1)



      Sort Records By Field (more than 1)


      I am trying to create a dyamic way to sort records by more than one field.I don't want to hard code it which one has to do with sort records function. Therefore I have come up with a way to pass the field name via a script parameter to the script which sorts the field based on the scriptparameter using sort records by field.

      This works very well when I have one field to sort by, the problem is I am trying to use the same method on a layout with a subsummary field. I need to sort by the field that I receive via the script parameter as well as the sub summary part. How can I accomplish this?

      If I sort via sort records by field it looses the sub summary part and if I sort via subsummary part it looses my other sort. Please keep in mind I want to stay away from hardcoding it in the sort records script step.

      Is there a way to "sort records by field" by multiple fields. If I use the sort records by field script step multiple times it only sorts by the last script step.

      Any suggestions would be greatly appreciated.



        • 1. Re: Sort Records By Field (more than 1)

          I don't completely understand what you are trying to accomplish, but you can pass multi valued script parameters.  You could then pass the script parameter to the script, and parse it. Then use multiple sorts that pass each part of a parsed script parameter.  Although I'm sure one of the local brilliant contributors on this site may have a much easier way.

          edit:  A simpler way may be to just use a small picker layout, with global fields that a user can select which type of find or sort you need and make the appropriate sort.


          • 2. Re: Sort Records By Field (more than 1)

            Hi Steve, Thanks for your response. However thats not going to work as I know what the second sort criteria is, I don't need to pass it via a script parameter. It is the field used for the sub summary part.

            What I would like to do is sort by field received from script parameter and from field used in sub summary part. However the sort records by field seems to only allow only one sort criteria.

            • 3. Re: Sort Records By Field (more than 1)

              It would be very nice if there was a better option, but your only practical option unless some plug in designer out there came up with a better way is to use a lot of Sort Records steps inside bunch of If - ElseIF - End IF script steps to specify the correct sort order that combines the sub summary part's break field with the user specified sort field.

              Say you have 3 columns of data, (fields A, B, C)  plus a break field D and you want the user to be able to sort by (A, D), (B, D) and (C, D) with the name of the user specified sort field passed as a script parameter.

              If [ Get (scriptParameter ) = "A" ]
                 Sort Records [Restore ; no dialog ] // AD sort
              Else IF [ Get ( ScriptParameter ) = "B" ]
                 Sort Records [Restore ; no dialog ] //BD sort
                  Sort Records [Restore ; no dialog ] //CD sort
              End If

              I can anticipate your objections, If you want to allow the user to select more fields or mix and match more than two fields in more than one basic order, this becomes a truly awful script, but this is best you can do with FileMaker built in features AFAIK.

              • 4. Re: Sort Records By Field (more than 1)

                Thanks PhilMod

                That is one way to accomplish it but as you say a messy and long script.

                I like this new sort records by field, its just a pity it only allows one sort criteria :(

                • 5. Re: Sort Records By Field (more than 1)

                  I have a similar problem.  Have a database of recipes/ingredients.  Within sections, ingredients are in a sort order, which is in the table, but not printed, eg:

                     1 cup mayo
                      2 tablespoons ketchup
                      pinch salt

                      1 cup shredded lettuce
                      1 medium tomato, diced

                  If I sort by the ingredient order first (not shown), I lose the sub-summary headers, If I sort by the sections first, the ingredients are printed in the wrong order within the sections.

                  What am I missing???