I have a formula field that I built in a custom object that combines various fields into this one field for a mail merge. The custom object is called Product Donations, and there are about 12 fields referencing various line items that are referenced here.
SUBSTITUTE(
IF(ISBLANK(NEW_Letters__c),"", Letter_Count__c)
&IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " handmade gators,")
&IF(ISBLANK(Paracords__c),"", " "&TEXT(Paracords__c) & " paracord bracelets,")
&IF(ISBLANK(Cool_Ties__c),"", " "&TEXT(Cool_Ties__c) & " handmade 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),"", " comic books,")
&IF(ISBLANK(Magazines__c),"", " magazines,")
&IF(ISBLANK(Books__c),"", " books,")
&IF(ISBLANK(Handmade_Cards__c),"", " handmade cards,")
&IF(ISBLANK(Care_Kits__c),""," care kits,")
&IF(ISBLANK(Gift_Cards__c),""," gift cards,")
&IF(ISBLANK(Socks__c),"", " socks,")
&IF(ISBLANK(Girl_Scout_Cookies__c),"", " "&TEXT(Girl_Scout_Cookies__c) & " cookies,")
&IF(ISBLANK(Food_And_Snacks__c),"", " snack foods,")
&IF(ISBLANK(Toys_Animals_Bears__c),""," assorted toys,")
&IF(ISBLANK(Beanie_Babies__c),"", " "&TEXT(Beanie_Babies__c)& " Beanie Babies,")
&IF(ISBLANK(Hats__c),"", " "&TEXT(Hats__c) & " handmade hats," )
&IF(ISBLANK(Scarves__c),"", " "&TEXT(Scarves__c)& " handmade scarves,")
&IF(ISBLANK(Video_Games__c),"", " "&TEXT(Video_Games__c)& " video games,")
&IF(ISBLANK(CDs_DVDs__c),"", " "&TEXT(CDs_DVDs__c)& " CD's,")
&IF(ISBLANK(CDs_DVDs__c),"", " "&TEXT(DVDs__c)& " DVD's,")
&IF(ISBLANK(Candy_lbs__c),"", " "&TEXT(Candy_lbs__c)& " Lbs of candy,")
&IF(ISBLANK(Assorted_Items__c),""," assorted items,")
&IF(ISBLANK(Recyclables__c),"", " recyclable items,")& " ",
",.",
""
)
I am also looking for a way to include the word "and" after the comma if multiple line items exist and leave out the numerical value if the value equals 1. The result should read like this.
letters, scarves, 20 paracords and assorted items.
Instead of
letters, 1 scarves, 20 paracords, assorted items
Also is there a formula to singularize items that have a 1 in it. For instance 1 scarves should just say scarf.
I can help with any further questions, even set up a trigger if needed.
Hope this helps.