+ Start a Discussion
Zane Prater 15Zane Prater 15 

How to evaluate multiple values in a contains function via declarative formula

I am trying check if the suppliedemail field contains any of these domains but is not working.
If(CONTAINS("lincare.com:ndc-inc.com:preferredhomecare.com", SuppliedEmail),mod(value(CaseNumber),3)+1,0)
Best Answer chosen by Zane Prater 15
Andrew GAndrew G
Hi Zane, 

CONTAINS (text, compare_text)
CONTAINS will return TRUE if "compare_text" is found in "text" and FALSE if not.

The issue with your CONTAINS formula is that the SuppliedEmail address will be "someone@lincare.com".  As a "string", that does not appear in your List of Domains.  You will need to strip the person component from the SuppliedEmail Address.

So something like:
IF(
  CONTAINS(
    "lincare.com:ndc-inc.com:preferredhomecare.com", 
    SUBSTITUTE(SuppliedEmail, LEFT(SuppliedEmail, FIND("@", SuppliedEmail)), NULL)
  ),
  mod(value(CaseNumber),3)+1,
  0
)

As noted by Mahajarn, you could switch the format to multiple contains 


Formula is untested, but the logic should work

regards
Andrew
 

All Answers

Maharajan CMaharajan C
Hi Zane,

Otherwise use like below:
 
If(
OR
(CONTAINS(Email__c, "lincare.com") , 
CONTAINS(Email__c, "ndc-inc.com"),
CONTAINS(Email__c, "preferredhomecare.com")),
mod(value(CaseNumber),3)+1,
0)

Thanks,
Maharajan.C
Zane Prater 15Zane Prater 15
Thanks Maharajan, but I am trying to avoid an OR statement because there are over 200 domains that need to be evaluated and the character limit would exceed for the formula if I go that route.  
Andrew GAndrew G
Hi Zane, 

CONTAINS (text, compare_text)
CONTAINS will return TRUE if "compare_text" is found in "text" and FALSE if not.

The issue with your CONTAINS formula is that the SuppliedEmail address will be "someone@lincare.com".  As a "string", that does not appear in your List of Domains.  You will need to strip the person component from the SuppliedEmail Address.

So something like:
IF(
  CONTAINS(
    "lincare.com:ndc-inc.com:preferredhomecare.com", 
    SUBSTITUTE(SuppliedEmail, LEFT(SuppliedEmail, FIND("@", SuppliedEmail)), NULL)
  ),
  mod(value(CaseNumber),3)+1,
  0
)

As noted by Mahajarn, you could switch the format to multiple contains 


Formula is untested, but the logic should work

regards
Andrew
 
This was selected as the best answer
Zane Prater 15Zane Prater 15
Thanks Andrew, worked perfect!