13 Replies Latest reply on Oct 23, 2015 12:22 PM by G T

    Sort via script, using Get(ScriptParameter)

    jdevans

      I have the need to write a single script that can be used to sort list views.

      I want to use a Boolean field to toggle 1 or 0, Use that bool to determine Ascending or Descending, but I want to pass a script parameter of the field name to the script to be used as which field to sort.

       

      For example:

       

      Sort Records by Field [Ascending; Get(ScriptParameter)]

       

      But I haven't been able to force Filemaker 14 to allow this. When I click specify on the field, it takes me to the dialog where I can select a field, instead of where I'd like to insert a calculation (Get(ScriptParameter)).

       

      Anyone have any idea if this is possible?

       

      Thanks

        • 1. Re: Sort via script, using Get(ScriptParameter)
          PeterWindle

          I have not tried this myself, however...

           

          you might want to note that the user can do this (without needing a script) by right mouse clicking on a field (Control click on mac)

          see image attached.

           

          It is possible to have a script that goes to field or goes to object - (GTO is better, because you can calculate the target) and then have the sort field by field script step after it, however, you may need to use a parameter to switch between ascending and descending.

          • 2. Re: Sort via script, using Get(ScriptParameter)
            jbrown

            ( I deleted my original post because I couldn't get it to work. Here's the text again, with revisions made)

             

            Evening.

             

            You can actually pass in the name of the field upon which you want to sort by passing in its name. For a header text I'm using as a button, I use "ClassName" as the parameter..

            Screen Shot 2015-10-22 at 9.45.52 PM.png

            Then use the GetField () function. Page 238 of the FM 14 function reference guide describes what you can do with this field: https://fmhelp.filemaker.com/docs/14/en/fmp14_functions_ref.pdf

            This function returns the contents of the field in the parenthesis.

             

            To set up sorting using this function:

            You create two new fields in your table:

            1. SortSelection - global, text

            2. SortKey - calculated field:  GetField(SortSelection)  

            Screen Shot 2015-10-22 at 9.45.29 PM.png

            In your script, grab the script parameter and set the parameter into the SortSelection field. Then the next step is to sort by the SortKey field.

            Screen Shot 2015-10-22 at 9.45.40 PM.png

            You can then use your toggle to decide if you should sort by that SortKey field ASC or DESC.

            • 3. Re: Sort via script, using Get(ScriptParameter)
              jdevans

              So far, I haven't gotten this to work. The SortSelect field in my data viewer contains the value of the primary key field of the record selected on the list view layout, instead of the Field Name, in this case the primary key field (_pk_employee_id). I set the script parameter of the button to Employees::_pk_employee_id.

              No matter how, thusfar, I've re-arranged this setup, I can't get the sort_key field to show any value. Right now in my data viewer it is "?". And SortSelection is "EMP0001"

               

              I'll keep trying. I understand the concept, but I haven't been able to get Filemaker to cooperate.


              At one point, I changed the SortSelection field to Auto-Enter calculate- Get(ScriptParameter). But nothing is changing what's in the data viewer.

              • 4. Re: Sort via script, using Get(ScriptParameter)
                jbrown

                Yeah. Its tricky. That function is pretty confusing and the combination of GetFieldName () and GetField() is mind-boggling.

                 

                I'll put together a quick demo and attach it here.

                • 5. Re: Sort via script, using Get(ScriptParameter)
                  jdevans

                  Here is a screenshot of the data viewer when I activate the script via button click

                  • 6. Re: Sort via script, using Get(ScriptParameter)
                    jbrown

                    jdevans wrote:

                     

                    So far, I haven't gotten this to work. The SortSelect field in my data viewer contains the value of the primary key field of the record selected on the list view layout, instead of the Field Name, in this case the primary key field (_pk_employee_id). I set the script parameter of the button to Employees::_pk_employee_id.

                     

                    In your script parameter set the name of the field you want to sort by. For my header "Name", I chose the field "Name_LF". That gets passed into the SortSelection field. The SortKey field returns the value of "Name_LF" field.

                     

                    No matter how, thusfar, I've re-arranged this setup, I can't get the sort_key field to show any value. Right now in my data viewer it is "?". And SortSelection is "EMP0001"

                    Your pic in your reply shows that SortKey is showing the value of the field that is in the Sort Selection field.

                    • 7. Re: Sort via script, using Get(ScriptParameter)
                      dtcgnet

                      I've occasionally used a technique like to allow users to sort portals. Portals tend to not have so many records that sorting on an unstored calculated field becomes slow. List views, however, can often have hundreds or even thousands of records. Oftentimes, developers have relied on the "Show All"/"Sort"/"Go to Record (first)" method with list views. Sorting large amounts of records, regularly, based on an unstored calc is a good way to make users feel that "FileMaker" is slow. Though the utility of this makes it attractive, be careful that the use of it doesn't kill the performance of your database.

                       

                      Having the ability to programmatically pass the actual name and sort direction via script would solve that, and would sure be nice to have.

                      • 8. Re: Sort via script, using Get(ScriptParameter)
                        jdevans

                        Yes, now the Employees::x_sort_selection is getting set to the field I want to sort by. So I changed by script to

                         

                        Set Field [Employees::sort_bool; Abs(Employees::sort_bool -1)]

                        Set Field [Employees::x_sort_selection; Get(ScriptParameter)]

                        If [Employees::sort_bool=1]

                             Sort Records by Field [Ascending; Employees::x_sort_selection]

                        Else If [Employees::sort_bool=0]

                             Sort Records by Field [Descending; Employees::x_sort_selection]

                        End If

                         

                        It kind of works like this, for whatever reason the descending sort tries to execute (the debugger goes to that if bool=0) but nothing changes on the layout.

                         

                        sort_key, keeps getting populated with the value in the field. According to the documentation, that's precisely what it's supposed to do, but I'm puzzled as to how it's getting set to a record value, rather than what shows up in x_sort_selection.

                         

                        Looking at Manage Database:

                        sort_key          Calculation          Unstored, from Employees, = GetField(x_sort_selection)

                        • 9. Re: Sort via script, using Get(ScriptParameter)
                          jbrown

                          You're right. This might be a drain on speed, using these unstored calcs.  I sorted 350,000 records by one two unstored fields and it took about 26 seconds to sort, on a local file. There's no index on these unstored fields, so the sorting takes longer.

                          When I sorted just using the sort dialog on the indexed field, the same records in the same file took about 5 seconds.

                          • 10. Re: Sort via script, using Get(ScriptParameter)
                            dtcgnet

                            Another thing to be aware of.

                             

                            You have to specify a field type for the unstored calculation. Since you won't know if the field will contain only text or only numbers, you should use "Text" as the result of the calculation. Without some "massaging", you'll get undesirable sort results.

                             

                            For example. Select "Birthday" as the sort field. Every birthdate will be seen as text. You'll see sorts like:

                            1/1/2015

                            1/1/2016

                            10/31/1999

                            2/1/2014

                             

                            It can be handled, but...it's another potential pitfall.

                            • 11. Re: Sort via script, using Get(ScriptParameter)
                              jdevans

                              It would be so simple and straightforward if Filemaker would just allow this:

                               

                              Sort Records by Field [Ascending; Get(ScriptParameter)].

                               

                              Not sure why this isn't allowed.

                              • 12. Re: Sort via script, using Get(ScriptParameter)
                                dtcgnet

                                Agreed. That would be awesome.

                                 

                                Even better...

                                 

                                Sort Records by Field ( [ $FieldNameSetbyScript ; Asc ] ; [ $OtherFieldNameSetbyScript ; Asc ] ; etc. )

                                 

                                I'd use it a LOT!

                                • 13. Re: Sort via script, using Get(ScriptParameter)
                                  G T

                                  I have uploaded the most recent version of a technique that you may find useful.

                                  It will sort according to your original post with a couple of other goodies.

                                   

                                  It consists of just one script.

                                   

                                  Smart Sort List Views v.1.0.3

                                   

                                  The original post can be seen here.

                                  Smart Sort List View

                                   

                                  Greg