11 Replies Latest reply on Apr 9, 2009 9:39 PM by ambassador53

    Sorting records in Portal -problem

    ambassador53

      Title

      Sorting records in Portal -problem

      Post

      I have a portal set up to show related records sorted by date, descending. Nevertheless, the related records show up in a random date order in the portal. Cannot figure out why.

       

      Have followed the instructions in the Help system which describe how to set up a portal and sort the records that it displays.

      Need suggestions.

        • 1. Re: Sorting records in Portal -problem
          synergy46
             There are several places to sort:  Layout, Portal and Script.   Check each one.  My 'favorite' bad habit is to copy an existing form and then forget to reset the sorting on the portal level.  Hope this helps.
          • 2. Re: Sorting records in Portal -problem
            comment_1
              

            Two things worth checking:

             

            1. That the field you sort by is indeed a Date field;

             

            2. That the field you sort by comes from the same table occurrence that the portal is pointing to. 

            • 3. Re: Sorting records in Portal -problem
              comment_1
                

              synergy46 wrote:
              There are several places to sort: Layout, Portal and Script.

              Not really. You can specify a sort order for the relationship itself, and you can override the sort order of the relationship when defining a portal. You cannot sort a portal by script.


              • 4. Re: Sorting records in Portal -problem
                marcusedward
                  

                 

                You actually can sort a portal with a script.  It just takes some setting up.  

                 

                Here is what you do

                 

                1.  create a global text field in the table which the portal refrences (we'll call it global sort)

                2.  create a calculation in the same table that looks something like this....

                Case ( 

                yourtable::global sort = "date"; GetAsNumber ( yourtable::date ) ;

                yourtable::global sort = "project name" ; yourtable:: project ;

                yourtable::global sort = "client" ; yourtable::client  ;

                yourtable::global sort = "completion date" ;  Case ( IsEmpty (project_date_delivered date); "9999999" ; 1000000 -GetAsNumber (project_date_delivered date))) 

                 

                you can continue to expand the calc to include as many fields as you need.  Changing your date into numbers will allow you to use a text field for the calc result and still have it sort properly.  Also using 1000000 -GetAsNumber (project_date_delivered date) reverses the sort so you'd see the most recent delivered on top even though your sort is set to smallest to largest (I am also forcing the undelivered to the bottom of the list)

                 

                3. in your table relationship set the table occurence for the portal to sort on the calculation field you just created (ascending order).

                4. deselect the sort option for the portal on the layout.

                5. create a script that will populate yourtable::global sort with get (scriptparameter) (depending on your setup you may also need a "commit" line in your script)

                6. create buttons on your layout with scriptparameters that match the triggers you set up in your calculation.

                 

                and that's it.... scripted sort for your portal   

                 

                You can also place yourtable::global sort directly on your layout and use a value list to populate it (skipping the scripting approach).   

                 

                 

                • 5. Re: Sorting records in Portal -problem
                  comment_1
                    

                  marcusedward wrote:

                  You actually can sort a portal with a script. 


                  No, you can't. Sorting by a calculation field, whose results depend on a global, that CAN be set by a script is NOT the same as telling a portal to sort by… (like you can with a found set).

                   

                   

                  BTW, the actual calculation needs to be much more complex in order to accommodate various data types - but I don't want to get into that and hijack this thread.


                  • 6. Re: Sorting records in Portal -problem
                    marcusedward
                      

                    comment wrote:

                    marcusedward wrote:

                    You actually can sort a portal with a script. 


                    No, you can't. Sorting by a calculation field, whose results depend on a global, that CAN be set by a script is NOT the same as telling a portal to sort by… (like you can with a found set).

                     

                     

                    BTW, the actual calculation needs to be much more complex in order to accommodate various data types - but I don't want to get into that and hijack this thread.


                     
                    You are correct it's not the same thing as simply running a sort but my solution does provide a sortable portal. 
                    Sorry you don't like my solution (or perhaps my wording).  The calc above works just fine for alabetical sort (ascending only) as well as date and numeric sorting (both ascending and decending).  For numeric sorting you simply add a static number (say 10000) to your numeric field (or subtract for decending sort order) thus avoiding the 1, 10 11 12 text sorting of numbers. The calc is copied directly from creative job tracking system used by 150 people on a daily basis.  Beyond that it also works with the web publishing engine.  I find this a much smoother solution in the web publishing vs. using a list layout which is cluncky at best.
                     
                    M. 

                     



                    • 7. Re: Sorting records in Portal -problem
                      comment_1
                         It may seem to be working because your actual data is limited in scope and does not expose the flaws. Try it with fractional and/or negative numbers, or very large numbers (above 10^9) and I am sure you will see what I mean. Or see which comes first: a date in 2009 or a date in 2900.
                      • 8. Re: Sorting records in Portal -problem
                        marcusedward
                          

                        comment wrote:
                        It may seem to be working because your actual data is limited in scope and does not expose the flaws. Try it with fractional and/or negative numbers, or very large numbers (above 10^9) and I am sure you will see what I mean. Or see which comes first: a date in 2009 or a date in 2900.
                         
                        Once again you are right... the sysem will have an issue with something due 900 years from now as it was posted.  It works absolutely perfect for any dates in the next 200 years though.  If concerned with something due 900 years from now all you need to do is add a static number and the problem is again solved.  The static number simply needs to be 10 times the largest number.  If the max number in my data field is 10 then add 100. If 100 then add 1000.  The text field will sort it just fine as long as each number has the same number of digits.
                         And yes you are right there are limits (such as negative numbers) but what I've posted works for a great many things and is extremely simple to set up and use.  

                         

                         


                        • 9. Re: Sorting records in Portal -problem
                          comment_1
                            

                          marcusedward wrote:
                          what I've posted works for a great many things and is extremely simple to set up and use.  

                          I have no quarrel with that. It's just that this is a public forum, and if a technique is limited, I believe it needs to be clearly stated.

                           

                          BTW, ever since version 8 I prefer to put "pre-sorted" portals in a tab control object.

                          • 10. Re: Sorting records in Portal -problem
                            marcusedward
                              

                            comment wrote:

                            marcusedward wrote:
                            what I've posted works for a great many things and is extremely simple to set up and use.  

                            I have no quarrel with that. It's just that this is a public forum, and if a technique is limited, I believe it needs to be clearly stated.

                             

                            BTW, ever since version 8 I prefer to put "pre-sorted" portals in a tab control object.


                             
                            I never thought to post the disadvantages to the technique as they applied to the original post... but then again every technique is limitted in some way.  
                             
                            Never thought to use tabs for presorted portals.   

                             


                            • 11. Re: Sorting records in Portal -problem
                              ambassador53
                                

                              You hit the nail on the head!  My "date" field was mistakenly created as a text field. Fixed that and all works!

                              Thanks much.