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
ManuelForceManuelForce 

? Combine AND with OR in Formula ?

Dear fellow members of the "Force",

 

I have trouble finding a better (more compressed!) way to build the following formula:

 

A field should check whether one of several countries is chosen ("OR"), then check whether

sex is "male" or "female", then generate a salutation text.

 

I came up with:

 

IF(
AND(
ISPICKVAL( Country_picklist__c, "Germany"),
ISPICKVAL (Sex__c, "M")), "Sehr geehrter Herr " + Title + " " + LastName ,
IF(
AND(
ISPICKVAL(Country_picklist__c, "Germany"),
ISPICKVAL (Sex__c, "F")), "Sehr geehrte Frau " + Title + " " + LastName,
null
))

 

I have no clue so far how to include the "OR" component for countries, which

would save me the increasingly long formula.

 

Helpful input will be VERY MUCH APPRECIATED !!!

Thanks in advance !

 

manuelforce

PilkoTechPilkoTech

Not completely sure if this will work, but according to the Help, you can use CASE with an ISPICKLIST criteria, so long as it evaluates to a numeric result. That, and I've nested an IF with the Gender and moved as much of the text out of the conditional statements to cut down on length wherever possible. I also added an "else_result" at the end to deal with undefined conditions.

 

Also, this isn't Javascript: "+" only works with numbers. You need an "&" here to concatenate the string.

 

 

CASE(1,
    IF (ISPICKVAL(Country_picklist__c, "Germany"), 1, 0),  
       "Sehr geehrte" & IF(ISPICKVAL(Sex__c, "M"),  
           "r Herr ", 
           " Frau "),
    "Dear M" & IF(ISPICKVAL(Sex__c, "M"),  "r. ", " s. ") 
)
& Title & " " & LastName

 

Good luck.

 
SwarnasankhaSwarnasankha

I have used the TEXT() function instead of ISPICKVAL(). In the example given below, I am using nested for loops for determining the Country and then proceed to check the Gender; I have also used an OR statement to show you the syntax on how to use the same.

 

 

IF
(
TEXT(Country__c)="Germany", IF(TEXT(Sex__c)="M", "Sehr geehrter Herr ", "Sehr geehrte Frau "),
IF
(
OR(TEXT(Country__c)="Armenia", TEXT(Country__c)="Russia"), IF(TEXT(Sex__c)="M", "Уважаемый г-н ", "Уважаемая госпожа "),
IF(TEXT(Sex__c)="M", "Dear Mr. ", "Dear Ms. ")
)
)+ Title + " " + LastName

 

IF

(

    TEXT(Country__c)="Germany", IF(TEXT(Sex__c)="M", "Sehr geehrter Herr ", "Sehr geehrte Frau "),

    IF

    (

       OR(TEXT(Country__c)="Armenia", TEXT(Country__c)="Russia"), IF(TEXT(Sex__c)="M", "RusMr. ", "RusMs. "),

       IF(TEXT(Sex__c)="M", "Dear Mr. ", "Dear Ms. ")

     )

) + Title + " " + LastName

 

Hope this helps - feel free to ask if you need any help or clarifications.