[SalesForce] Image Formula and If Statements

I had an image formula field running off a picklist that had fixed values 1,2,3,4,5. The formula worked and showed a red, amber, green, light blue, or dark blue image respectively. To make the picklist more meaningful, I changed the numbers to words. My Image formula stopped working. I tried with and without ISPICKVAL(), TEXT() and nothing before Support_level__c. Nothing happening.

IMAGE(
  CASE( ISPICKVAL(Support_Level__c ,
    "Intensive Support", "/servlet/servlet.FileDownload?file=0150g000002vla0",
    "Middle Range Support", "/servlet/servlet.FileDownload?file=0150g000002vla5",
    "Some Support and Stable", "/servlet/servlet.FileDownload?file=0150g000002vlaF",
    "Little Support and Stable", "/servlet/servlet.FileDownload?file=0150g000002vlaA",
    "Ready to move on", "/servlet/servlet.FileDownload?file=0150g000002vlaA",
    "Undefined")),
"Undefined")

I then tried a work around! Lightbulb. I created a number field which was updated by a workflow according to what the Support_Level__c values was.

IMAGE(
  CASE(Update_Traffic_Number__c,
    1, "/servlet/servlet.FileDownload?file=0150g000002vla0",
    2, "/servlet/servlet.FileDownload?file=0150g000002vla5",
    3, "/servlet/servlet.FileDownload?file=0150g000002vlaF",
    4, "/servlet/servlet.FileDownload?file=0150g000002vlaA",
    5, "/servlet/servlet.FileDownload?file=0150g000002vlaA",
    "Undefined")),
"Undefined")

Still no joy. The number field was not updating so I could not test this formula. I made number formula field to see if that would help. Nope. It did not update. There is something in the Support_Level__C field that I am not translating through anymore.

The other versions I have played with (using the traffic light formula field):

CASE( Traffic_Light_No__c ,
    1, IMAGE("/servlet/servlet.FileDownload?file=0150g000002vla0","Red"),
    2, IMAGE("/servlet/servlet.FileDownload?file=0150g000002vla5", "Amber"),
    3, IMAGE("/servlet/servlet.FileDownload?file=0150g000002vlaF", "Green"),
    4, IMAGE("/servlet/servlet.FileDownload?file=0150g000002vlaA", "Light Blue"),
    5, IMAGE("/servlet/servlet.FileDownload?file=0150g000002vlaA", "Dark Blue"),
    "Undefined")

And this:

IF( (ISPICKVAL(Support_Level__c, "Intensive Support")), (IMAGE("/servlet/servlet.FileDownload?file=0150g000002vla0", "Red")),
IF( (ISPICKVAL(Support_Level__c, "Middle Range Support")), IMAGE("/servlet/servlet.FileDownload?file=0150g000002vla5", "Amber"),
IF( (ISPICKVAL(Support_Level__c, "Some Support and Stable")), IMAGE("/servlet/servlet.FileDownload?file=0150g000002vlaF", "Green"),
IF( (ISPICKVAL(Support_Level__c, "Little Support and Stable")), IMAGE("/servlet/servlet.FileDownload?file=0150g000002vlaA", "Light Blue"),
IF( (ISPICKVAL(Support_Level__c, "Ready to move on")), IMAGE("/servlet/servlet.FileDownload?file=0150g000002vlaA", "Dark Blue"),
"Undefined")))))

I think I have now been looking at this so long I can't see my way!

Best Answer

your first variant was almost correct. The only problem is that you used wrong formula to define text value of picklist field. According to Formula Operators and Functions documentation

ISPICKVAL - Determines if the value of a picklist field is equal to a text literal you specify.

Correct formula is TEXT

TEXT - Converts a percent, number, date, date/time, or currency type field into text anywhere formulas are used. Also, converts picklist values to text in approval rules, approval step rules, workflow rules, escalation rules, assignment rules, auto-response rules, validation rules, formula fields, field updates, and custom buttons and links.

So your proper formula is:

IMAGE(
    CASE(
        TEXT(Support_Level__c),
        "Intensive Support", "/servlet/servlet.FileDownload?file=0150g000002vla0",
        "Middle Range Support", "/servlet/servlet.FileDownload?file=0150g000002vla5",
        "Some Support and Stable", "/servlet/servlet.FileDownload?file=0150g000002vlaF",
        "Little Support and Stable", "/servlet/servlet.FileDownload?file=0150g000002vlaA",
        "Ready to move on", "/servlet/servlet.FileDownload?file=0150g000002vlaA",
        "Undefined"
    ),
    "Undefined"
)

In addition, I recommend you to load that images as static resource. This will allow you to have it working on sandboxes as well, as in this case you've just hardcoded ids of uploaded documents. Lets say, you created one zip archive names SupportLevelIcons with images named in the following way:

IntensiveSupport.png
MiddleRangeSupport.png
SomeSupportAndStable.png
LittleSupportAndStable.png
ReadyToMoveOn.png

then your formula will look like:

IMAGE(
    CASE(
        TEXT(Support_Level__c),
        "Intensive Support", "/resource/SupportLevelIcons/IntensiveSupport.png",
        "Middle Range Support", "/resource/SupportLevelIcons/MiddleRangeSupport.png",
        "Some Support and Stable", "/resource/SupportLevelIcons/SomeSupportAndStable.png",
        "Little Support and Stable", "/resource/SupportLevelIcons/LittleSupportAndStable.png",
        "Ready to move on", "/resource/SupportLevelIcons/ReadyToMoveOn.png",
        "Undefined"
    ),
    "Undefined"
)

Related Solutions

[SalesForce] Is is possible to have an automatic width or height on a photo in an image formula

Doesn't look like it, using the IMAGE formula directly. A few options I would suggest.

as part of your normal business process, re-scale the images upon upload. Obviously depending on the process and/or personnel involved, or if they are self-service uploads, this may not be feasible.
do a nightly / hourly / whatever rescale of all new images. This would be a pretty easy task via an API integration and the excellent ImageMagick library.
if changing your upload process to introduce a scaling step, or a nightly rescale, is undesirable, you could introduce a middle-man server to do the scaling on the fly. So a URL like 'http://posse.force.com/photos/servlet/servlet.FileDownload?file='+LEFT(SYSTEM_Photo_Id__c,15) becomes something more like 'http://myimagescaleserver/scale?sfid='+LEFT(SYSTEM_Photo_Id__c,15)+'&width=150'

For option 3, there is a discussion here with some interesting options, including the little tidbit that Google Docs supports dynamic image resizing which I was not aware of. Image Resizer also seems like a pretty good server option, and it's free.

EDIT: realized I left out a fourth option, which is a hack but it seems like a pretty common approach to this sort of issue. You use a sidebar component with some JavaScript (usually backed by jQuery) which looks at your page, finds the profile image (probably by combination of table location and URL), and then dynamically resizes it. It's a hack because Salesforce doesn't recommend DOM manipulation and doesn't guarantee that anything will remain the same from release to release, etc. but I've seen similar things done at several clients in the past. Here's an example of what this sort of page-manipulation component looks like.

EDIT 2: just found this thread which has what look to be pretty viable middle-man options that are already around and look pretty easy to use (and mostly free!), if you don't mind a third party processing your images.

[SalesForce] Display Attachment image in formula field

Because you've enclosed customfield__c inside single quotes it is being treated as a string literal in your formula.

You need to remove it from the single quotes and instead append it to your string to get the result you want.

IMAGE('/servlet/servlet.FileDownload?file=' + customfield__c, customfield__c, 20, 20)

Best Answer

Related Solutions

[SalesForce] Is is possible to have an automatic width or height on a photo in an image formula

[SalesForce] Display Attachment image in formula field

Related Topic