8 Replies Latest reply on Jun 21, 2015 3:34 AM by erolst

    Sorting asc. and desc. in the same portal.

    JavierDura

      I am displaying some related records in a portal.

      These records have a dateField.

       

      I want to sort those records in the same portal in:

      - ascending order for future dates.

      - descending order for past dates.

       

       

      Example (assuming that today is the 20th of June):

       

      5th of July 2015

      10th of October 2015

      20th of January 2016

      8th of May 2015

      7th of March 2015

      25th of December 2014

       

      How can I do it?

      Thanks!

        • 1. Re: Sorting asc. and desc. in the same portal.
          Draco

          Hola Javier, que tal : 2 Select concatenados y ordenados de manera distinta,

          ( ...con corte al día de hoy )

           

          saludos

          Draco

          • 2. Re: Sorting asc. and desc. in the same portal.
            erolst

            Create a calc field, type date =

             

            Case (

              DateTable::theDate <= Get ( CurrentDate ) ;

              DateTable::theDate ;

              Let ( [

                maxMin =

                  ExecuteSQL ( "

                    SELECT Max ( theDate ), MIN ( theDate )

                    FROM DateTable

                    WHERE theDate > ?

                  " ; ¶ ; "" ; Get ( CurrentDate )

                ) ;

             

                theMax = GetValue ( maxMin ; 1 ) ;

                theMax = Date ( Middle ( theMax ; 6 ; 2 ) ; Right ( theMax ; 2 ) ; Left ( theMax ; 4 ) ) ;

             

                theMin = GetValue ( maxMin ; 2 ) ;

                theMin = Date ( Middle ( theMin ; 6 ; 2 ) ; Right ( theMin ; 2 ) ; Left ( theMin ; 4 ) )

                ] ;

                theMin + ( theMax - DateTable::theDate )

              )

            )

             

            and sort descending on this field.

             

            You could of course implement the same logic using a self-join where Table::cCurrentDate > Table_self::theDate.

            • 3. Re: Sorting asc. and desc. in the same portal.
              Benjamin Fehr

              You could of course implement the same logic using a self-join where Table::cCurrentDate > Table_self::theDate.

               

              2 identical TO's, - one with sort [ascending], one with sort [descending].

              2 Portals layered for both TO's with dedicated 'HideObject' formula if-sorted-by.

              mmh, not quite sure about this, but used it before. I also once had Register-Elements: each element with portal with TO according to sort-state.

               

              If you would use List-View instead of a Portal, you could easy achieve this with GTRR (TO_ascending / TO_descending) within the same layout.

              • 4. Re: Sorting asc. and desc. in the same portal.
                erolst

                The self_join is merely for retrieving the Min() and Max() values for records where theDate > today.

                efficientbizz wrote:

                2 identical TO's, - one with sort [ascending], one with sort [descending].

                2 Portals layered for both TO's with dedicated 'HideObject' formula if-sorted-by.

                You could use the same TO as before, and create two portals with different sort fields and filter conditions; but how would you make them appear as one single list, i.e. shift records to their correct position within the virtual combined list? (And what about scrolling etc.)

                 

                Using a calculated field and a single UI device seems to me a much more straightforward solution.

                 

                efficientbizz wrote:

                If you would use List-View instead of a Portal, you could easy achieve this with GTRR (TO_ascending / TO_descending) within the same layout.

                I don't understand that sentence. How would that (whatever it is) give you a single sorted list of all records (or an arbitrary found set)?

                • 5. Re: Sorting asc. and desc. in the same portal.
                  Benjamin Fehr

                  Sorry, looks like I lost myself in some GTRR and TO studies in List-View to oversee the nearest possibilities:  script sort by.

                  Off topic anyway since this has to be achieved with Portal.

                  • 6. Re: Sorting asc. and desc. in the same portal.
                    JavierDura

                    It works! Thanks, erolst.

                    • 7. Re: Sorting asc. and desc. in the same portal.
                      user19752

                      I like simple, unstored calculation field type number for sort ascending

                       

                      Abs ( theDate - Get ( CurrentDate) ) + Case ( Get ( CurrentDate ) > theDate ; GetAsNumber ( Date ( 12 ; 31 ; 4000 ) ) )

                       

                      get difference from current date as nearer date first, add max date value as number to all past dates after future dates.

                      • 8. Re: Sorting asc. and desc. in the same portal.
                        erolst

                        Yup; same idea, but much simpler.