3 Replies Latest reply on Dec 20, 2013 5:53 AM by erolst

    Sort blanks last

    AllegroDataSolutions

      I have a portal that is sorted by a value in a global field. User selects from a value list in the field used to sort the portal:


      Priority

      Completed

      Date

      Event Name

       

      If "Priority" is selected, a formula gets the value of a field which may contain a numeric value from 1 to 9.
      If "Completed" is selected, a formula gets the value of a field which may contain a numeric value of 1.

      If "Date" is selected, a formula gets the numeric value of a field containing a date.

      If "Event Name" is selected, a formula gets the value of a field containing text.

       

      Any of the fields referenced in the formula may have been left blank.

      The calculation returns the resut as text.

       

      It all works fine -- except that, when the list is sorted in ascending order, records in which the selected sort field is empty sort at the top of the list. For example, if sorted by Priority, all the records with no value for priority appear in the top rows of the portal and the user has to scroll down to see all those with a priority of 1, followed by those with a priority of 2, and so on.

       

      If I sort in descending order, the Event Name field sorts in reverse alphabetical order -- which, obviously, we do not want.

       

      I am looking for the simplest way to correct this last problem.

       

      Thanks.

        • 1. Re: Sort blanks last
          erolst

          Add a condition to your sort field calculation to return a string like "zzz" if the result of the previous calculation steps (i.e. the pertinent field as per sort field selection) is empty.

          • 2. Re: Sort blanks last
            AllegroDataSolutions

            Thanks. I had tried that (using Char(255) for blank values) -- but, for some reason, I couldn't get it to work. I don't remember exactly how I coded it. Perhaps the nesting of the conditions was too complicated. This time I just did simple If() statements for each test for a blank value and nested them into the  Case() statement and it worked fine.

            • 3. Re: Sort blanks last
              erolst

              Well, the entire purpose of your calculation field is to calculate a final value; it should be sufficient to just check that value, instead of scrutinizing each intermediate step.

               

              For example …

               

              Let ( [

              sf = globals::gSortField ;

              sc = Case (

              sf = "Name" ; name ;

              ……… // other fields with type conversion etc.

              )

              ] ;

              Case ( IsEmpty ( sc ) ; "zzz" ; sc )

              )