0

I'm trying to create a formula field that will tell me the region which a country is part of. Problem is that there are too many. The code below is just for European Union and Europe and I'm already getting an error. How can I simplify this IF statement?

IF(CONTAINS( Country , "Austria"), "European Union",
IF(CONTAINS( Country , "Belgium"), "European Union",
IF(CONTAINS( Country , "Bulgaria"), "European Union",
IF(CONTAINS( Country , "Cyprus"), "European Union",
IF(CONTAINS( Country , "Czech Republic"), "European Union",
IF(CONTAINS( Country , "Denmark"), "European Union",
IF(CONTAINS( Country , "Estonia"), "European Union",
IF(CONTAINS( Country , "Finland"), "European Union",
IF(CONTAINS( Country , "France"), "European Union",
IF(CONTAINS( Country , "Germany"), "European Union",
IF(CONTAINS( Country , "Greece"), "European Union",
IF(CONTAINS( Country , "Hungary"), "European Union",
IF(CONTAINS( Country , "Ireland"), "European Union",
IF(CONTAINS( Country , "Italy"), "European Union",
IF(CONTAINS( Country , "Latvia"), "European Union",
IF(CONTAINS( Country , "Lithuania"), "European Union",
IF(CONTAINS( Country , "Luxembourg"), "European Union",
IF(CONTAINS( Country , "Malta"), "European Union",
IF(CONTAINS( Country , "Netherlands"), "European Union",
IF(CONTAINS( Country , "Poland"), "European Union",
IF(CONTAINS( Country , "Portugal"), "European Union",
IF(CONTAINS( Country , "Romania"), "European Union",
IF(CONTAINS( Country , "Slovakia"), "European Union",
IF(CONTAINS( Country , "Slovenia"), "European Union",
IF(CONTAINS( Country , "Spain"), "European Union",
IF(CONTAINS( Country , "Sweden"), "European Union",
IF(CONTAINS( Country , "United Kingdom"), "European Union",
IF(CONTAINS( Country , "Albania"), "Europe",
IF(CONTAINS( Country , "Andorra"), "Europe",
IF(CONTAINS( Country , "Belarus"), "Europe",
IF(CONTAINS( Country , "Bosnia"), "Europe",
IF(CONTAINS( Country , "Croatia"), "Europe",
IF(CONTAINS( Country , "European Union"), "Europe",
IF(CONTAINS( Country , "Faroe Islands"), "Europe",
IF(CONTAINS( Country , "Gibraltar"), "Europe",
IF(CONTAINS( Country , "Guernsey"), "Europe",
IF(CONTAINS( Country , "Alderney"), "Europe",
IF(CONTAINS( Country , "Iceland"), "Europe",
IF(CONTAINS( Country , "Jersey"), "Europe",
IF(CONTAINS( Country , "Kosovo"), "Europe",
IF(CONTAINS( Country , "Liechtenstein"), "Europe",
IF(CONTAINS( Country , "Macedonia"), "Europe",
IF(CONTAINS( Country , "Island of Man"), "Europe",
IF(CONTAINS( Country , "Moldova"), "Europe",
IF(CONTAINS( Country , "Monaco"), "Europe",
IF(CONTAINS( Country , "Montenegro"), "Europe",
IF(CONTAINS( Country , "Norway"), "Europe",
IF(CONTAINS( Country , "Russia"), "Europe",
IF(CONTAINS( Country , "San Marino"), "Europe",
IF(CONTAINS( Country , "Serbia"), "Europe",
IF(CONTAINS( Country , "Svalbard"), "Europe",
IF(CONTAINS( Country , "Jan Mayen Islands"), "Europe",
IF(CONTAINS( Country , "Switzerland"), "Europe",
IF(CONTAINS( Country , "Turkey"), "Europe",
IF(CONTAINS( Country , "Ukraine"), "Europe",
IF(CONTAINS( Country , "Vatican City"), "Europe",
null))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Error:

Error: Compiled formula is too big to execute (5,892 characters). Maximum size is 5,000 characters

3
  • You can try a CASE statement but if you plan to do this for 170+ countries, you're going to run out of space anyway Oct 19, 2018 at 20:07
  • 2
    It looks like you're using a standard field so, since a lookup is not practical there, maybe you can explore using a flow, WorkflowRules or even a trigger to populate a regular field, and not a formula. Oct 19, 2018 at 20:07
  • 2
    Why are you using CONTAINS? Do you actually expect those values to be a substring or will it be the entire value?
    – Adrian Larson
    Oct 19, 2018 at 20:34

2 Answers 2

2

It seems like you are using CONTAINS where you could really use an equality check. If that is the case, you should absolutely use the CASE function here:

CASE(
    Country,
    "Austria", "European Union",
    "Belgium", "European Union",
    ...
    "Albania", "Europe",
    "Andorra", "Europe",
    ...
    "Rome", "Vatican City",
    null
)

Every time you reference another field, it eats up quite a bit of your calculated size. In this way, you reduce the number of field references to one, and also collapse everything into just one function call.

This formula is about as efficient as you can get it. If adding in all the countries you wish to support still overflows the maximum allowable size, you will indeed need to move your logic into a calculated mechanism (Apex Trigger, Workflow Rule, etc.).

0

You are hitting the Compile Time limitations on Formula Field here.

Compile size limit—Formula fields can’t exceed 5,000 bytes when compiled. The compile size is the size of the formula (in bytes) including all of the fields, values, and formulas it references. There is no direct correlation between the compile size and the character limit. Some functions, such as TEXT, DATEVALUE, DATETIMEVALUE, and DATE significantly increase the compile size.

Start from Tips for Reducing Formula Size, and see if you can implement recommendations out in that document to bring down the size of your formula.


However, this solution may not be scalable as you are already past the limit and let's say even if you fine tune it, you will be still somewhere close to that limit.

My recommendation is to go through a custom route. You can easily utilize a trigger here to verify if the incoming field value belongs to any of those country, then just populate the value accordingly. You can either use the existing country picklist values, or create a custom settings, or even just create a list in your apex class to store such countries, and validate the incoming value with that list.

In summary, if you cannot reduce the formula field, you will anyways need to think for alternate options here.

You must log in to answer this question.