concatenate 5 different picklist fields values

Question

I want to concatenate 5 picklist fields values in a new custom field on Lead object. All are single pick text fields. The new field can be a formula (read-only) or text field.

Output format: Picklist1_Picklist2_Picklist3_Picklist4_Picklist5 condition : 1. underscore should skip if any value is null or blank. 2. For picklist 1 it should only add selected values (Eg: Pick values: a, b, c, d, e, f: it should only add to the formula when a or b or c is selected else it should be blank)

Can someone help with the formula or workflow for this requirement?

Answer 1

What you need is to know how to use ISBLANK and IF function in formula editor. What I have done is created three fields of datatype picklist. Then, first I have checked whether any of the picklists is blank or not using If condition.

   IF( ( ISBLANK(TEXT(sa_nket__Pick_1__c)) || ISBLANK(TEXT(sa_nket__Pick_2__c)) ||  ISBLANK(TEXT(sa_nket__Pick_3__c)), value if true, value if false )

then defined the value for true condition and false condition. Since you have another condition to check whether a particular value is selected in first picklist field, So you have to again check the condition as follows for both condition i.e value if true, value if false in first if condition:-

IF( ISPICKVAL(sa_nket__Pick_1__c , "Test 1") , TEXT( sa_nket__Pick_1__c ) , '') --> for value if true

IF( ISPICKVAL(sa_nket__Pick_1__c , "Test 1") , TEXT( sa_nket__Pick_1__c )+'_', '')  ---> if value if false

Then finally merged all according to your condition as follows:-

IF( 
    ( ISBLANK(TEXT(sa_nket__Pick_1__c)) || ISBLANK(TEXT(sa_nket__Pick_2__c)) ||  ISBLANK(TEXT(sa_nket__Pick_3__c)) ), 
    IF( ISPICKVAL(sa_nket__Pick_1__c , "Test 1") , TEXT( sa_nket__Pick_1__c ) , '') + TEXT( sa_nket__Pick_2__c ) + TEXT( sa_nket__Pick_3__c ), 
    IF( ISPICKVAL(sa_nket__Pick_1__c , "Test 1") , TEXT( sa_nket__Pick_1__c )+'_', '') ,  + '_' + TEXT( sa_nket__Pick_2__c ) + '_' + TEXT( sa_nket__Pick_3__c ) 
    )