0

I have two picklist fields; "Status" and "Ruled out reason".

I have a formula field called acceptability rating which is determined by the status and the ruled out reason fields. I have written the logic for the formula but it is too long and will not fit within the maximum limits for a formula field. Can anyone help with shortening the logic?

At the moment i am writing the logic for the ruled out reason status every time is there any way to reduce this. Apologies if this is a stupid question but i am stuck with it. Logic outlined below;

IF(ISPICKVAL( pba__Status__c , "New"), "Undetermined", "") 
+IF(ISPICKVAL( pba__Status__c , "Initial"), "Undetermined", "") 
+IF(ISPICKVAL( pba__Status__c , "LNC"), "Undetermined", "") 
+IF(ISPICKVAL( pba__Status__c , "Unqualified"), "Undetermined", "") 
+IF(ISPICKVAL( pba__Status__c , "Good Lead"), "Acceptable", "") 
+IF(ISPICKVAL( pba__Status__c , "Prospect"), "Acceptable", "") 
+IF(ISPICKVAL( pba__Status__c , "Dealt"), "Acceptable", "") 
+IF(ISPICKVAL( pba__Status__c , "Uncontactable"), "Unacceptable", "")  
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Agent")), "Unacceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Bought Elsewhere")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Brexit")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Change of Circumstances")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Commercial property only")), "Unacceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Completed property")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Completion time")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Concerned about exit")), "Acceptable", "") 
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Coronavirus")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Didn't like development")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Event sign up")), "Undetermined", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Existing enquiry is good lead/prospect")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Expensive")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Financial")), "Unacceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Freehold only")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Fund")), "Unacceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Had property terminated")), "Unacceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Investor services")), "Undetermined", "") 
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Lack of management control")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Language Barrier")), "Unacceptable", "") 
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Location")), "Acceptable", "") 
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Lost Confidence")), "Acceptable", "") 
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Lost contact")), "Undetermined", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Not a strong enough investment")), "Acceptable", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Not interested refused to talk")), "Unacceptable", "") 
+IF( AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Other")), "Undetermined", "") 
+IF(AND(ISPICKVAL (pba__Status__c,"Ruled Out"), ISPICKVAL( Ruled_Out_Reason__c , "Remove from database")), "Unacceptable", "")

1 Answer 1

0

Try this out. The CASE functions convert your picklist values to a numeric value and then the outcomes (0, 1, 2) are translated.

CASE(
CASE( TEXT(  pba__Status__c ),
    "New", 0,
    "Initial", 0,
    "LNC", 0,
    "Unqualified", 0,
    "Good Lead", 2,
    "Prospect", 2,
    "Dealt", 2,
    "Uncontactable", 1,
    "Ruled Out", 1,
    0
    )
+
CASE( TEXT( Ruled_Out_Reason__c)
    "Agent", 0,
    "Bought Elsewhere", 1,
    "Brexit", 1,
    "Change of Circumstances", 1,
    "Commercial property only", 0,
    "Completed property", 1,
    "Completion time", 1,
    "Concerned about exit", 1,
    "Coronavirus", 1,
    "Didn't like development", 1,
    "Event sign up", -1,
    "Existing enquiry is good lead/prospect", 1,
    "Expensive", 1,
    "Financial", 0,
    "Freehold only", 1,
    "Fund", 0,
    "Had property terminated", 0,
    "Investor services", -1,
    "Lack of management control", 1,
    "Language Barrier", 0,
    "Location", 1,
    "Lost Confidence", 1,
    "Lost contact", -1,
    "Not a strong enough investment", 1,
    "Not interested refused to talk", 0,
    "Other", -1,
    "Remove from database", 0,
    0
    ),
    0, "Undetermined",
    1, "Unacceptable",
    2, "Acceptable",
    "Error"
)

You must log in to answer this question.

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