1

I am looking for a solution how to express a formula for an user-defined field with the following description:

the formula should show the corresponding letter salutation dependent on the picklist values in fields Salutation and Language, for example:

  • if you choose in the picklist Salutation "Mr." and the contact language is "English", the user-defined field will show for example the expression "Dear Mr. John Benson" for John Benson

  • if you choose in the picklist Salutation "Mrs." and the contact language is "English", the user-defined field will show for example the expression "Dear Mrs. Diana Fox" for Diana Fox

  • if you choose in the picklist Salutation "Mr." and the contact language is "English", the user-defined field will show for example the expression "Sehr geehrter Herr X Y" for X Y

I wrote this formula, which unfortunately is not working right:

if((ISPICKVAL(Sprache__c ,"German") && (ISPICKVAL(Salutation ,"Mr."))),"Sehr geehrter Herr" & FirstName & LastName) OR if((ISPICKVAL(Sprache__c ,"German") && (ISPICKVAL(Salutation ,"Mrs."))),"Sehr geehrte Frau" & FirstName & LastName) OR if((ISPICKVAL(Sprache__c ,"English") && (ISPICKVAL(Salutation ,"Mr."))),"Dear Mr." & FirstName & LastName) OR if((ISPICKVAL(Sprache__c ,"English") && (ISPICKVAL(Salutation ,"Mrs."))),"Dea r Mrs." & FirstName & LastName)

The syntax error says "extra if"

Could somebody give me a tip, how can I correct this error? I would appreciate....

Improve this question
1
  • first rule in debugging complex formulas - use the Enhanced Formula Editor Chrome extension: jessealtman.com/2013/09/…. Second rule is to use multiple lines and indenting to decompose your formula into something readable
    – cropredy
    Jul 5, 2014 at 21:30

1 Answer 1

Reset to default
1

Unfortunately I have found a lot of errors in your formula. This formula is also very hard to read, hence I propose to use following one that I wrote:

IF (
ISPICKVAL(Sprache__c , "German"),
CASE(TEXT(Salutation), "Mr.", "Sehr geehrter Herr ", "Mrs.", "Sehr geehrte Frau ", ""),
IF (ISPICKVAL(Sprache__c, "English"),
    CASE(TEXT(Salutation), "Mr.", "Dear Mr. ", "Mrs.", "Dear Mrs. ", ""),
    ""
)
)
& FirstName & " " & LastName

First difference is that part of text that is the same for all options i moved on the end (first and last name). I also use "CASE" instead of "IF", because it is easier to read and maintenance. Here I had to use TEXT formula to convert picklist value.

Now two additional hints for you:

1) You shouldn't use so many brackets. In IF formula you have just three parameters and compiler know that when you put comma, next parameter is started. It means that instead of this:

if((ISPICKVAL(Sprache__c ,"German") && (ISPICKVAL(Salutation ,"Mr."))), [...]

you can write this:

if(ISPICKVAL(Sprache__c ,"German") && ISPICKVAL(Salutation, "Mr."),

2) If you want write something like if-else you have to use IF formula inside IF formula. So, this:

if((ISPICKVAL(Sprache__c ,"German") && (ISPICKVAL(Salutation ,"Mr."))),"Sehr geehrter Herr" & FirstName & LastName) OR if((ISPICKVAL(Sprache__c ,"German") && (ISPICKVAL(Salutation ,"Mrs."))) [...]

doesn't work. You should write something like this:

if(ISPICKVAL(Sprache__c ,"German") && ISPICKVAL(Salutation ,"Mr."), "Sehr geehrter Herr" & FirstName & LastName, if(ISPICKVAL(Sprache__c ,"German") && (ISPICKVAL(Salutation ,"Mrs."), [...] ) )

It is that, beacuse IF formula works in this way:

IF ( *condition*, *procedure when condition is met*, *otherwise* )
Improve this answer

You must log in to answer this question.