+ Start a Discussion
jisaacjisaac 

another If and OR conundrum

I want to create a formula the enters a the following percentage based on a picklist (default is 20) unless the product line is one of those specified.

OSI Business Units (picklist)


CSG Bank Acct Mgmt34

CSG Bank Sales34

CSG CU Acct Mgmt30

CSG CU Sales - Major30

INTL30

IPTG Acct Mgmt15

IPTG Sales15

OSC-BSG30

OSC Core AM30

All Others20
except when the opportunity is for one of these Product Lines
Product Line (picklist)


Internet - ASP40

Internet - In-House40

Lending - Commercial Velocity40

Lending - Consumer Streamlend40

Lending - Consumer Velocity40

Lending - Velocity Conversion40


Here's what I have tried so far:

IF(ISPICKVAL(OSI_Business_Unit__c , "CSG Bank Acct Mgmt", "CSG Bank Sales",34, 20),
IF(ISPICKVAL(OSI_Business_Unit__c , "CSG CU Acct Mgmt", "CSG CU Sales - Major","OSC-BSG","OSC Core AM",30, 20),
IF(ISPICKVAL(OSI_Business_Unit__c , "IPTG Acct Mgmt", "IPTG Sales",15, 20), 20),
IF( ISPICKVAL( Product_Line__c ,  "Internet - In-House", "Internet - ASP", "Lending - Consumer Streamlend", "Lending - Consumer Velocity", "Lending - Commercial Velocity","Lending - Velocity Conversion", 40, 20),20)
)
)

but I keep getting syntax errors - can someone show me how to write this?

Best Answer chosen by Admin (Salesforce Developers) 
jisaacjisaac
Finally got it - it was the problem in the very last set where the alternative (false) answer needed to be before the closing paragraph.

I did as you suggested - one If statement at a time - thanks for your help and suggestions!

Jane



if(
     or(
         ispickval( Product_Line__c ,"Internet - ASP")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Lending - Commercial Velocity")
        ,ispickval(Product_Line__c ,"Lending - Consumer Streamlend")
        ,ispickval(Product_Line__c ,"Lending - Consumer Velocity")
        ,ispickval(Product_Line__c ,"Lending - Velocity Conversion")
     ), 40
,if(
    or(
       ispickval( OSI_Business_Unit__c ,"CSG CU Sales - Major")
      ,ispickval(OSI_Business_Unit__c ,"INTL")
      ,ispickval(OSI_Business_Unit__c ,"OSC Core AM")
      ,ispickval(OSI_Business_Unit__c ,"OSC-BSG")
      ,ispickval(OSI_Business_Unit__c ,"CSG CU Acct Mgmt")
    ), 30
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"CSG Bank Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"CSG Bank Sales")
    ), 34
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"IPTG Sales")
    ), 15,20
)
)
)
)

All Answers

JakesterJakester
Are you talking about updating the standard Percentage field in Salesforce? Or did you create a custom percentage field? I'm confused, because it sounds like you want a percentage field that can only be one of a few possible numbers, none of which can ever reach 100%...
jisaacjisaac
This is a custom percentage field. We take off a certain percentage of the total amount if there are third party fees in the deal. That percentage varies based on the Business Unit and sometimes by the Product Line.


Message Edited by jisaac on 10-03-2008 01:08 PM
JakesterJakester
Something like this, then. Keep in mind that you didn't give me the field names, so Product Line is probably something like Product_Line__c, but I couldn't be sure so I didn't guess. Same thing for OSI Business Units. Also, I only gave you a couple of examples to get started - you'll need to flesh out the rest.
Code:
 if(
     or(
         ispickval(Product Line,"Internet - ASP")
        ,ispickval(Product Line,"Internet - In-House")
     ), 40
,if(
    or(
       ispickval(OSI Business Units,"IPTG Sales")
      ,ispickval(OSI Business Units,"INTL")
      ,ispickval(OSI Business Units,"OSC Core AM")
    ), 30
,if(
    or(
       ispickval(OSI Business Units,"CSG Bank Acct Mgmt")
       ,ispickval(OSI Business Units,"CSG Bank Sales")
    ), 34
)
),0
)



 
jisaacjisaac
Thanks Jakester,

I really appreciate your help on this and I like the way you lay it out. I think we are close but still a problem:

Here's what I did so far and I am getting an error message: "Incorrect number of parameters for Function IF(). Expected 3, Received 2."  Any idea where and why?

if(
     or(
         ispickval( Product_Line__c ,"Internet - ASP")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Lending - Commercial Velocity")
        ,ispickval(Product_Line__c ,"Lending - Consumer Streamlend")
        ,ispickval(Product_Line__c ,"Lending - Consumer Velocity")
        ,ispickval(Product_Line__c ,"Lending - Velocity Conversion")
     ), 40
,if(
    or(
       ispickval( OSI_Business_Unit__c ,"CSG CU Sales - Major")
      ,ispickval(OSI_Business_Unit__c ,"INTL")
      ,ispickval(OSI_Business_Unit__c ,"OSC Core AM")
      ,ispickval(OSI_Business_Unit__c ,"OSC-BSG")
      ,ispickval(OSI_Business_Unit__c ,"CSG CU Acct Mgmt")
    ), 30
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"CSG Bank Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"CSG Bank Sales")
    ), 34
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"IPTG Sales")
    ), 15
),20
)
)
)
JakesterJakester

You're welcome-

I'm pretty sure all you need to do is put the ",20" towards the bottom of the formula after the second-to-last close parenthesis, like so:

Code:
...
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"IPTG Sales")
    ), 15
)
)
),20
)


 

jisaacjisaac
Rats - still getting the same error message even after moving it like you suggested. I wish I understood the logic better so it would not feel like I am stumbling around in the dark. Someday I am going to learn how to write code....

Any other ideas?
JakesterJakester

Work backwards. Start with just one if() and get it working. Then add an or() into it, then add another if(). The basic ideas are these:

If(logical test, what to do if true, what to do if false)

Or(logical test, logical test, logical test, etc) - if any of the tests are True, then you get a True result.

So, here's a simple if() example:

Code:
if(ispickval(Product Line,"Internet - ASP"), 40, 0)


 

That's saying "if the product line picklist is set to "Internet - ASP" then return 40, if it's not, return 0"
 
to add an Or() into the mix to allow the picklist to be two values:
 
Code:
if(
or(
ispickval(Product Line,"Internet - ASP") ,ispickval(Product Line,"Internet - ASP")
),40,0
)

In order to add additional If() statements, you replace the spot where I've been putting a 0 (the "what to do if the logical test is false" area) with a new If() statement.

Everything else is just testing, carefully adding on, counting parenthesis, and a fair amount of cussing <grin>.

Good luck!

-Jake
 

jisaacjisaac
Finally got it - it was the problem in the very last set where the alternative (false) answer needed to be before the closing paragraph.

I did as you suggested - one If statement at a time - thanks for your help and suggestions!

Jane



if(
     or(
         ispickval( Product_Line__c ,"Internet - ASP")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Internet - In-House")
        ,ispickval(Product_Line__c ,"Lending - Commercial Velocity")
        ,ispickval(Product_Line__c ,"Lending - Consumer Streamlend")
        ,ispickval(Product_Line__c ,"Lending - Consumer Velocity")
        ,ispickval(Product_Line__c ,"Lending - Velocity Conversion")
     ), 40
,if(
    or(
       ispickval( OSI_Business_Unit__c ,"CSG CU Sales - Major")
      ,ispickval(OSI_Business_Unit__c ,"INTL")
      ,ispickval(OSI_Business_Unit__c ,"OSC Core AM")
      ,ispickval(OSI_Business_Unit__c ,"OSC-BSG")
      ,ispickval(OSI_Business_Unit__c ,"CSG CU Acct Mgmt")
    ), 30
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"CSG Bank Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"CSG Bank Sales")
    ), 34
,if(
    or(
       ispickval(OSI_Business_Unit__c ,"IPTG Acct Mgmt")
       ,ispickval(OSI_Business_Unit__c ,"IPTG Sales")
    ), 15,20
)
)
)
)
This was selected as the best answer
jisaacjisaac
One correction in the formula (in case anyone else cares). Since I wanted the resulting entries to be percentages (with no decimal points) I had to correct the values in the formula. Instead of 30, I had to use 0.3. Instead of 15, I had to use 0.15. etc. etc.

Jane


Message Edited by jisaac on 10-07-2008 04:14 PM
JakesterJakester
Good job, Jane! Well done.