1

I am trying to make a field populate with a formula based on a selection in a picklist value. can you please check my formula?

CASE(FedCapture__Our_Role__c,

"Prime",Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c))
)

"Subcontractor",Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c))
)

"JV - Managing Partner",Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((  G_IAP_Share_JV_Share__c) * (Calculated_PWIN__c))
)

"JV - Minority Partner",Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((  G_IAP_Share_JV_Share__c) * (Calculated_PWIN__c))
))
3
  • use ISPICKVAL() Commented Apr 13, 2017 at 17:58
  • @SantanuBoral ISPICKVAL() isn't required if FedCapture__Our_Role__c is the picklist. Read the last tip included in the description of Case in the formula function documentation.
    – Derek F
    Commented Apr 13, 2017 at 18:07
  • Right now, you're getting the error at "Subcontractor", because the right-paren immediately before that is closing your CASE(). It looks like you want to make a second comparison after testing is FedCapture__Our_Role__c is "Prime", "Subcontractor", etc..., but to help you build that, we'll need additional information. Please edit your question to include more details about the conditions where you want to use Total_Contract_Value_USD__c * Calculated_PWIN__c vs when you want to use Total_Contract_Value_USD__c * 100 * Calculated_PWIN__c (or G_IAP_Share_JV_Share__c later on)
    – Derek F
    Commented Apr 13, 2017 at 18:30

3 Answers 3

1

The syntax of the CASE() formula is this:

CASE(
    field to compare,
    test_value_1, result_1,
    test_value_2, result_2,
    ...
    test_value_n, result_n,
    default_result
 )

If we use this, and apply it to your provided formula...

CASE(
    FedCapture__Our_Role__c,
    'Prime', Total_Contract_Value_USD__c  * ( Calculated_PWIN__c),
    Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c))
),
'Subcontractor', ...

So by the time we get to testing for your second picklist value, you've already exited the CASE() function. Salesforce has no idea what to do with your formula after that point.

By looking at your attempted formula, I can guess that you want to perform an additional test for each picklist value to determine which calculation (one of two) that you want to perform.

You haven't included enough information in your question for me to give an exact formula, but the general version would look something like this:

CASE(
    FedCapture__Our_Role__c,
    "Prime", IF(<some field> = <some value>, Total_Contract_Value_USD__c  * (Calculated_PWIN__c), Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c))),
    "Subcontractor",IF(<some field> = <some value>, Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c))),
    "JV - Managing Partner", IF(<some field> = <some value>, Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((  G_IAP_Share_JV_Share__c) * (Calculated_PWIN__c))),
    "JV - Minority Partner",IF(<some field> = <some value>, Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((  G_IAP_Share_JV_Share__c) * (Calculated_PWIN__c)))
)

Notice how I've added an IF() into things here. If you want to perform one of two calculations, that's what you'll need to do. The information about which picklist value is chosen for a given record isn't enough.

You could also simplify the formula a little bit (removing extraneous parenthesis, getting rid of a few non-required field references)

CASE(
    FedCapture__Our_Role__c,
    "Prime", Total_Contract_Value_USD__c  * Calculated_PWIN__c * IF(<some field> = <some value>, 1, 100),
    "Subcontractor",Total_Contract_Value_USD__c  * Calculated_PWIN__c * IF(<some field> = <some value>, 1, 100),
    "JV - Managing Partner", Total_Contract_Value_USD__c  *  Calculated_PWIN__c * IF(<some field> = <some value>, 1 ,  G_IAP_Share_JV_Share__c),
    "JV - Minority Partner",Total_Contract_Value_USD__c  *  Calculated_PWIN__c * IF(<some field> = <some value>, 1 ,  G_IAP_Share_JV_Share__c)
)

+edit: The above formula could be simplified further still. Since Total_Contract_Value_USD__c * Calculated_PWIN__c is common to all picklist values, we can take that out of the CASE() entirely

Total_Contract_Value_USD__c  *  Calculated_PWIN__c *
CASE(
    FedCapture__Our_Role__c,
    "Prime", IF(<some field> = <some value>, 1, 100),
    "Subcontractor", IF(<some field> = <some value>, 1, 100),
    "JV - Managing Partner", IF(<some field> = <some value>, 1 ,  G_IAP_Share_JV_Share__c),
    "JV - Minority Partner", IF(<some field> = <some value>, 1 ,  G_IAP_Share_JV_Share__c)
 )
1
  • Thank you Derek i realized that i actually didnt need to make it so complicated thank you,
    – ryan
    Commented Apr 13, 2017 at 19:37
1

Was able to get it to work thanks!

    CASE(FedCapture__Our_Role__c,
"Prime", Total_Contract_Value_USD__c  * Calculated_PWIN__c,
"Subcontractor", Total_Contract_Value_USD__c  * Calculated_PWIN__c,
"JV - Managing Partner", Total_Contract_Value_USD__c  * Calculated_PWIN__c G_IAP_Share_JV_Share__c, NULL)

Thanks!

0

I'm going to assume Fed_Capture_Our_Role__c is the picklist. So that would play out like this:

IF(
ISPICKVAL(FedCapture__Our_Role__c, "Prime"), Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c)),
IF(
ISPICKVAL(FedCapture__Our_Role__c, "Subcontractor"), Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c)),
IF(
ISPICKVAL(FedCapture__Our_Role__c, "JV - Managing Partner"), Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((  G_IAP_Share_JV_Share__c) * (Calculated_PWIN__c))),
IF(
ISPICKVAL(FedCapture__Our_Role__c, "JV - Minority Partner"), Total_Contract_Value_USD__c  * ( Calculated_PWIN__c), Total_Contract_Value_USD__c * ((  G_IAP_Share_JV_Share__c) * (Calculated_PWIN__c)),
""))))

I think the () line up it got a little confusing at the end of the line, but you get the idea.

EDIT:

You could use CASE but not in the way you currently have this setup. CASE works as CASE(expression, value, result, else_result)

Looking at your first lines you have

expression: FedCapture__Our_Role__c value: "Prime" result: Total_Contract_Value_USD__c * ( Calculated_PWIN__c) else: Total_Contract_Value_USD__c * ((100) * (Calculated_PWIN__c))

then you close it. Which would work for the one line but you continue the pattern without a new CASE(expression. So if you want to go down that path you need to remove your else result or put more case(FedCapture__Our_Role__c, in there.

9
  • If you use the CASE() function, you don't need all of those IF() and ISPICKVAL(). See the tips section of CASE() in the formula function documentation
    – Derek F
    Commented Apr 13, 2017 at 18:09
  • Im getting a syntax error Error: Syntax error. Extra IF
    – ryan
    Commented Apr 13, 2017 at 18:24
  • if i use CASE it says Syntx ERROR "Extra Subcontractor"
    – ryan
    Commented Apr 13, 2017 at 18:30
  • It's because you closed the case with a ) then started a new statement. Try commenting out some of the lower lines and get one line to compile and then add lines one at a time. Commented Apr 13, 2017 at 18:31
  • When i do this it says Extra "Subcontractor
    – ryan
    Commented Apr 13, 2017 at 18:41

You must log in to answer this question.

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