10 Replies Latest reply on Jul 8, 2014 10:59 AM by BruceRobertson

    Re-Sorting a Portal

    maddie

      I currently have a portal that holds a customer's notes. Right now it sorts by status by a value list (active, complete and canceled), the date due (descending), and the last modified date/time (descending).

       

      My client wants to have the ability to click on the portal labels to re-sort the portal based on the label clicked, at the same time retaining the status and the last modified sorts.

       

      I've suggested using invisible tabs for the label headers so then the layout will update with a different portal, but it will have the sort he wants. He doesn't want that, he wants to see the pointer to change to a hand so then he knows there's a button there that can be clicked.

       

      I also tried using hiding portals layered on top of each other, when he clicks the label a global variable is set to the label name. All other portals are hidden while the correct portal is displayed. Unfortunately one of his co-workers has a slow connection, and complains that the portal disappears whenever he clicks on a label. It eventually re-appears, but it takes a minute or two for it to "revive".

       

      I am now trying to use a global field in the notes table that will grab the label name and will then change the sort based on whatever was grabbed.

       

      For example: the starting sort is status, date due, last mod. a user then clicks on the subject field. to view things alphabetically. the sort on the portal then changes to status, subject, last mod.

       

      Any ideas how this should work?

        • 1. Re: Re-Sorting a Portal
          Mike_Mitchell

          This older thread may be of some use.

           

          https://fmdev.filemaker.com/message/96090#96090

          1 of 1 people found this helpful
          • 3. Re: Re-Sorting a Portal
            maddie

            I created a Sort and a gSort (global) field in the notes table.

             

            The Sort field calculation looks like this:

             

            Case

            (

            gSort = "date due" ; Date Due ;

            gSort = "type" ; Type ;

            gSort = "subject" ; Subject ;

            gSort = "task to" ; Task To

            )

             

            In the script there is only one script step. It is Customer_Notes::gSort; Get(ScriptParameter). The script parameter is either due date, type, subject or task to.

             

            When I click on the button, the records are not sorted at all. Instead, the fields are being set with the data.

             

            For example: if I click on the due date label, gSort is changed to date due and Sort is changed to a random date in the portal records. Nothing is happening to resort the records.

            • 4. Re: Re-Sorting a Portal
              jlamprecht

              Could be the portal is not resorting. You may want to use refresh and see if it then resorts. If it doesn't, it may be that the sort logic is not correct.

               

              The link Mike sent over has an approach to using SQL for the sorting. I am a fan of that approach.

              1 of 1 people found this helpful
              • 5. Re: Re-Sorting a Portal
                maddie

                Flushing the cached join results and external data got the portal to refresh. He was very impressed with the results but now wants to sort the selected label ascending and descending.

                 

                Watching the link below might work, but he's on a tight budget and doesn't want to take up too much time with this "update".

                 

                https://www.youtube.com/watch?v=gCYqlgFflfI

                • 6. Re: Re-Sorting a Portal
                  erolst

                  maddie wrote:

                  Case

                  (

                  gSort = "date due" ; Date Due ;

                  gSort = "type" ; Type ;

                  gSort = "subject" ; Subject ;

                  gSort = "task to" ; Task To

                  )

                  maddie wrote:

                  but now wants to sort the selected label ascending and descending

                   

                  Add another field gSortOrder; change the existing field's name to, say, cSortASC, and its calculation to

                   

                  Case (

                    gSortOrder = "ASC" ;

                    Case (

                      gSort = "date due" ; Date Due ;

                      gSort = "type" ; Type ;

                      gSort = "subject" ; Subject ;

                      gSort = "task to" ; Task To

                     ) ;

                  1

                  )

                   

                  Add a second sort field, say, cSortDESC, with

                   

                  Case (

                    gSortOrder = "DESC" ;

                    Case (

                      gSort = "date due" ; Date Due ;

                      gSort = "type" ; Type ;

                      gSort = "subject" ; Subject ;

                      gSort = "task to" ; Task To

                     ) ;

                  1

                  )

                   

                  and sort the portal / relationship by cSortASC, ascending, and cSortDESC, descending.

                   

                  Modify your script to check if the clicked label is the active sort label; if so, only change gSortOrder from ASC to DESC or vice versa; otherwise, leave it and change the sort field.

                  • 7. Re: Re-Sorting a Portal
                    jbante

                    The Virtual Sort module does what you're asking for. There's some set-up involved, but once everything is in place users can sort portals on whatever combinations of fields they want.

                    • 8. Re: Re-Sorting a Portal
                      maddie

                      Here's what I ended up doing in the end.

                       

                      I created four fields, gSort (global text), gSortOrder (global text), cSortAsc (calc) and cSortDesc (calc)

                      erolst wrote:

                       

                      Case (

                        gSortOrder = "ASC" ;

                        Case (

                          gSort = "date due" ; Date Due ;

                          gSort = "type" ; Type ;

                          gSort = "subject" ; Subject ;

                          gSort = "task to" ; Task To

                         ) ;

                      1

                      )

                       

                      Add a second sort field, say, cSortDESC, with

                       

                      Case (

                        gSortOrder = "DESC" ;

                        Case (

                          gSort = "date due" ; Date Due ;

                          gSort = "type" ; Type ;

                          gSort = "subject" ; Subject ;

                          gSort = "task to" ; Task To

                         ) ;

                      1

                      )

                      I proceeded to use the calculations above, the calclulation result as text and checked the do not store calculation results. In the portal I set cSortAsc to ascending order and cSortDesc to descending order on the portal sort and then saved the layout.

                       

                      The script that I wrote looks like this:

                       

                      If [$$P = "" or $$P = "ASC"]

                           If [Get ( ScriptParameter ) = "date due"

                                Set Field [Customer_Notes::gSort; "date due"]

                           Else If [Get ( ScriptParameter ) = "type"

                                Set Field [Customer_Notes::gSort; "type"]

                           Else If [Get ( ScriptParameter ) = "subject"

                                Set Field [Customer_Notes::gSort; "subject"]

                           Else If [Get ( ScriptParameter ) = "task to"

                                Set Field [Customer_Notes::gSort; "task to"]

                           End If

                      Set Field [Customer_Notes::gSortOrder; "DESC"]

                      Set Variable [$$P; Value:"DESC"]

                      Refresh Window [Flush cahced join results; Flush cached external data]

                      Exit Script []

                      End If

                       

                      If [$$P = "" or $$P = "DESC"]

                           If [Get ( ScriptParameter ) = "date due"

                                Set Field [Customer_Notes::gSort; "date due"]

                           Else If [Get ( ScriptParameter ) = "type"

                                Set Field [Customer_Notes::gSort; "type"]

                           Else If [Get ( ScriptParameter ) = "subject"

                                Set Field [Customer_Notes::gSort; "subject"]

                           Else If [Get ( ScriptParameter ) = "task to"

                                Set Field [Customer_Notes::gSort; "task to"]

                           End If

                      Set Field [Customer_Notes::gSortOrder; "ASC"]

                      Set Variable [$$P; Value:"ASC"]

                      Refresh Window [Flush cahced join results; Flush cached external data]

                      Exit Script []

                      End If

                       

                       

                      I ended up putting exit scripts into each part because the script would pick up the global variable and set it back to it's original sort. Didn't want that. But in other words the portal is now sorting in both directions. Hooray!

                      • 9. Re: Re-Sorting a Portal
                        BruceRobertson

                        Which could be reduced to:

                         

                        If [ isEmpty($$P) or $$P = "ASC"]

                          Set Variable [$$P; Value:"DESC"]

                        Else

                          Set Variable [$$P; Value:"ASC"]

                        End If

                         

                        Set Field [Customer_Notes::gSortOrder; $$P ]

                        Set Field [Customer_Notes::gSort; get( scriptParameter ) ]

                         

                        Refresh Window [Flush cached join results; Flush cached external data]

                        Exit Script []

                        • 10. Re: Re-Sorting a Portal
                          BruceRobertson

                          Or even:

                           

                          Set Variable [$current_sort; Customer_Notes::gSortOrder]

                          Set Field [Customer_Notes::gSortOrder; case( isEmpty( $current_sort); “ASC”; $current_sort = “ASC”; “DESC”; “ASC”)

                          Set Field [Customer_Notes::gSort; get( scriptParameter ) ]

                          Refresh Window [Flush cached join results; Flush cached external data]

                          Exit Script []