3 Replies Latest reply on Apr 25, 2014 9:06 AM by philmodjunk

    Sorting records with blanks at the end issue

    KristofferDystheBilek

      Title

      Sorting records with blanks at the end issue

      Post

           I've added a calculation field called "IncomeEmpty" to my table and specified it with this calculation: 

      If ( IsEmpty ( SalesIncome ) ; "1" ; SalesIncome )

           If I now sort my records first by IncomeEmpty (Ascending) then SalesIncome (Ascending) it works fine. The blanks are moved to the end of the table and the records are sorted by SalesIncome in ascending order.

           Though I want the records to be sorted by SalesIncome in descending order still with blanks at the end. If I change the sort option for SalesIncome to descending, the blanks are back at the front again. How can I fix this issue?

        • 1. Re: Sorting records with blanks at the end issue
          philmodjunk

               Sort by SalesIncome and only by SalesIncome in descending order and the Records with empty SalesIncome fields will be listed last.

          • 2. Re: Sorting records with blanks at the end issue
            KristofferDystheBilek

                 No, the records with empty (not existing) SalesIncome fields are still listed first if I do that..

                 (The SalesIncome field is in a related table (Sales Reports). If the customer has bought nothing, the Customer ID is not represented in the Sales Reports table at all.)

                 So the calculation is actually like this:

                 SalesIncome:

            If ( IsEmpty ( Sales Reports::SalesIncome ) ; "1" ; Sales Reports::SalesIncome )

            • 3. Re: Sorting records with blanks at the end issue
              philmodjunk

                   (The SalesIncome field is in a related table (Sales Reports). If the customer has bought nothing, the Customer ID is not represented in the Sales Reports table at all.)

                   That is new information not in your original post.

                   In cases where there is no related record (as opposed to a field in the layout's table that is empty), the records will still list first.

                   I'd use 0 or a negative number in place of 1 in your calculation, but it looks like you just need to clear the "do not evaluate if all referenced fields are empty" check box.

                   That's all I needed to do to get the records with no related record in the related table to sort to the bottom of the list.