7

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 ?

5
  • How are the values of your Status__c field being set? Is it based on the current user's language setting?
    – martin
    Commented Jul 16, 2015 at 8:03
  • In other words, what is the motivation behind wanting to use the value of a label as opposed to the value of a field?
    – martin
    Commented Jul 16, 2015 at 8:10
  • The formula that I have pasted is just a tiny lookalike of the actual formula that is live. We are trying to add more "values" and it is hitting the 5000 characters limit. That's why we are trying to replace the string literals of status picklist values with custom labels.
    – Varun
    Commented Jul 16, 2015 at 8:16
  • 2
    What's taking up the most space then? Is it the length of the picklist values?
    – martin
    Commented Jul 16, 2015 at 8:20
  • Yes..that's right
    – Varun
    Commented Jul 16, 2015 at 8:20

6 Answers 6

2
+100

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.

1
  • 1
    This is exactly the workaround that I had to adopt..tx :)
    – Varun
    Commented Jul 22, 2015 at 23:51
12

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

Set Picklist Values to Numbers

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

Add Translations to Those Values

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

IF( $RecordType.Name = "XYZ", IMAGE( 
CASE( 
Status__c , 
"1","/resource/first", 
"2", "/resource/second", 
"3", "/resource/THIRD", 
"/s.gif"),  
"DEFAULT")
2
  • Not that there is anything wrong with this but it seems a huge overhead of effort. Is there a chance that there is a conflict in the translation?
    – SeanGorman
    Commented Jul 23, 2015 at 20:10
  • 1
    @SeanGorman I would typically think that you wouldn't want to keep using such long text values in the code and stored in the database, but it certainly could be time consuming to make the change, and it might not be deemed worthwhile to spend that time when the current setup is working. As for a conflict in translation, you just have to make sure that everyone is clear on what all of the codes mean. If the code is "APPSTORE", you should make sure that the translators for each of the languages that your org uses knows that the value refers to any app store, and not just the Apple store.
    – martin
    Commented Jul 24, 2015 at 1:22
2

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

1

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.

1

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")
0

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..

2
  • Actually I will create 3 custom labels for the above example one for each matching. I am ok if each custom label return the string in the case function.
    – Varun
    Commented Jul 13, 2015 at 8:57
  • Those Custom Labels are not part of your Status picklist - it wont change them according to your logic..
    – sfdx bomb
    Commented Jul 13, 2015 at 10:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .