2

I'm trying to get Total_Minutes__c to return a number value based upon a text pickval, but the only value that is working is 45, other picklist values are returning 0.

(If I choose "15 min" or "30 min" or "1hr 30 min" I get a return value of 0, but if I choose "45 min", it returns 45)

I've verified that the formula text matches all picklist values:

CASE(

IF(ISPICKVAL(Time_Spent__c , "15 min") , 15, 0), IF(ISPICKVAL(Time_Spent__c , "30 min") , 30, 0), IF(ISPICKVAL(Time_Spent__c , "45 min") , 45, 0), IF(ISPICKVAL(Time_Spent__c , "1hr") , 60, 0), IF(ISPICKVAL(Time_Spent__c , "1hr 15 min") , 75, 0), IF(ISPICKVAL(Time_Spent__c , "1hr 30 min") , 90, 0), IF(ISPICKVAL(Time_Spent__c , "1hr 45 min") , 105, 0), IF(ISPICKVAL(Time_Spent__c , "2hr") , 120, 0), IF(ISPICKVAL(Time_Spent__c , "2hr 15 min") , 135, 0), IF(ISPICKVAL(Time_Spent__c , "2hr 30 min") , 150, 0), IF(ISPICKVAL(Time_Spent__c , "2hr 45 min") , 165, 0), IF(ISPICKVAL(Time_Spent__c , "3hr") , 180, 0) )

2
  • Is this a multi-select picklist?
    – o-lexi
    May 6, 2016 at 20:51
  • 1
    @Oleksiy you cannot use ispickval on multiselect picklist help.salesforce.com/…
    – Rao
    May 6, 2016 at 20:56

2 Answers 2

7

Your usage of CASE() isn't quite right. You don't need to use ISPICKVAL() if your CASE() function is operating on a picklist field.

The syntax is

CASE(
 [expression],
 [test value 1], [result 1],
 [test value 2], [result 2],
 ...
 [test value n], [result n],
 [default result]
)

To get the result you want, the first few lines of your CASE should be

CASE(
    Time_Spent__c,
    "15 min", 15,
    "30 min", 30,
    ...,
    0
)

As an interesting exercise, I'll try to explain why your provided snippet was returning 45 for '45 min' and 0 for everything else.

tl;dr: you managed to create a formula that effectively dynamically changed the formula being evaluated, based on the value of a field

Why was it returning the correct value for '45 min'?

Your first IF(ISPICKVAL()) was being used as the expression that all of the subsequent cases were testing against.

IF(ISPICKVAL(Time_Spent__c, '15 min'), 15, 0) returns either 0 or 15.

Your second and third IF(ISPICKVAL()) were acting as the first test and result. The third and fifth acted as the second test/result, and so on.

For '45 min', the CASE became

CASE(
 IF('45 min' == '15 min'), 15, 0),
 IF('45 min' == '30 min'), 30, 0), IF(('45 min' == '45 min'), 45, 0),
 ...
)

which simplifies to

CASE(
 0,
 0, 45,
 ...
)

or simply

IF(0 == 0, 45, 0)

Why was this the only non-zero value that would ever be returned?

Simplifying the entirety of your original CASE looks like this

// when Time_Spent__c was '15 min'
CASE(
    15,
    0, 0,
    0, 0,
    0, 0,
    0, 0,
    0, 0,
    0
)

// when Time_Spent__c was '30 minutes'
CASE(
    0,  
    30, 0,
    0, 0,
    0, 0,
    0, 0,
    0, 0,
    0
)

// when Time_Spent__c was '75 minutes'
// with overlapping test values, the first one (0, 0) is used
// ...or maybe the last one is used.
// It couldn't just be going to the default case, or you would have seen 
//  the proper value for 3 hours
CASE(
    0,  
    0, 0,
    0, 75,
    0, 0,
    0, 0,
    0, 0,
    0
)

Given those 3 cases above, you should be able to extrapolate why the rest of the values produced a result of '0'.

0
0

Use TEXT() function picklist field. For Example if your switch statement is like CASE(Field__C,'val1','result1',...)

than change it to

CASE(TEXT(Field__C),'val1','result1',...)

You must log in to answer this question.

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