Apex Code Development You need to sign in to do that
Don't have an account?
Medhanie Habte Sorting a custom contactenated formula field with number values in descending order
Greetings, I am trying to determine a way to sort in descending order a custom concatenated formula that contains number values for various fields. So for instance, If I have 1 letter and 10 cards, the field result should amount to 10 cards and 1 letter.
Additionally, I am trying to determine a way to include a formula for instance if a value in any of these items amounts to one, then it would just read as one letter.
Also is there a way to include an and in this formula after the last comma in the event that there a values in more than one item, so it would 11 handmade card, 11 phone care and one letter.
Let me know if this is possible and many thanks.
Additionally, I am trying to determine a way to include a formula for instance if a value in any of these items amounts to one, then it would just read as one letter.
Also is there a way to include an and in this formula after the last comma in the event that there a values in more than one item, so it would 11 handmade card, 11 phone care and one letter.
Let me know if this is possible and many thanks.
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,")& " ", ",.", "" )
1) You can't sort values within a formula field, you'll need to use apex code.
2) However if you wanted your formula field that contained a number to itself be text sorted in a report here is how you can do that:
In order to properly sort numbers you need to ensure they are padded. Example if you try to text sort 1,2,10 it will show up as 1,10,2. So you need to make your numbers look lke 01, 02, 10. However you have to pad your numbers to your largest value. If you expect the highest number to be 100 you need to have 001, 002, 010, 100. And if you expect 1,000 then you'll need 0001, 0002, 0010, 0100, 1000, etc. Now they can be sorted by text.
Here is a formula that will pad numbers from 1 to 10:
/* If the number is less than 10 pad with 2 zeros, if it's 10 to 99 then pad with 1 zero */
IF(Number__c < 10, "0","") & TEXT(Number__c)
Here is a formula that will pad numbers from 1 to 100:
/* If the number is less than 10 pad with 2 zeros, if it's 10 to 99 then pad with 1 zero */
IF(Number__c < 10, "00", IF(Number__c < 100, "0","")) & TEXT(Number__c)
Here is a formula that will pad numbers from 1 to 1000:
/* If the number is less than 10 pad with 2 zeros, if it's 10 to 99 then pad with 1 zero */
IF(Number__c < 10, "000", IF(Number__c < 100, "00", IF(Number__c < 1000, "0",""))) & TEXT(Number__c)
Hopefully this shows you the pattern.
Now you can sort your formula field in a report.
Hi Caleb, How can have the first 5 records which has the Field1__c as 1, 2, 3, 4 and 5. Please see the below screen shot for reference, need you help and suggestion.
public class accountprioritysort {
public list<Company_Priorities__c> acts {set;get;}
public accountprioritysort (){
acts = [select Field1__c,Account_Priorities__c from Company_Priorities__c ORDER BY Field1__c ASC limit 5];
}
}