function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
StefanStefan 

Concatenate Salutation in a custom formula

I am trying to build a custom field to be used in mail merge and am having problems with Salutation.
 
If Salutation field is populated I would like a space following the Salutation. If Salutation is empty then no space.
 
So why does this work:
 
IF(ISPICKVAL( Salutation , ""), "", "Mr,Mrs etc" & " " )
& FirstName & " "
&LastName
 
Mr,Mrs etc George BushTony Blair

But this:

IF(ISPICKVAL( Salutation , ""), "", Salutation & " " )
& FirstName & " "
&LastName

results in: Error: Field Salutation is a picklist field. Use it with an ISPICKVAL() or CASE() function instead.

StefanStefan
I can't believe how inflexible Salesforce is sometimes! Surely there must be a way to enter something like: Salutation & " " &  FirstName & " " & LastName in a custom field.
JShaffJShaff
Did you ever come up with the proper formula to concatenate 'Salutation' & 'First Name' & 'Last Name', 'Suffix'?  I'm running into the same situation as you described here and it seems odd that this wouldn't be a basic formula text concatentation example. 
NPMNPM

DIrectly mixing Picklist values and Text fields is a problem.  You may need to consider converting the Picklist value as part of the formula or maybe even a new text formula field (which you then could use in the field you want to build using concatenation) using something like this:

IF( ISPICKVAL( Salutation , "Mr") , "Mr ", "")

Since there obviously are several values of salutation using a CASE statement might be better (and I have had more luck using CASE for picklist values than ISPICKVAL).

CASE( Salutation ,

"Mr", "Mr ",
"Mrs", "Mrs ",
"etc.", "etc. ", "")

 

 
Elizabeth SmithElizabeth Smith
For others who are struggling with what should be a simple concatenation on Salutation, First, Last - here's what finally did the trick:  Text(Salutation) & " " & FirstName & " " & LastName