0

I have a problem with a formula for a custom field. I've written the formula that works partially but I want to make some tweaks and I dont know how to do it. I have 2 text fields State HQ and Country HQ. State HQ is used for AMER region while Country HQ is used for EMEA region. I have a working formula that only looks at State HQ field for both AMER and EMEA region.

I need some tweaks for formula that checks If state HQ is blank then it should look at Country HQ text and assign the proper subregion. But if State HQ is not blank and contains text then it should assign subregion based on that field.

Thank you!

Improve this question

1 Answer 1

Reset to default
1

If you are sure that State HQ is used for AMER region while Country HQ is used for EMEA region, you could replace State HQ by Country HQ for those regions in the formula field.

But if you want to make it more flexible, i.e. depending on State_HQ being filled or not, then there are other solutions indeed.

You could e.g. replace State_HQ__c by BLANKVALUE(State_HQ__c, Country_HQ__c) everywhere you use it, and remove the one at the beginning where you check for ISBLANK. BLANKVALUE determines if an expression has a value and returns a substitute expression if it doesn’t. https://help.salesforce.com/s/articleView?id=sf.customize_functions_blankvalue.htm&type=5

You might hit formule field limits, though, because it's quite a large formula already. If that happens, you might need to explore other options.

Example: instead of

IF(ISBLANK(State_HQ__c), "",
IF(CONTAINS("Nunavut:Manitoba:Ontario:Quebec:Québec:New Brunswick:Nova Scotia:Newfoundland and Labrador",  State_HQ__c ), "AMER - Canada East",
IF(CONTAINS("California:CA",  State_HQ__c ), "AMER - California",

do this:

IF(CONTAINS("Nunavut:Manitoba:Ontario:Quebec:Québec:New Brunswick:Nova Scotia:Newfoundland and Labrador",  BLANKVALUE(State_HQ__c, Country_HQ__c)), "AMER - Canada East",
IF(CONTAINS("California:CA",  BLANKVALUE(State_HQ__c, Country_HQ__c)), "AMER - California",

i.e.

  • remove the first line (and don't forget to also remove one closing parenthesis at the end of the formula)
  • replace every other occurence of State_HQ__c by BLANKVALUE(State_HQ__c, Country_HQ__c)
Improve this answer
2
  • Hey, thank you for your solution! Right now State HQ is only for AMER and Country HQ is for EMEA. The problem with your solution is when I change State HQ for Country HQ in the EMEA subregions, the formula will not automatically fill subregion because State HQ is blank. When I fill both State HQ and Country HQ then the formula works and subregion is detected. I think its because of first part of the formula where it checks if State HQ is blank or not. Can you help me tweak this formula so it gives me a subregion if State HQ is blank and Country HQ contains data? Thank you
    – Henrik Irving
    Commented Dec 2, 2022 at 11:54
  • I think I described that in my answer, but I've added an example now to make that clearer.
    – Guy Clairbois
    Commented Dec 3, 2022 at 14:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .