10

Is it possible to have a related list sorted by a specific column while displaying the empty values last? By default, these are shown at the top of the list.

The field i want to sort after has the type Number(13,5), let's call it FieldToSortAfter__c. In SOQL i can do:

SELECT Field1__c, Field2__c
FROM MyObject__c
ORDER BY FieldToSortAfter__c NULLS LAST

I didn't find a counterpart for sorting related lists, though. I have already tried creating a formula field Order__c with this content:

IF( ISNULL(FieldToSortAfter__c),(
  9999999999999.99999
),(
  FieldToSortAfter__c
))

But when i want to use this to sort the related list, i have to display the column which looks ugly. Any suggestions on how to improve this?

Maybe one could build a VF page that mimicks the related list but it would need to have all the components (buttons, help link) that standard lists have. Another idea would be to use some Javascript to sort the rows on the client side. Is this possible while still using the standard layout?

8

Try to use the following formula of type "Text":

IF(ISBLANK(FieldToSortAfter__c),  IMAGE('/s.gif',' ') , ' ' + TEXT(FieldToSortAfter__c))

The formula checks for the blank value and if it is then it puts a transparent image so nobody can see it on the page. But if the field has a value then just puts a space before it so the sorter "thinks" that this value is "smaller" that the image :)

Here is my result:

enter image description here

  • 2
    This seems like a bit of an evil hack. Brilliant, but evil. – Daniel Ballinger May 29 '13 at 22:05
  • @DanielBallinger all approaches so far seem like evil hacks, including my own ones. I already gave up on finding a clean solution :) – Patrick Oscity May 30 '13 at 6:52
  • @padde I should mention that I happily gave the answer a +1. Sometimes a hack is the best you can hope for. Still, might be worth a note in a description field or something to address the WTF moment when the next person looks at it. – Daniel Ballinger May 30 '13 at 7:15
  • @DanielBallinger will do. The solution still isn't perfect, however. TEXT(...) does a poor job at displaying decimal numbers in the user's preferred locale, doesn't show leading zeros for values 0 < x < 1, doesn't show trailing zeros for values that don't use all of the decimal places and so on. It still looks very different. Another point is that text columns are left aligned whereas decimal columns are right aligned which makes it easier to grasp the magnitude of the number. But i guess there's no way around that. However i am very happy that the order is correct now. – Patrick Oscity May 30 '13 at 7:21
1

In the end, i went with a javascript solution. I built a VF page that overrides the standard View. These are the contents of the page:

<apex:page standardController="MyObject__c" showHeader="true">
<apex:includeScript value="{!$Resource.jquery}"/>
<apex:detail inlineEdit="true" id="detail" relatedList="true" showChatter="true">
    <script type="text/javascript" id="custom">
        var decimalFromSFString = function(str) {
            var x = parseFloat(str.replace(/[^0-9]/g, ''));
            return (isNaN(x)) ? null : x;
        };

        var getTable = function() {
            var relatedList = $('.bRelatedList .pbTitle h3:contains({!$ObjectType.MyObject__c.LabelPlural})').parents('.bRelatedList');
            return relatedList.find('table.list');
        };

        $(document).ready(function() {
            var table = getTable();

            var headerRows = table.find('.headerRow');
            var dataRows = table.find('.dataRow');

            var headerColumns   = headerRows.children('th');
            var sortColumn      = headerRows.children('th:contains({!$ObjectType.MyObject__c.fields.FieldToSortAfter__c.Label})');
            var sortColumnIndex = headerColumns.index(sortColumn);

            dataRows.detach();
            dataRows.removeClass('odd even first last');

            dataRows.sort(function(a,b){
                var x = decimalFromSFString($(a).children()[sortColumnIndex].innerHTML);
                var y = decimalFromSFString($(b).children()[sortColumnIndex].innerHTML);

                if (null === x) return  1;
                if (null === y) return -1;

                return x < y ? -1 : 1;
            });

            table.append(dataRows);

            table = getTable();
            table.find('.dataRow:odd').addClass('odd');
            table.find('.dataRow:even').addClass('even');
            table.find('.dataRow:first').addClass('first');
            table.find('.dataRow:last').addClass('last');
        });
    </script>
</apex:detail>
</apex:page>

Benefits:

  • Decimal formatting is preserved
  • Decimals are right aligned
  • No rubbish custom fields required

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.