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
MCLMCL 

How to create a formula - language and gender

Hi there,

Can someone advise me on how to create a forluma with languages and genders please. I have created picklists of languages, German and English, and genders, Female and Male. I would like to create a new formula field for the letter salutations. For instance, If a contact is a female and speaks German, her salutation will be Frau. I am new to Salesforce. Any advice will be much appreciated!!

Thank you,
Best Answer chosen by MCL
Parker EdelmannParker Edelmann
I see the error. the "blank"s had significance in the formula. Apparently "blank" doesn't work, so I replaced it with "null". I tested the syntax in my DE this time because I did so terribly earlier. Try this: 
IF(
ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", "Herr","English", "Mr." , null),
IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau","English", "Mrs.", null), null))

Again, I appologize for my bad formula building skills. I've gotten a little rusty. If you're wondering what ISPICKVAL() does, it takes a field of picklist type in the first argument and text in the second. It creates a text representation of the Picklist field's value and compares it with the text you entered in the second argument. If they match, the function returns true, if not, it returns false. I should have incluced the other part of the documentation that I referenced: https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_i_z.htm&language=en_US if you scroll down a little, you'll find information about ISPICKVAL.

All Answers

Parker EdelmannParker Edelmann
There is a Standard Contact field called Salutation. Unfortunately, this is a standard picklist field, and besides the values, it cannot be customized because it is standard.

Seeing as how you want a custom formula field, here's what I'd probably do: 
IF(
   ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", /*Pardon me, I'm unable to remember the correct Salutation for a German man, but that would go here*/,
                                                 "English", "Mr.", blank),
    IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau",
                                                         "English", "Mrs.", blank)
       )
    )
If you're not familiar with any of the functions that I used, check this help document; it gives a much better explaination than I could give and includes all functions available: https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US

Let me know if this helps.

Thanks,
Parker
Parker EdelmannParker Edelmann
I'm sorry, I just noticed an error in my formula. Let's try that again. 
IF(
   ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", /*Pardon me, I'm unable to remember the correct Salutation for a German man, but that would go here*/,
                                                "English", "Mr.", blank),
   IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau",
                                                         "English", "Mrs.", blank),
    blank)
    )
Parker EdelmannParker Edelmann
Today's not my best day. I just noticed another error. 
IF(
   ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", /*Pardon me, I'm unable to remember the correct Salutation for a German man, but that would go here*/,
                                                "English", "Mr.", blank),
   IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau","English", "Mrs.", blank),blank),
   blank)
This should work. Sorry for the inconvenience.
 
MCLMCL
Thank you so much for your advice!! I didn't know how to use ISPICKVAL. I copied your formula, but I got an error message saying "Error: Syntax error. Found ')' . It highlighs ) after "Mr". I tried to remove it, but it gave me another error mesage. Would you mind having a look again?

IF(
ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", "Herr","English", "Mr." , )
IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau","English", "Mrs.")
), ) ,)

Thank you!
Parker EdelmannParker Edelmann
I see the error. the "blank"s had significance in the formula. Apparently "blank" doesn't work, so I replaced it with "null". I tested the syntax in my DE this time because I did so terribly earlier. Try this: 
IF(
ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", "Herr","English", "Mr." , null),
IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau","English", "Mrs.", null), null))

Again, I appologize for my bad formula building skills. I've gotten a little rusty. If you're wondering what ISPICKVAL() does, it takes a field of picklist type in the first argument and text in the second. It creates a text representation of the Picklist field's value and compares it with the text you entered in the second argument. If they match, the function returns true, if not, it returns false. I should have incluced the other part of the documentation that I referenced: https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_i_z.htm&language=en_US if you scroll down a little, you'll find information about ISPICKVAL.
This was selected as the best answer
MCLMCL
It works perfectly! Thank you so much once again!! And also the explanation of ISPICKVAL is very helpful. No wonder my formula didn't work at all. I am so happy that I've finally got a solution:):):) I really appreciate it.
 
Parker EdelmannParker Edelmann
I'm glad I could help! If you want to add another language, just add the value to the Language Picklist and the value to the formula. For example, you want to add Spanish, simply update the formula to this: 
IF(
ISPICKVAL(Gender__c,"Male"),CASE(Language__c, "German", "Herr","English", "Mr." , "Spanish", "Señor" null),
IF(ISPICKVAL(Gender__c, "Female"), CASE(Language__c, "German", "Frau","English", "Mrs.", "Spanish", "Sra." null), null))
Not sure why bing Translate gave me "Sra." as Spanish for "Mrs", but you get the point; the formula is easily expandable.

Again, I'm more than happy to help!

Regards,
Parker
MCLMCL
Ok, I got it! It is a useful formula. I appreciate all your help and advice!!!