0

Trying to figure out how to best implement this. Background: Have a current formula field that captures if a picklist value is X for any of the picklists show X in the formula. There is a coded backend calculator that houses the proper tier order. For example, if someone selects "Happy" on the picklists, it's the top tier value so the field would "Happy" versus "Silly". Consider this formula field's structure as the order (tier). I need to add in two checkboxes to this formula field. Checkbox1 indicates that if someone selects "Happy" and the checkbox is checked, the formula field shows "Unhappy". If checkbox2 is checked, it overrides the checkbox1 behavior to show "Happy" include (nullifying the "Unhappy" result). I need to have an override function. But I cannot manage to figure this one out. Any thoughts???

    IF(
OR(
AND(
Checkbox1__c = TRUE,
Checkbox2__c = TRUE,
RecordType.DeveloperName = 'XXX'
),
ISPICKVAL(Picklist1__c, "Happy"), 
ISPICKVAL(Picklist2__c, "Happy"), 
ISPICKVAL(Picklist3__c, "Happy"), 
ISPICKVAL(Picklist4__c, "Happy"), 
ISPICKVAL(Picklist5__c, "Happy")
)
'Happy',
'Not Happy'
),

IF(
OR(
AND(
Checkbox1__c = TRUE,
Checkbox2__c = TRUE,
RecordType.DeveloperName = 'XXX'
),
ISPICKVAL(Picklist1__c, "Silly"), 
ISPICKVAL(Picklist2__c, "Silly"), 
ISPICKVAL(Picklist3__c, "Silly"), 
ISPICKVAL(Picklist4__c, "Silly"), 
ISPICKVAL(Picklist5__c, "Silly")
)
'Silly',
'Not Silly'
),

IF(
OR(
AND(
Checkbox1__c = TRUE,
Checkbox2__c = TRUE,
RecordType.DeveloperName = 'XXX'
),
ISPICKVAL(Picklist1__c, "Sad"), 
ISPICKVAL(Picklist2__c, "Sad"), 
ISPICKVAL(Picklist3__c, "Sad"), 
ISPICKVAL(Picklist4__c, "Sad"), 
ISPICKVAL(Picklist5__c, "Sad")
)
'Sad',
'Not Sad'
),

IF(
OR(
AND(
Checkbox1__c = TRUE,
Checkbox2__c = TRUE,
RecordType.DeveloperName = 'XXX'
),
ISPICKVAL(Picklist1__c, "Angry"), 
ISPICKVAL(Picklist2__c, "Angry"), 
ISPICKVAL(Picklist3__c, "Angry"), 
ISPICKVAL(Picklist4__c, "Angry"), 
ISPICKVAL(Picklist5__c, "Angry")
)
'Angry',
'Not Angry'
)

1 Answer 1

1

You can optimize this formula a lot with some case statements. It would look like this:

IF(
    RecordType.DeveloperName = 'XXX',
    IF(AND(Checkbox1__c, NOT(Checkbox2__c)),"Not ","" &
    CASE(
        MAX(
            CASE(Picklist1__c,"Happy",4,"Silly",3,"Sad",2,"Angry",1,0),
            CASE(Picklist2__c,"Happy",4,"Silly",3,"Sad",2,"Angry",1,0),
            CASE(Picklist3__c,"Happy",4,"Silly",3,"Sad",2,"Angry",1,0),
            CASE(Picklist4__c,"Happy",4,"Silly",3,"Sad",2,"Angry",1,0),
            CASE(Picklist5__c,"Happy",4,"Silly",3,"Sad",2,"Angry",1,0)
        ),
    4, "Happy",
    3, "Silly",
    2, "Sad",
    1, "Angry", 
    ""),
    ""
)
5
  • hi SFDCfox, since the code in the back end captures the tier, do I need the 4-3-2-1 callout? Also, if I want to bucket 2 and 1 as "Unimportant Feelings", what could I do? And if I check off checkbox2, even if checkbox1 is checked, it should default to the normal picklist values Sep 26, 2018 at 21:51
  • I also received the following error: Error: Incorrect parameter type for function 'AND()'. Expected Boolean, received Text Sep 26, 2018 at 21:59
  • @AwesomeUser2 Yeah, the parentheses were out of order. You can do whatever you like with this formula, it was only meant as an illustration of how to optimize against your existing formula.
    – sfdcfox
    Sep 26, 2018 at 22:01
  • Hi sfdcfox, I REALLY appreciate everything. I really want your input on the best course of action. Sep 26, 2018 at 22:04
  • @AwesomeUser2 the 4-3-2-1 is used by the case to find out which tier/bucket it was in. You could make it 3-2-1 where 1 is "Unimportant Feelings" and move the IF statement down inside the case statement if you want to. Take a look at CASE, MAX, and IF in the documentation. It's really important that you understand how they work.
    – sfdcfox
    Sep 26, 2018 at 22:20

You must log in to answer this question.

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