6

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 ?

  • How are the values of your Status__c field being set? Is it based on the current user's language setting? – martin Jul 16 '15 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 Jul 16 '15 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 Jul 16 '15 at 8:16
  • 2
    What's taking up the most space then? Is it the length of the picklist values? – martin Jul 16 '15 at 8:20
  • Yes..that's right – Varun Jul 16 '15 at 8:20
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
    This is exactly the workaround that I had to adopt..tx :) – Varun Jul 22 '15 at 23:51
11

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")
  • 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 Jul 23 '15 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 Jul 24 '15 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.

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

  • 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 Jul 13 '15 at 8:57
  • Those Custom Labels are not part of your Status picklist - it wont change them according to your logic.. – sfdx bomb Jul 13 '15 at 10:52
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.