function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
PProkesPProkes 

Combining 3 formulas into 1

 I wish for a "New Task" to be created and sent when either of the following 3 validation rules (that I want to combine into 1 rule) apply. However, when I join the 3 formulas together I get an error message saying "Error: Syntax error. Extra AND". Can someone please advise me how to join the 3 formulas below so no errors occur? I initially had them created as separate rules though I only want one Task to be created, not possibly 3 (for the one opportunity).

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
Soft_Costs_percentage__c>0.2,
Soft_Costs_Approved__c =FALSE,
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))


AND
(
$RecordType.Name = "AUS : DFS : Opportunity Record Type", IF (
Term_mos__c >
VALUE
(
Master_Lease_Agreement__r.Term_Exc_Interim_Not_To_Exceed_Mths__c
) , TRUE,
ISPICKVAL
(
StageName,"Submitted to Sales Support : DFS"
)
)
)


AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL ( Account.Pay_Method__c ,"DDR"),
ISPICKVAL (Pay_Method__c ,"INV"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
ISPICKVAL ( Account.Pay_Method__c ,"INV"),
ISPICKVAL (Pay_Method__c ,"DDR"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))
*werewolf**werewolf*

Well the error is telling you the truth.  You can't just put one AND, close it out, and then put another -- how's the formula supposed to know which takes precedence?

 

You should try something like

 

OR (
    AND (
        clause 1...
    ),
    AND (
        clause 2...
    ),
    AND (
        clause 3...
    )
)

 

Notice how I put tabs in there, by the way.  A best practice here is to create your formulas in something like Notepad++ (which is free) which maintains indentation for you like this -- it really helps you see what's up in your formulas when you tabify it properly.

 
PProkesPProkes

Hi, thanks for the feedback. The formula below now has no errors in it, though the error message only appears when "Pay Method Approved" is FALSE (formula 3). When "Soft Costs Approved" and "Term Approved" are FALSE (and the criteria is met) the opportunity is able to be saved (when it shouldn't be). Any ideas where the formula needs tweaking?

 

OR (
AND (
$RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
Soft_Costs_percentage__c>0.2,
Soft_Costs_Approved__c =FALSE,
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"
),
OR (
AND
(
$RecordType.Name = "AUS : DFS : Opportunity Record Type", Term_Approved__c = FALSE,
IF (
Term_mos__c >
VALUE
(
Master_Lease_Agreement__r.Term_Exc_Interim_Not_To_Exceed_Mths__c
) , TRUE,
ISPICKVAL
(
StageName,"Submitted to Sales Support : DFS"
)
)
),
OR (
AND (
$RecordType.Name = "AUS : DFS : Opportunity Record Type" , Pay_Method_Approved__c = FALSE,
ISPICKVAL ( Account.Pay_Method_M__c ,"DDR"),
ISPICKVAL (Pay_Method__c ,"INV"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
Pay_Method_Approved__c = FALSE,
ISPICKVAL ( Account.Pay_Method_M__c ,"INV"),
ISPICKVAL (Pay_Method__c ,"DDR"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS")
)
))))
*werewolf**werewolf*

Well now you've gone overboard with the ORs.  All you need is one OR:  OR(x, y, z).  Remember that this is prefix notation, so you don't need the ORs in between.
PProkesPProkes

Hi "werewolf", thank you. The formula below has no errors though it doesn't work (i.e. I can still save an opportunity at "Submitted to Sales Support" even though the criteria has been met and I shouldn't be allowed to). What could be wrong with the formula below, it seems to makes sense.

 

AND (
$RecordType.Name = "AUS : DFS : Opportunity Record Type" , Pay_Method_Approved__c = FALSE,
ISPICKVAL ( Account.Pay_Method_M__c ,"DDR"),
ISPICKVAL (Pay_Method__c ,"INV"),
ISPICKVAL (StageName,"To Be Priced : DFS"),
OR (
AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
Pay_Method_Approved__c = FALSE,
ISPICKVAL ( Account.Pay_Method_M__c ,"INV"),
ISPICKVAL (Pay_Method__c ,"DDR"),
ISPICKVAL (StageName,"To Be Priced : DFS")
))
PProkesPProkes

I meant to say "To Be Priced". The formula never seems to fire irrespective of Stage.
*werewolf**werewolf*

What do you mean, the formula never seems to fire?  Is this in a workflow rule?
*werewolf**werewolf*

As I mentioned, tabification is your friend.  Tabified correctly it looks like this, and I suspect this isn't what you meant to type:

 

AND (
    $RecordType.Name = "AUS : DFS : Opportunity Record Type" , Pay_Method_Approved__c = FALSE,
    ISPICKVAL ( Account.Pay_Method_M__c ,"DDR"),
    ISPICKVAL (Pay_Method__c ,"INV"),
    ISPICKVAL (StageName,"To Be Priced : DFS"),
    OR (
        AND (
            $RecordType.Name = "AUS : DFS : Opportunity Record Type"
        ) ,
        Pay_Method_Approved__c = FALSE,
        ISPICKVAL ( Account.Pay_Method_M__c ,"INV"),
        ISPICKVAL (Pay_Method__c ,"DDR"),
        ISPICKVAL (StageName,"To Be Priced : DFS")
    )
)

 

Notepad++.  Get it.  Use it.  Start tabifying your formulas and pasting them into Salesforce.com.  It will make your life easier.
PProkesPProkes

It is a Validation Rule. I also have it saved as a Workflow Rule for the automatic Task component.