7 Replies Latest reply on Jun 7, 2013 5:22 AM by Oliver_Reid

    Sorted portals

    GaryTate

      I'm trying to work out a way to dynamically sort a portal. For example, I display a list of invoices showing invoice number, customer, date, status. I want to be able to sort by any of the fields at the click of a button through the same portal and on the same layout.

       

      My best option would seem to be to build a value list and sort by this, you can then set the ale list to be the sorted order of the selected field using SQL and hey presto. That should work a treat in a single user environment as you just put the value list items into a field and build your value list from that (although I've not done it so it might not work ;-)

       

      But when you want to do this in a multi user environment your sort order will en raked up by envying else who is viewing the same layout. Global fields won't work as they aren't indexed and so can't build a value list. You could set up a table to hold all these value lists by user by layout, but thats quite long winded.

       

      Anyone else had any brain storms on this one?

       

      Cheers

       

      Gary

        • 1. Re: Sorted portals
          stephensexton

          Just an idea, but might be too much of a hit on performance depending on record numbers and network setup... Have you tried sorting by an unstored calculation field that is based on a global field value?  The global field value could be selected from a drop-down list of field names, and the unstored calculation could use the selected global field value in an  "Evaluate ()" function to evaluate the contents of that field in each row. 

          1 of 1 people found this helpful
          • 2. Re: Sorted portals
            jbante

            I've been working on a module I'm calling "Virtual Sort" that does exactly that. You can download the current prototype from GitHub. (Click on the ZIP icon, not the FileMaker file.) I'm hoping to put together some documentation and better examples this weekend. It's based on a technique demoed by John Ahn at the last DevCon for using ExecuteSQL to come up with the sort. My version adds sorting on multiple fields and builds the SQL for you. Creating and sorting by a value list was an I idea I considered, but I wound up not going with that. I don't remember exactly why.

            • 3. Re: Sorted portals
              Steve Wright

              There are a few examples of portal sorting techniques here:

               

              http://fmforums.com/forum/topic/83953-portal-sorting-with-fm12/

               

              Hope it helps.

              • 4. Re: Sorted portals
                GaryTate

                Stephen

                 

                I've tried this but using SQL to calculate the sort value and performance is fine (on my limited, local dataset). The layout needs a Refresh which always takes time, but it's very quick. I think we are on to a winner.

                 

                Thank

                 

                Gary

                • 5. Re: Sorted portals
                  GaryTate

                  Well that most certainly works ;-) and there looks to be a lot going on there, I'm sure you're working out sort order (ascending/descending) and all sorts of things.

                   

                  I did the following…

                  Set Field (gSort ; get(scriptparameter)

                  Refresh Window

                   

                  Then a field called Sort has the following calculation…

                  ExecuteSQL ( "SELECT \"" & gSort & "\" FROM \"Job\" WHERE \"PK_ID\" = " & PK_ID ; "" ; ¶ )

                   

                  Basically getting the contents of the field selected for the current record and inserting it. The portal is then sorted on the field Sort.

                   

                  Thanks for the example though, good work.

                   

                  G

                  • 6. Re: Sorted portals
                    manjit

                    Hi Gary,

                     

                    Though a good solution has been posted I would like to add some more options for sorting of portal records. Please look into the attached file where you can get options for 3 ways to sorting portal rows.

                     

                    1. Sort Portal rows by Custom Value list

                    2. Sort Portal rows by Custom Value list (According to Field Name for which you want to sort)

                    3. Sort Portal rows by respective column's values on click over the column headers

                     

                    [This is only giving Ascending order sorting, if you want both way you need to just add another condition.]

                     

                     

                     

                     

                    with regards,

                    Manjit Behera

                    Software Developer, Mindfire Solutions, India

                    http://www.mindfiresolutions.com

                    email:manjitb@mindfiresolutions.com

                    Skype: mfsi_manjitb

                    • 7. Re: Sorted portals
                      Oliver_Reid

                      You can also put several copies of a portal in serparate tabs, each with a specific sort specification. The tab control can be made "invisible" with the "Sort by xxx" choices used as the tab captions. You think you are pushing a button, but actually switching tabs. Very fast as the sort can be on stored fields rather than calc.


                      Andy Gaunt came up with this idea