8 Replies Latest reply on Sep 5, 2012 6:17 AM by TimDietrich

    Sorting the records returned by newPerformScriptCommand

    dloughlin

      I need to sort the reulting set of records returned as the result of a

      newPerformScriptCommand

       

      Some of the PHP code is as follows:

       

      }else{

       

      $records = $find_result->getRecords ();

       

      I then get access fields with the $records object using

       

      $record ->getField

       

       

      It is this $records that I want to be able to sort by some of the fields within it.

       

      It would seem that the obvious thing to do would be to sort the records within the script, but the fieldnames that I would want to sort by are within the $findstr, and it is not possible (at least as far as I know), to sort by a calculated field name.

       

      I use the records to create a spreadsheet using PHPExcel, and unfortunately, PHPExcel does not seem to have the functionality to sort the rows before outputting.

       

      That leaves me with trying to sort the $records object (if that is possible).

       

      So I guess my question is, is that possible, and if it is, how?

       

      Any help would be greatly appreciated.

       

      Dennis Loughlin

       

       

       

       

      $script_result = $fm->newPerformScriptCommand ( 'RSFindsAsOfNew', 'GetAsOf Records from RelatedServices', $asof.'||'.$findstr );

      $find_result

      = @$script_result->execute ();

       

       

       

      if

      (FileMaker::isError ( $find_result ))
      $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(1,2, 'no records found');

       

        • 1. Re: Sorting the records returned by newPerformScriptCommand
          dloughlin

          The code should look as follows:

           

          $script_result = $fm->newPerformScriptCommand ( 'RSFindsAsOfNew', 'GetAsOf Records from RelatedServices', $asof.'||'.$findstr );

          $find_result = @$script_result->execute ();
          if (FileMaker::isError ( $find_result )){
          $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(1,2, 'no records found');
          }else{
            $records = $find_result->getRecords ();

          • 2. Re: Sorting the records returned by newPerformScriptCommand
            Malcolm

            What about getting the script to sort the result? Alternatively, look at setPreSortScript, "Sets a ScriptMaker script to be run after performing a Find command, but before sorting the result set."

             

            Malcolm

            • 3. Re: Sorting the records returned by newPerformScriptCommand
              dloughlin

              As I indicated in my original post, I would have preferred to sort the records in the script, but the field names that I would want to sort by, are part of the script parameter (the $findstr), and even though you can Set Field By Name (the name of the field being being derived from the script parameter), there apparently  is no way to sort by the derived field name.

              • 4. Re: Sorting the records returned by newPerformScriptCommand
                steve_ssh

                Hello Dennis,

                 

                Here is a thought for you to consider:

                 

                Going back to the idea of performing the sorting within the script:  The obstacle you face is that the SortRecords script step requires you to know and define your sort field(s) at the time that you are composing and saving the script, whereas your need is to be able to dynamically determine the sort fields at runtime, based on a global field or a script parameter or some such methodology.

                 

                Thus, the "missing" functionality is the ability to be able to have some kind of "sort by field name" step.

                 

                I think it would be worth investigating the potential of leveraging the same kind of techniques that people use when they perform "Dynamic Portal Sorting".

                 

                As an example:  http://www.filemakerhacks.com/?p=1284

                 

                Such dynamic portal sorting methodologies illustrate clever ways of realizing the ability to sort by a dynamically specified field.

                 

                It seems like it would be worth giving a try.

                 

                 

                Caveats:

                 

                1) While I've read about and often appreciated the dynamic portal sorting techniques, I haven't ever utilized them in a solution, and as such, I can't really speak to what you might expect with regard to performance, nor can I tip you off to any gotchas that might be involved (other than being careful about differentiating between sorting as text versus sorting as number).

                 

                2) You would need to concede adding at least one extra field to your schema.

                 

                3) If you need dynamic sorting based on more than a single sort field at a time, I'm sure that you could still use this concept, but it might get a little ugly.

                 

                 

                 

                Please let me know if I haven't connected enough of the dots to have this idea make sense.

                 

                Hope this helps & best,

                 

                -steve

                • 5. Re: Sorting the records returned by newPerformScriptCommand
                  Malcolm

                  As I indicated in my original post, I would have preferred to sort the records in the script, but the field names that I would want to sort by, are part of the script parameter (the $findstr), and even though you can Set Field By Name (the name of the field being being derived from the script parameter), there apparently  is no way to sort by the derived field name.

                   

                  You can still use $findstr to walk through a set of if/then, although that gets messy if you want to sort by more than one column.

                   

                  You don't say anything about the other suggestion. That seems to fit the bill. You are able to run a script and set the sort order using php. It is used with the Find command but that doesn't matter. Your script can over-ride the find and then you get to control the sort.

                   

                  Malcolm

                  • 6. Re: Sorting the records returned by newPerformScriptCommand
                    mbraendle

                    Asking the other way around: What find requests does the script that can't be done with the standard FM PHP API  Find and CompoundFind commands?

                    • 7. Re: Sorting the records returned by newPerformScriptCommand
                      dloughlin

                      I couldn't use the API Finds, as I needed to set a Global value (a date field), which is necessary for the finds.

                       

                      Thank you for your responses

                      • 8. Re: Sorting the records returned by newPerformScriptCommand
                        TimDietrich

                        Here's another idea to consider - and it's a technique I use quite often.

                         

                        First, do a FindAll command. This might seem inefficient and odd, but it is necessary in order for the rest of the API request to work. (And don't worry, you won't be returning all records.)

                         

                             $fm_request = $fm -> newFindAllCommand ( 'PHP - Sites' );

                         

                        Next, use the setPreSortScript method to indicate what script that you want to run - and you can pass in parameters, too, of necessary. In your case, you could pass in the value that you want to set in that Global field, then do your find via the script.

                         

                             $fm_request -> setPreSortScript ( 'Perform Quick Find', $your_param] );

                         

                        Then use the addSortRule method to specify your sort order. It sounds like you are sorting dynamically, so just use PHP to specify the field names, orders, etc.

                         

                             $fm_request -> addSortRule( 'Your_Field', 1, FILEMAKER_SORT_ASCEND );

                         

                        // If you are paging the find results (breaking the overall found set into smaller "chunks"), use the setRange method to indicate what records you want to have returned.

                         

                             $fm_request -> setRange( $skip, $max );

                         

                        // And finally, execute the request.

                         

                             $fm_result = @ $fm_request -> execute ();

                         

                        What you should end up with are...

                        • The results of whatever the found set was when the script was run.

                        • The records should be sorted properly (based on your "setPreSortScript" settings).

                        • And again, if you are paging the results, you should get the correct record set (based on your setRange settings).

                         

                        This is a great way to use the API to get a record set back via the results of a script.

                         

                        Hope this helps.

                         

                        -- Tim