2

I have a formula field that concatenates values from various fields within a custom object. Each of these items has a simple numerical value. I've created this formula field to summarize certain items and have it appear in mail merge template we've designed.

I am trying to determine a way to sort this formula field by the value of each item in descending order. For instance 10 scarves, 5 letters, 4 socks.

Let me know if this is possible, formula code is below.

Thanks again.

SUBSTITUTE( 
IF(ISBLANK(Total_LettersTally__c),""," "&TEXT(Total_LettersTally__c) & " letters,") 
&IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " Gators,") 
&IF(ISBLANK(Paracords__c),"", " "&TEXT(Paracords__c) & " paracord bracelets,") 
&IF(ISBLANK(Cool_Ties__c),"", " "&TEXT(Cool_Ties__c) & " cool ties,") 
&IF(ISBLANK(Phone_Cards__c),"", " "&TEXT(Phone_Cards__c) & " phone cards,") 
&IF(ISBLANK(Battalion_Buddy__c),"", " "&TEXT(Battalion_Buddy__c) & " battalion buddies,") 
&IF(ISBLANK(Comic_Books__c),"", " "&TEXT(Comic_Books__c) & " comic books,") 
&IF(ISBLANK(Handmade_Cards__c),"", " "&TEXT(Handmade_Cards__c) & " handmade cards,") 
&IF(ISBLANK(Care_Kits__c),"", " "&TEXT(Care_Kits__c) & " care kits,") 
&IF(ISBLANK(Gift_Cards__c),"", " "&TEXT(Gift_Cards__c) & " gift cards,") 
&IF(ISBLANK(Socks__c),"", " "&TEXT(Socks__c) & " socks,") 
&IF(ISBLANK(Girl_Scout_Cookies__c),"", " "&TEXT(Girl_Scout_Cookies__c) & " girl scout cookies,") 
&IF(ISBLANK(Food_And_Snacks__c),"", " "&TEXT(Food_And_Snacks__c) & " food and snack items,") 
&IF(ISBLANK(Toys_Animals_Bears__c),"", " "&TEXT(Toys_Animals_Bears__c)& " toys, animals And bears,") 
&IF(ISBLANK(Beanie_Babies__c),"", " "&TEXT(Beanie_Babies__c)& " beanie babies,") 
&IF(ISBLANK(Hats__c),"", " "&TEXT(Hats__c) & " hats," ) 
&IF(ISBLANK(Scarves__c),"", " "&TEXT(Scarves__c)& " scarves,") 
&IF(ISBLANK(Video_Games__c),"", " "&TEXT(Video_Games__c)& " video games,") 
&IF(ISBLANK(CDs_DVDs__c),"", " "&TEXT(CDs_DVDs__c)& " CD's & DVD's,") 
&IF(ISBLANK(Candy_lbs__c),"", " "&TEXT(Candy_lbs__c)& " lbs of candy,") 
&IF(ISBLANK(Assorted_Items__c),"", " "&TEXT(Assorted_Items__c)& " assorted items,") 
&IF(ISBLANK(Recyclables__c),".", " "&TEXT(Recyclables__c)& " recyclable items,")& " ", 

",.", 

"" 
)
  • 2
    instead of keeping it as a formula field, it will be better to do the sorting & concatenating logic in a trigger and update a normal text field – Vamsi Krishna Oct 1 '15 at 17:34
  • This makes sense. I'm fairly new to triggers and am wondering how I could write such a trigger. I can definitely work with you to provide some details about our custom object and give you an idea of what we hope to achieve. – Medhanie Habte Oct 1 '15 at 18:12
0

The requirement can be fulfilled using a trigger. The following code snippet can be used inside the trigger for the custom object records:

Map<Integer, String> mapNumValueAndFieldLabel = new Map<Integer,String>();
String strMessage;

//null checkers for the field and adding the number value as key and the string message as the value if its not null/blank/zero
IF(Total_LettersTally__c != null && Total_LettersTally__c != '' && Integer.valueOf(Total_LettersTally__c) != 0)
    mapNumValueAndFieldLabel.put(Integer.valueOf(Total_LettersTally__c), Total_LettersTally__c + " letters");

IF(Gators__c != null && Gators__c != '' && Integer.valueOf(Gators__c) != 0)
    mapNumValueAndFieldLabel.put(Integer.valueOf(Gators__c), Gators__c + " Gators");

IF(Paracords__c != null && Paracords__c != '' && Integer.valueOf(Paracords__c) != 0)
    mapNumValueAndFieldLabel.put(Integer.valueOf(Paracords__c), Paracords__c + " paracord bracelets");

//and so on for other fields....

//Proceed further only if either of the fields have non zero values 
if(mapNumValueAndFieldLabel.values().size() > 0)
{
    List<Integer> lstNumValue = new List<integer> ();
    lstNumValue.addAll(mapNumValueAndFieldLabel.keyset());

    //Sort the Values (by default SFDC sorts it in ascending order)
    lstNumValue.sort();

    //Iterating through the sorted Integer list in reverse order (descending) to get the final String message from the map 
    for(Integer i = lstNumValue.size()-1; i >= 0; i--)
    {
        strMessage = mapNumValueAndFieldLabel.get(lstNumValue(i)) + ", ";
    }
}

//strMessage will hold the message you want to display

Hope this helps.

Thanks.

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.