Ask Search:
Amanda BaileyAmanda Bailey 

Formula with multiple images not working

I'm working on a formula that displays a different flag (green, yellow and red) based on specific fields being completely blank, some populated or all populated. I tried creating the formula with IF(), CASE(), AND() but nothing seems to be working it just keeps throwing errors. When I do get it to not throw errors it just doesn't display anything... (changing field names because they are really specific)

IF(( Field_A__c  <> NULL) && ( Field_B__c <> NULL) && ( Field_C__c <> NULL) && ( Field_D__c <> NULL) && ( Field_E__c <> NULL) && ( Field_F__c <> NULL) && ( Field_G__c <> NULL) && ( Field_H__c <> NULL) && ( Field_I__c <> NULL) && ( Field_J__c <> NULL) && ( Field_K__c <> NULL), IMAGE("https://houseloan--training--c.cs63.content.force.com/servlet/servlet.ImageServer?id=0156C000000LhoC&oid=00D6C00000092uZ&lastMod=1568151767000 (https://houseloan--training--c.cs63.content.force.com/servlet/servlet.ImageServer?id=0156C000000LhoC&oid=00D6C00000092uZ&lastMod=1568151767000" style="color:#954f72; text-decoration:underline)", "Green"), 

IF (( Field_A__c  <> NULL) || ( Field_B__c <> NULL) || ( Field_C__c <> NULL) || ( Field_D__c <> NULL) || ( Field_E__c <> NULL) || ( Field_F__c <> NULL) || ( Field_G__c <> NULL) || ( Field_H__c <> NULL) || ( Field_I__c <> NULL) || ( Field_J__c <> NULL) || ( Field_K__c <> NULL), IMAGE("https://houseloan--training--c.cs63.content.force.com/servlet/servlet.ImageServer?id=0156C000000Lhqm&oid=00D6C00000092uZ&lastMod=1568152306000 (https://houseloan--training--c.cs63.content.force.com/servlet/servlet.ImageServer?id=0156C000000Lhqm&oid=00D6C00000092uZ&lastMod=1568152306000" style="color:#954f72; text-decoration:underline)", "Yellow"), 

IF (( Field_A__c  = NULL) && ( Field_B__c = NULL) && ( Field_C__c = NULL) && ( Field_D__c = NULL) && ( Field_E__c = NULL) && ( Field_F__c = NULL) && ( Field_G__c = NULL) && ( Field_H__c = NULL) && ( Field_I__c = NULL) && ( Field_J__c = NULL) && ( Field_K__c = NULL), IMAGE("https://houseloan--training--c.cs63.content.force.com/servlet/servlet.ImageServer?id=0156C000000LhoH&oid=00D6C00000092uZ&lastMod=1568150696000 (https://houseloan--training--c.cs63.content.force.com/servlet/servlet.ImageServer?id=0156C000000LhoH&oid=00D6C00000092uZ&lastMod=1568150696000" style="color:#954f72; text-decoration:underline)", "Red"), NULL)))

Any help is appreciated. Thanks!
Best Answer chosen by Amanda Bailey
Deepak AnandDeepak Anand
Try this = 
IMAGE(
    CASE(
        IF(NOT(ISBLANK(Field_A__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_B__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_C__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_D__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_E__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_F__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_G__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_H__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_I__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_J__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_K__c)), 1, 0),
        
        11, "/servlet/servlet.ImageServer?id=0156C000000LhoC&oid=00D6C00000092uZ&lastMod=1568151767000",
        
        0,  "/servlet/servlet.ImageServer?id=0156C000000LhoH&oid=00D6C00000092uZ&lastMod=1568150696000",
        
        "/servlet/servlet.ImageServer?id=0156C000000Lhqm&oid=00D6C00000092uZ&lastMod=1568152306000"
    ),
    "Status"
)

All Answers

Deepak AnandDeepak Anand
Try this = 
IMAGE(
    CASE(
        IF(NOT(ISBLANK(Field_A__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_B__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_C__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_D__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_E__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_F__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_G__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_H__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_I__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_J__c)), 1, 0) +
        IF(NOT(ISBLANK(Field_K__c)), 1, 0),
        
        11, "/servlet/servlet.ImageServer?id=0156C000000LhoC&oid=00D6C00000092uZ&lastMod=1568151767000",
        
        0,  "/servlet/servlet.ImageServer?id=0156C000000LhoH&oid=00D6C00000092uZ&lastMod=1568150696000",
        
        "/servlet/servlet.ImageServer?id=0156C000000Lhqm&oid=00D6C00000092uZ&lastMod=1568152306000"
    ),
    "Status"
)
This was selected as the best answer
Amanda BaileyAmanda Bailey
That worked perfectly! Thank you!

Was the formula I was using just not correct or is this an easier way to get the result?
Deepak AnandDeepak Anand
This is an easier way because - 
  1. The fields are repeated ONLY once
  2. IMAGE function is used ONLY once
  3. Easier to add a new field in future
  4. Readable