Using Custom Label in Custom Formula Fields

Question

We have a formula field that displays images based upon the custom object's record type and a picklist value.

This formula field is like

IF( $RecordType.Name = "XYZ", IMAGE( 
CASE( 
Status__c , 
"FIRST","/resource/first", 
"SECOND", "/resource/second", 
"THIRD", "/resource/THIRD", 
"/s.gif"), 
"DEFAULT")

The above works well and displays images as expected.

We want to explore whether instead of using "FIRST", "SECOND" etc..can we use custom labels.

The custom label's name is First and its value is FIRST.

I tried the following without success

1)

IF( $RecordType.Name = "XYZ", IMAGE( 
CASE( 
Status__c , 
$Label.First,"/resource/first", 
"SECOND", "/resource/second", 
"THIRD", "/resource/THIRD", 
"/s.gif"), 
"DEFAULT")

Result : Getting the following error

"Error: Incorrect parameter type for function 'CASE()'. Expected , received Text"

2)

 IF( $RecordType.Name = "XYZ", IMAGE( 
CASE( 
TEXT(Status__c) , 
$Label.First,"/resource/first", 
"SECOND", "/resource/second", 
"THIRD", "/resource/THIRD", 
"/s.gif"), 
"DEFAULT")

Result : Formula field gets successfully compiled but the image is not being shown.

I searched online and I could not find any actual examples of $Label being used in formula fields though there are examples of $Label being used in VF pages.

Can someone help me on this ?

Answer 1

I did this a very different way...

So rather than use a Formula field to do this I use a workflow rule with a field update. The amount of space in the field update is much greater so your statements will not be an issue.

In fact I use this method to check the difference between the Product total amount (roll up of Opportunity Line Item) and the Estimated Revenue that the Opportunity Owner enters manually (this being their best idea of the value of the opportunity at any given step).

We check the month, quarter and year of the opportunity, check whether the value is above or below a certain threshold and the line of business and the end response sets a field (called RedAmberGreen__c) that is not on their page view and then I use a formula field to show either a red, amber or green light.

Answer 2

One thing that you could do is change all of your picklist values to numbers:

Then set up a translation value for each of those picklist values in the translation workbench:

That way, your users will still see the full text value, but you can save space in your formula field:

IF( $RecordType.Name = "XYZ", IMAGE( 
CASE( 
Status__c , 
"1","/resource/first", 
"2", "/resource/second", 
"3", "/resource/THIRD", 
"/s.gif"),  
"DEFAULT")

Answer 3

The CASE function only takes string literals and not pointers like Custom Label and Custom Setting (though undocumented). So the above desired functionality is not achievable.

You will have to use picklist values directly something like you tried at the first place-

IF( $RecordType.Name = "XYZ", IMAGE( 
CASE( 
Status__c , 
"FIRST","/resource/first", 
"SECOND", "/resource/second", 
"THIRD", "/resource/THIRD", 
"/s.gif"), 
"DEFAULT")

More details on case function here - https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#CASE

Answer 4

I think you cannot do this because Labels can change upon user execution context, so the formula would not be the same for all users. I think you should use Custom Settings.

Answer 5

Seems like you are using picklist Values in your formula - you can translate them using translation workbench.
custom labels are static they won't work with your criteria..

Answer 6

For some reason the CASE function seems to require a literal, it won't even let you concatenate strings. You can accomplish the same functionality with nested if's and the == operator will let you compare the value of Status__c to a label

IF( $RecordType.Name = "XYZ", IMAGE( 
  IF(TEXT(Status__c) == $Label.First, "/resource/first", 
    IF(TEXT(Status__c) == $Label.Second, "/resource/second",
      IF(TEXT(Status__c) == $Label.Third, "/third", "/s.gif"))), 'alt')
,  
"DEFAULT")