3

I found a workaround to do this, however, there are a few minor tweaks I need to determine in my formula structure.

I created this formula, which concatenates and populates data based on if this field has valid

IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " Gators ")& IF(ISBLANK(Paracords__c),"", ", "&TEXT(Paracords__c) & " Paracords ")& IF(ISBLANK(Cool_Ties__c),"", ", "&TEXT(Cool_Ties__c) & " Cool Ties ")& IF(ISBLANK(Phone_Cards__c),"", ", "&TEXT(Phone_Cards__c) & " Phone Cards ")& IF(ISBLANK(Gift_Cards__c),"", ", "&TEXT(Gift_Cards__c) & " Gift Cards worth &TEXT(Gift_Cards_Value__c) ")& IF(ISBLANK(Total_Letters__c),"", ", "&TEXT(Total_Letters__c)& " Letters ")& 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(Recyclables__c),"", ", "&TEXT(Recyclables__c)& " Recyclable Items ")& IF(ISBLANK(Other_Special_Items__c),"", ", "&TEXT(Other_Special_Items__c)& " Other Items ")

Ultimately, here's the result I get

13 Gators , 16 Paracords , 14 Cool Ties , 1 Phone Cards , 3 Gift Cards worth &TEXT(Gift_Cards_Value__c) , 42 Letters , 5 Girl Scout Cookies , 7 Food And Snack Items , 8 Toys, Animals And Bears , 2 Beanie Babies , 15 Hats , 17 Scarves , 19 Video Games , 20 CD's & DVD's , 21 Lbs of Candy , 23 Recyclable Items , 22 Other Items

So basically, while the formula is near perfect, I am looking to determine a way to omit the first comma in the formula (for instance If I provide no value in Gators, it reads , 16 Paracords...), and if possible include an and preceding the last value (i.e. and 22 Other Items) Ideally, I vision something that looks like this....

"16 Paracords and 22 Other Items"

This as a result concatenates the data into a digestible, readable result, which can be included in printed documentation, mail-merge letters and the like. If it isn't possible, no big deal.

Additionally, I am also determining a way to populate the value "Gift Cards Value" within the text so it reads i.e. "1 Gift Card worth $1.00).

Thanks very much.

  • I got the solution to work across the board. Many thanks! – Medhanie Habte Jun 5 '15 at 7:21
3

Yes it is possible, you would need to combine the following types of functions:

  1. IF(logical_test, value_if_true, value_if_false) and replace logical_test with the expression you want evaluated; replace value_if_true with the value you want returned if the expression is true; replace value_if_false with the value you want returned if the expression is false.

  2. The ISBLANK
    ISBLANK(expression) and replace expression with the expression you want evaluated.

  3. & (Concatenate)

    Description: Connects two or more strings.

    Use: string1&string2 and replace each string with merge fields, expressions, or other values.

    Example: "Expense-" & Trip_Name__c & "-" & ExpenseNum__c

    This formula displays the text “Expense-” followed by trip name and the expense number. This is a text formula field that uses an expense number custom field.

  • I found a workaround to do this, however, there are a few minor tweaks I need to determine in my formula structure. – Medhanie Habte Jun 4 '15 at 23:34
0

I found a workaround to do this, however, there are a few minor tweaks I need to determine in my formula structure.

I created this formula, which concatenates and populates data based on if this field has valid

IF(ISBLANK(Gators__c),""," "&TEXT(Gators__c) & " Gators ")& IF(ISBLANK(Paracords__c),"", ", "&TEXT(Paracords__c) & " Paracords ")& IF(ISBLANK(Cool_Ties__c),"", ", "&TEXT(Cool_Ties__c) & " Cool Ties ")& IF(ISBLANK(Phone_Cards__c),"", ", "&TEXT(Phone_Cards__c) & " Phone Cards ")& IF(ISBLANK(Gift_Cards__c),"", ", "&TEXT(Gift_Cards__c) & " Gift Cards worth &TEXT(Gift_Cards_Value__c) ")& IF(ISBLANK(Total_Letters__c),"", ", "&TEXT(Total_Letters__c)& " Letters ")& 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(Recyclables__c),"", ", "&TEXT(Recyclables__c)& " Recyclable Items ")& IF(ISBLANK(Other_Special_Items__c),"", ", "&TEXT(Other_Special_Items__c)& " Other Items ")

Ultimately, here's the result I get

13 Gators , 16 Paracords , 14 Cool Ties , 1 Phone Cards , 3 Gift Cards worth &TEXT(Gift_Cards_Value__c) , 42 Letters , 5 Girl Scout Cookies , 7 Food And Snack Items , 8 Toys, Animals And Bears , 2 Beanie Babies , 15 Hats , 17 Scarves , 19 Video Games , 20 CD's & DVD's , 21 Lbs of Candy , 23 Recyclable Items , 22 Other Items

So basically, while the formula is near perfect, I am looking to determine a way to omit the first comma in the formula (for instance If I provide no value in Gators, it reads , 16 Paracords...), and if possible include an and preceding the last value (i.e. and 22 Other Items) Ideally, I vision something that looks like this....

"16 Paracords and 22 Other Items"

This as a result concatenates the data into a digestible, readable result, which can be included in printed documentation, mail-merge letters and the like. If it isn't possible, no big deal.

Additionally, I am also determining a way to populate the value "Gift Cards Value" within the text so it reads i.e. "1 Gift Card worth $1.00).

Thanks very much.

  • Can you edit your original question with an update and delete this answer? I would move the comma inside your if statement so like " Gators, " then your comma will always be at the end so you can do left function wrapped around the entire thing with length function - 2 (one for the comma and the other for the space. – Jenny B Jun 5 '15 at 1:09

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.