Ask Search:
Liz TelschowLiz Telschow 

Text formula field - combine two picklists and conditionally insert text between the field values

I'm trying to build a text formula that combines values from two picklists, Curriculum and Program, and inserts text between the two values based on the value of Program.

A picklist value is always selected for Curriculum; however, Program sometimes has a meaningful value, sometimes has a value of "None", and is often times blank.

If Program has a meaningful value, then I want to combine Curriculum and Program and connect them with the word "for". Example: Swimming Lessons for Dogs

If there is no Program or Program is "None", then I only want to display Curriculum, and I don't want the word "for" to appear. (Yes: Swimming Lessons; No: Swimming Lessons for)

My formula is below. It works to combine the field values, but I can't get it to display "for" only when Program has a meaningful value.

I apologize if this is answered already, I had no idea how to go about searching for it.

Thank you!

TEXT(Training__r.Training_Curriculum__c) & " " &
IF(
ISNULL(TEXT(Training__r.Training_Program__c)) || ISPICKVAL(Training__r.Training_Program__c, "None") || ISPICKVAL(Training__r.Training_Program__c, "-"),
", ",
(" for " & (TEXT(Training__r.Training_Program__c))))
Best Answer chosen by Liz Telschow
Anel MalabananAnel Malabanan
Try this:
IF(OR
(ISBLANK(TEXT(Training__r.Training_Program__c)), 
ISPICKVAL(Training__r.Training_Program__c, "None"), 
ISPICKVAL(Training__r.Training_Program__c, "-")
),
TEXT(Training__r.Training_Curriculum__c)&" "&Text(Training__r.Training_Program__c),TEXT(Training__r.Training_Curriculum__c)&" for "&TEXT(Training__r.Training_Program__c))

 

All Answers

Anel MalabananAnel Malabanan
Hi Liz,

Try this:
 
IF(OR(ISBLANK(TEXT(Training__r.Training_Program__c)), ISPICKVAL(Training__r.Training_Program__c, "None"), ISPICKVAL(Training__r.Training_Program__c, "-")),TEXT(Training__r.Training_Curriculum__c),TEXT(Training__r.Training_Curriculum__c)&" for "&(TEXT(Training__r.Training_Program__c))

Hope it helps!
Liz TelschowLiz Telschow
Thanks, Anel. I have high hopes, but right now I'm getting: Error: Syntax error. Missing ')'

Any chance you can tell me where that missing ) goes?
Anel MalabananAnel Malabanan
Try this:
IF(OR
(ISBLANK(TEXT(Training__r.Training_Program__c)), 
ISPICKVAL(Training__r.Training_Program__c, "None"), 
ISPICKVAL(Training__r.Training_Program__c, "-")
),
TEXT(Training__r.Training_Curriculum__c)&" "&Text(Training__r.Training_Program__c),TEXT(Training__r.Training_Curriculum__c)&" for "&TEXT(Training__r.Training_Program__c))

 
This was selected as the best answer
Liz TelschowLiz Telschow
Amazing! Thank you so much, Anel!