function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MM2020MM2020 

Hi! I need help with a formula. I am trying to calculate using two picklist fields. What is the correct syntax?

Best Answer chosen by MM2020
Maharajan CMaharajan C
It looks like the maximum compiled size formulas compilizaion on an object is exceeded.

So create a new text field, make it read-only, and set a workflow rule to populate the value every time the record is saved. Workflow formula field updates do not count against the object's compiled formula size.

In the field update set the value to new field  by use the below formula:

CASE( TEXT(Urgency__c)+'-'+TEXT(Impact__c) , 
    "Critical-Extensive", "P1", 
    "Critical-Significant", "P1",
    "Critical-Moderate", "P2",
    "Critical-Minor", "P3",
    "High-Extensive", "P1",
    "High-Significant", "P2",
    "High-Moderate", "P2",
    "High-Minor", "P3",
    "Medium-Extensive", "P2",
    "Medium-Significant", "P2",
    "Medium-Moderate", "P3",
    "Medium-Minor", "P3",
    "Low-Extensive", "P4",
    "Low-Significant",  "P4",
    "Low-Moderate",  "P4",
    "Low-Minor",  "P4",
"")

Thanks,
Maharajan.C

All Answers

MM2020MM2020
Here is what I have so far:

IF(ISPICKVAL(Urgency__c, 'Critical') && ISPICKVAL(Impact__c, 'Extensive','P1'
IF(ISPICKVAL(Urgency__c, 'Critical') && ISPICKVAL(Impact__c, 'Significant','P1,
IF(ISPICKVAL(Urgency__c, 'High') && ISPICKVAL(Impact__c, 'Extensive, P1',
IF(ISPICKVAL(Urgency__c, 'Critical') && ISPICKVAL(Impact__c, 'Moderate', 'P2',
IF(ISPICKVAL(Urgency__c, 'High') && ISPICKVAL(Impact__c, 'Significant', 'P2',
IF(ISPICKVAL(Urgency__c, 'High') && ISPICKVAL(Impact__c, 'Moderate', 'P2',
IF(ISPICKVAL(Urgency__c, 'Medium') && ISPICKVAL(Impact__c, 'Extensive', 'P2',
IF(ISPICKVAL(Urgency__c, 'Medium') && ISPICKVAL(Impact__c, 'Significant', 'P2',
IF(ISPICKVAL(Urgency__c, 'Critical') && ISPICKVAL(Impact__c, 'Minor', 'P3',
IF(ISPICKVAL(Urgency__c, 'High') && ISPICKVAL(Impact__c, 'Minor', 'P3',
IF(ISPICKVAL(Urgency__c, 'Medium') && ISPICKVAL(Impact__c, 'Moderate', 'P3',
IF(ISPICKVAL(Urgency__c, 'Medium') && ISPICKVAL(Impact__c, 'Minor', 'P3',
IF(ISPICKVAL(Urgency__c, 'Low') && ISPICKVAL(Impact__c, 'Extensive', 'P4',
IF(ISPICKVAL(Urgency__c, 'Low') && ISPICKVAL(Impact__c, 'Significant', 'P4',
IF(ISPICKVAL(Urgency__c, 'Low') && ISPICKVAL(Impact__c, 'Moderate', 'P4',
IF(ISPICKVAL(Urgency__c, 'Low') && ISPICKVAL(Impact__c, 'Minor', 'P4'))))))))))))))))
Maharajan CMaharajan C
Hi,

Try the formula like below:

CASE( TEXT(Urgency__c)+'-'+TEXT(Impact__c) , 
    "Critical-Extensive", "P1", 
    "Critical-Significant", "P1",
    "High-Extensive", "P1",
    "Critical-Moderate", "P2",
    "High-Significant", "P1",
    "High-Moderate", "P1",
    "Medium-Significant", "P1",
" Else Result ")                          // Please add the else value if no combination are met

Thanks,
Maharajan.C
MM2020MM2020
Thank you! 

I tried the formula you mentioned, but now I get a message saying the compiled formula is too big to execute.

Here's the formula:

CASE( TEXT(Urgency__c)+'-'+TEXT(Impact__c) , 
    "Critical-Extensive", "P1", 
    "Critical-Significant", "P1",
    "Critical-Moderate", "P2",
    "Critical-Minor", "P3",
    "High-Extensive", "P1",
    "High-Significant", "P2",
    "High-Moderate", "P2",
    "High-Minor", "P3",
    "Medium-Extensive", "P2",
    "Medium-Significant", "P2",
    "Medium-Moderate", "P3",
    "Medium-Minor", "P3",
    "Low-Extensive", "P4",
    "Low-Significant",  "P4",
    "Low-Moderate",  "P4",
    "Low-Minor",  "P4",
" Else Result ")
Maharajan CMaharajan C
It looks like the maximum compiled size formulas compilizaion on an object is exceeded.

So create a new text field, make it read-only, and set a workflow rule to populate the value every time the record is saved. Workflow formula field updates do not count against the object's compiled formula size.

In the field update set the value to new field  by use the below formula:

CASE( TEXT(Urgency__c)+'-'+TEXT(Impact__c) , 
    "Critical-Extensive", "P1", 
    "Critical-Significant", "P1",
    "Critical-Moderate", "P2",
    "Critical-Minor", "P3",
    "High-Extensive", "P1",
    "High-Significant", "P2",
    "High-Moderate", "P2",
    "High-Minor", "P3",
    "Medium-Extensive", "P2",
    "Medium-Significant", "P2",
    "Medium-Moderate", "P3",
    "Medium-Minor", "P3",
    "Low-Extensive", "P4",
    "Low-Significant",  "P4",
    "Low-Moderate",  "P4",
    "Low-Minor",  "P4",
"")

Thanks,
Maharajan.C
This was selected as the best answer
MM2020MM2020
Yay!  It worked.

Thank you so much, I really appreciate your help.