+ Start a Discussion
PProkesPProkes 

Validation rules - matching picklists

Please advise what the correct formula would be for the following: before a user is able to save a DFS opportunity at the "Submitted to Sales Support : DFS" stage, the "Paid Advance or Arrears" fields on the Account and Opportunity page must match, if not, the opportunity cannot be saved at that stage. I've made an attempt at the formula below but the following error message appears: "Error: Field Paid_Advance_or_Arrears__c is a picklist field. Picklist fields are only supported in certain functions."

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c = "Advance"),
ISPICKVAL (Paid_Advance_or_Arrears__c = "Arrears"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c = "Arrears"),
ISPICKVAL (Paid_Advance_or_Arrears__c = "Advance"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS")))

Bhawani SharmaBhawani Sharma

Please try this

 

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c,"Advance"),
ISPICKVAL (Paid_Advance_or_Arrears__c,"Arrears"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
IF($RecordType.Name = "AUS : DFS : Opportunity Record Type",true,false) ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c,"Arrears"),
ISPICKVAL (Paid_Advance_or_Arrears__c,"Advance"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

PProkesPProkes

Thanks for the advice. I tried the formula, and it had no syntax errors, though when I activated this Validation Rule, no error message appeared on the Opportunity page. Then I tried changing the formula (see below), but still no error came up. I'm not sure why?

 

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c,"Advance"),
ISPICKVAL (Paid_Advance_or_Arrears__c,"Arrears"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c,"Arrears"),
ISPICKVAL (Paid_Advance_or_Arrears__c,"Advance"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

PProkesPProkes

I also have a similar formula to create. If "Term (mos") is greater than "Term Exc....." for a DFS Opportunity, an opportunity cannot advance to "Submitted to Sales Support : DFS". I get the following error message: "Error: Incorrect parameter for operator '>'. Expected Number, received Object".

 

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,

IF ( Term_mos__c > $ObjectType.Master_Lease_Agreement__c.Fields.Term_Exc_Interim_Not_To_Exceed_Mths__c ,TRUE),

ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

Bhawani SharmaBhawani Sharma

For the formula:

 

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c,"Advance"),
ISPICKVAL (Paid_Advance_or_Arrears__c,"Arrears"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"),
OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,
ISPICKVAL (Account.Paid_Advance_or_Arrears__c,"Arrears"),
ISPICKVAL (Paid_Advance_or_Arrears__c,"Advance"),
ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

 

I couldn't see anything wrong in it, only change is :

OR ($RecordType.Name = "AUS : DFS : Opportunity Record Type") ,

you can use a if statement also here

Bhawani SharmaBhawani Sharma

AND ( $RecordType.Name = "AUS : DFS : Opportunity Record Type" ,

IF ( Term_mos__c > $ObjectType.Master_Lease_Agreement__c.Fields.Term_Exc_Interim_Not_To_Exceed_Mths__c ,TRUE),

ISPICKVAL (StageName,"Submitted to Sales Support : DFS"))

 

This is not a valid formula.

Instead of "$ObjectType.Master_Lease_Agreement__c.Fields.Term_Exc_Interim_Not_To_Exceed_Mths__c" , yoy need to put the field name which you want to put for the condition match.

PProkesPProkes

We actually have a field called "Term (Exc. Interim) Not To Exceed (Mths)" on the "Master Lease Agreement" object page. I used the SFDC "insert field" button when adding it. Any ideas?