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
Terry_0101Terry_0101 

Formula not showing Netherlands value

This formula works for the USA states.  But for Netherlands, it shows up as null.

IF(OR(ISBLANK(State),ISBLANK(Country)), "", 
IF(CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada", State ), "US - WEST", 
IF(CONTAINS("Netherlands:Norway", Country ), 
"EUR",
"")))


The IF(OR( should look at IF statement as separate?
James LoghryJames Loghry
What is the exact value of Country for the case of Netherlands?

Note that the CONTAINS formula is case sensitive, so you'll get different results for "NETHERLANDS" versus "Netherlands".  For instance, if it's an issue of case sensitivity, you could do the following:
  
IF(CONTAINS("netherlands:norway",LOWER(Country))
Terry_0101Terry_0101
The Netherlands value is coming from the standard billing address field on Leads.....state and country picklist is enabled.

I tired the LOWER and not working.
Terry_0101Terry_0101
The value from the picklist is Netherlands
Brian FordBrian Ford
I tried this as a formula field on the Account object and it worked.  
IF( 
   OR(ISBLANK(BillingState), ISBLANK(BillingCountry)),
   IF(
      CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada", BillingState ), 
      "US - WEST", 
      IF(
         CONTAINS("Netherlands:Norway", BillingCountry ), 
         "EUR",
         ""
      )
   )
)

Potential Problems:
  • You're using State and Country instead of BillingState, BillingCountry. Or you're using custom fields and omitting the '__c'
  • There's a phantom space after Netherlands in your countries picklist
Terry_0101Terry_0101
On the Leads, the field is called "Address" where I select Netherlands on the state and country picklist popup.  On the Leads object, there are fields such as BillingCountry, Country, Address.  Neither one works.
Brian FordBrian Ford
Tried it on the lead object and it worked again. I think your problem is with the inital OR statement. If you're populating country with Netherlands but state is blank, the IF statement will return null. Try removing the OR statement.
  
IF(
   CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada", State), 
   "US - WEST",
   IF(
      CONTAINS("Netherlands:Norway", Country ), 
      "EUR",
      ""
   )
)

 
Terry_0101Terry_0101
By removing the OR, the result is US - West, not EUR.  Using the Lead object, the standard address fields (state and Country)

IF(OR(ISBLANK(State),ISBLANK(Country)), "", 
IF(CONTAINS("Alaska:Washington:California:Oregon:Hawaii:Nevada", State ), "US - WEST", 
IF(CONTAINS("Netherlands:Norway", Country ), 
"EUR",
"")))
 
Brian FordBrian Ford
For your existing formula, witht the OR statement, here are some scenarios and results:

State=Alaska, Country=' ' returns null
State=' ', Country=Netherlands returns null
State=Alaska, Country=Unites States returns US-West
State=Alaska, Country=Netherlands returns US-West

If you remove the OR statement the same scenarios will return the following:

State=Alaska, Country=' ' returns US-West
State=' ', Country=Netherlands returns EUR
State=Alaska, Country=Unites States returns US-West
State=Alaska, Country=Netherlands returns US-West

Let me know if these scenarios don't match your requirements, or if there's something missing.
Terry_0101Terry_0101

I see, so there is no way to get Netherlands to only show EUR?  And skip US West?
Terry_0101Terry_0101
I was able to resolve this with having two formula fields, one for state and other for country.  It works.  Thank you for helping.