Formula for showing flags based on a number field

Question

am fairly new to Salesforce and do not come from a technical background so struggling with Formulas. I am trying to achieve the following logic; If the NPG Triage Result Code is 5, 7, 9, 21, 99 then Yellow Flag, for any other value Red Flag, or if blank then blank.

IF(
    OR(
        NPG_Triage_Result_Code__c = 5,
        NPG_Triage_Result_Code__c = 7, 
        NPG_Triage_Result_Code__c = 9, 
        NPG_Triage_Result_Code__c = 21, 
        NPG_Triage_Result_Code__c = 99
    )
), 
IMAGE("/img/samples/flag_yellow.gif","Yellow"), 
IMAGE("/img/samples/flag_red.gif", "Red")
)

I am unsure how to show the different values that need the yellow flag more efficiently, and struggling to show the any other value = red flag and no value = blank part. I also keep running up against errors. The one I am getting for above is -

Error: Syntax error. Extra IMAGE

I have now tried different versions of the code

IF(
    ISBLANK(NPG_Triage_Result_Code__c), 
    "", 
    IF(
        CONTAINS("5:7:9:21:99", NPG_Triage_Result_Code__c),
        IMAGE("/img/samples/flag_yellow.gif","Yellow"),
        IF(
            CONTAINS("1:2:3:4:6:8:10:11:12:20:98:400:500", NPG_Triage_Result_Code__c),
            IMAGE("/img/samples/flag_red.gif", "Red") 
        )
    )
)

But am running across a new error -

Incorrect parameter type for function 'CONTAINS()'. Expected Text, received Number (Related field: Formula)

When trying the CASE function

CASE(
    NPG_Triage_Result_Code__c, 
    "5:7:9:21:99", IMAGE("/img/samples/flag_yellow.gif","Yellow"),
    "1:2:3:4:6:8:10:11:12:20:98:400:500", IMAGE("/img/samples/flag_red.gif", "Red")
)

I get this error -

Incorrect number of parameters for function 'CASE()'. Expected 4, received 5 (Related field: Formula)

Am getting a bit lost trying to find SF specific help for it in the documents so even if I could be pointed to the right reference docs that would be a massive help. Thank you!

Answer 1

tl;dr:

Don't try to be fancy. In most cases, you should just stick to the simplest thing that works.

So you should stick to the simple IF() approach of your first attempt (comparing to values one at a time), and nest it inside of another IF() so you can handle blank values.

long version

Your first formula is the closest (extra close-parenthesis aside).

In general, formulas (and code) can't compare multiple things at once. Most of the time, you simply just need to check for values one at a time. That's just how computers work.

There are some situations where using CONTAINS() could work, but

• the order of the arguments that you pass to that function matters
• e.g. CONTAINS("Parts", "Parts and Service") will return false, but reverse the order of the arguments CONTAINS("Parts and Service", "Parts") and it will return true
• it'll return true if any part of the second argument is contained in the first argument
• e.g. CONTAINS(NPG_Triage_Result_Code__c, "5:7:9:21:99") will result in true if NPG_Triage_Result_Code__c is either 1 or 2, not just if it's 21 (as you would hope)

In the end, the "most efficient" way to go about this is to fix your first example (and then extend it).

Getting rid of that extra parenthesis brings you to

IF(
    OR(
        NPG_Triage_Result_Code__c = 5,
        NPG_Triage_Result_Code__c = 7, 
        NPG_Triage_Result_Code__c = 9, 
        NPG_Triage_Result_Code__c = 21, 
        NPG_Triage_Result_Code__c = 99
    ),
    IMAGE("/img/samples/flag_yellow.gif","Yellow"), 
    IMAGE("/img/samples/flag_red.gif", "Red")
)

To extend that to handle blank values, you'd nest the above in another IF() function (which is one of the only ways we have to say "if thing X, else if thing Y, else Z").

As you have in your second example, you want to perform this blank check first.

IF(
    ISBLANK(NPG_Triage_Result_Code__c),
    "",
    IF(
        OR(
            NPG_Triage_Result_Code__c = 5,
            NPG_Triage_Result_Code__c = 7, 
            NPG_Triage_Result_Code__c = 9, 
            NPG_Triage_Result_Code__c = 21, 
            NPG_Triage_Result_Code__c = 99
        ),
        IMAGE("/img/samples/flag_yellow.gif","Yellow"), 
        IMAGE("/img/samples/flag_red.gif", "Red")
    )
)

The best explanation I can come up with for why you want to do things in that order is that each successive IF() is separating things into two groups.
It just makes more logical sense to me to check if a number is blank before checking to see if it's a specific number rather than the other way around.

You could use CASE(), but it'd end up being more typing. The syntax for CASE() is

CASE( Field_To_Test__c,
    "value 1", "result 1",
    "value 2", "result 2",
    ...
    "value n", "result n",
    "result if Field_To_Test__c matches none of the 'values'"
)

Applied to your scenario

CASE(NPG_Triage_Result_Code__c,
    5, IMAGE("/img/samples/flag_yellow.gif","Yellow"),
    7, IMAGE("/img/samples/flag_yellow.gif","Yellow"),
    9, IMAGE("/img/samples/flag_yellow.gif","Yellow"),
    21, IMAGE("/img/samples/flag_yellow.gif","Yellow"),
    99, IMAGE("/img/samples/flag_yellow.gif","Yellow"),
    IMAGE("/img/samples/flag_red.gif","Red")
)

and that doesn't handle blank values (you'd need an IF() to handle that).

The best official documentation for formula functions is the Formula Operators and Functions by context help page

Answer 2

You can use IF statements inside the IMAGE function to drastically improve the formula, as well as using the CASE function. Here's my rendition of this solution:

IF(NPG_Triage_Result_Code__c = NULL,
    NULL,
    IMAGE(
        IF(
            CASE(
                NPG_Triage_Result_Code__c, 
                5, 1, 7, 1, 9, 21, 99, 1, 0
            ) = 1,
            '/img/samples/flag_yellow.gif',
            '/img/samples/flag_red.gif',
        ),
        IF(
            CASE(
                NPG_Triage_Result_Code__c, 
                5, 1, 7, 1, 9, 21, 99, 1, 0
            ) = 1,
            'Yellow',
            'Red'
        )
    )
)

Here, we first say that if the field is null, we return no value (null). Otherwise, we return an IMAGE where we are checking if the values are one of 5, 7, 9, 21, or 99, where we return a 1 if so, or a 0 otherwise.

CASE(FieldName, check1, value1, check2, value2, ... defaultValue)

As you can see, we check FieldName against check1, check2, etc, until we find a match, and return that value. CASE(...) = 1 is a workaround for the fact that we can't just return TRUE/FALSE.