5 Replies Latest reply on May 16, 2012 6:37 PM by darkenney

    Sorting Script



      Sorting Script


      DId a search, but couldn't find. Hopefully, there are some ideas. I have a global calculation field the result of the calculation is the name of the field I'd like to sort by. How do I pass that value into the Sort script step? Thanks. 

        • 1. Re: Sorting Script

          You couldn't find it because you can't do it.

          Best FileMaker can do is a script like this:

          If [YourTable::YourCalculationField = "Field1"
             Sort [no dialog ; restore ] //specify a sort order based on Field 1
          Else IF [ YourTable::YourcalculationField = "field 2"
             Sort [no dialog ; restore ] //specify a sort order based on Field 2
          Else IF [ YourTable::YourcalculationField = "field 3"
             Sort [no dialog ; restore ] //specify a sort order based on Field 3
          Else IF [ YourTable::YourcalculationField = "field 4"

          and so forth....

          • 2. Re: Sorting Script

            Oh. That's a surprise and dissapointment, but thank you for the quick and clear repsonse. It would seem that a Sort script step that could be passed 6 parameters would save a lot of effort.

            Between three sort fields, each with 6 choices, and each choice being available ASC or DSC - if I do the math on that - let's see that equals... 'quite a mess.' Better get started on the  if, else if. Thanks again. 

            • 3. Re: Sorting Script

              I don't understand where the maths is coming from.  You said you want to be able to nominate the field name to sort on.  Then you said "a Sort script step that could be passed 6 parameters would save a lot of effort.", so I assume you mean there will be 6 else-ifs... in the example that Phil gave.  But then after that you say "three sort fields", so I'm now thinking there are 3 fields you want to sort on.  Where do the other complexity multipiers come from?

              Do you mean that there are 3 fields in the sort sequence, and each of those fields holds 6 unique values, and each of those values could be sorted ascending or descending?  That's not how sorting would work.

              • 4. Re: Sorting Script

                This is IWP so I can't use FM Sort dialog - I am constructing my own. There are actually six fields that the user could choose to sort on (for example: creation_date, page, status, color, merchandising_group, material). This solution permits three levels of sort, which is consistent with how versions of Excel approached it and is a comfortable spot for the users.

                I have three fields (gsort1, gsort2, and gsort3) each is a value list.  Gsort1 is a value list with the 6 fields that the user may sort on. The user can pick any of those 6 and ASC, DSC for the first level sort. (For example: creation_date DSC.) Then gsort2 is a dynamic value list that shows the remaining 5 fields. The user can select any of those remaining and then click ASC DSC. (For example: status ASC.) Gsort3 shows the remaining 4 fields. The user can select one of those fields and pick asc desc. (For example: page ASC.) Then the sort would execute that three level sort. 

                So, gsort1 has 12 options (6 choices each Asc or Dsc). gsort2 has 10 options, and gsort3 has 8 options. So constructing an elseif for all of those sort permutations seemed like a lot. Doable but a lot. 

                My initial hope was that FileMaker might have a script step where I could pass it the values representing the names of the three fields to sort on and a value ASC, DSC, and it could execute. Let me know if you'd like more info.

                • 5. Re: Sorting Script

                  There were 350+ combinations of legitimate searches. Phil's if-elseif worked, but makes me nervous for long-term support, maintenance, performance. I have a counter in place to see what actually gets searched. Again, a Sort script step that you pass the names of the fields to sort on with asc, desc, custom list would seem to have value. I am new at FileMaker and having spent my time in Excel, I am surprised that offering up a sort that would be straightforward through the Sort dialog box (or commonplace in Excel) would require 700 lines of script steps.