Sign Up ›

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

Validation Rule and Using ISPICKVAL

The  Picklist has an option to choose -- None --

 

In my application, I want the user to always choose an item.

 

I have been unsuccessful in developing a validation rule that puts an Error Message and Does NOT Save the records.

 

Codes Checked

====

 

ISPICKVAL(CustomObject__c,"null") in the Debug Log

 

Values Found:  CustomObject__c = null

Result: Pass-Continue

 

This ISPICKVAL should be true and the Error Message Box Shown

====

Tried the Following ----

 

OR(ISPICKVAL(CustomObject__c, "Item A"),ISPICKVAL(CustomObject__C,"Item B"))

 

Value Found: CustomObject__c = Item A

 

First ISPICKVAL would be True and the Second ISPICKVAL should be so Expression is True

 

Result: Pass-Continue

====

Changed the "null" to "--None--"

 

If anyone has any ideas it would be appreciated.

 

Thank you

 

 

Best Answer chosen by Admin (Salesforce Developers) 
MarkSilberMarkSilber

The easiest thing to do is to make the field required on the page layout. As a second option, you can try this:

 

 


AND(NOT(ISPICKVAL(CustomObject__c, "")),OR(ISPICKVAL(CustomObject__c, "Item A"),ISPICKVAL(CustomObject__C,"Item B"))
 
If you are only trying to make sure the field always has a value and it doesn't matter which one, this would work:
 
 

NOT(ISPICKVAL(CustomObject__c, ""))
 
 
 
 
 
Message Edited by Mark Silber on  08-31-2009 06:01 AM


All Answers
MarkSilberMarkSilber
The easiest thing to do is to make the field required on the page layout. As a second option, you can try this:
 
 

AND(NOT(ISPICKVAL(CustomObject__c, "")),OR(ISPICKVAL(CustomObject__c, "Item A"),ISPICKVAL(CustomObject__C,"Item B"))
 
If you are only trying to make sure the field always has a value and it doesn't matter which one, this would work:
 
 

NOT(ISPICKVAL(CustomObject__c, ""))
 
 
 
 
 
Message Edited by Mark Silber on  08-31-2009 06:01 AM
This was selected as the best answer
billyweb99billyweb99
Thank you Mark.
 
 
I used the Picklist as a required field and it bypassed "NONE"
 
 


ErikNelke1ErikNelke1
I'm having a simliar problem. I have a picklist "Stage" with 4 statuses the come into play with varifying another field: RFP received, RFP Submitted,  RFI Received and RFI Submitted.
 
There is a second picklist field, Proposal Type, with a defaul vaule of "Pre-Proposal". I am trying to make sure users select a status other than "Pre-Proposal" if any of the above 4 stages are selected.
 
I created a field to convert the Proposal Type to text but can not get the validation rule to pass the syntax check. Error: Field StageName is a picklist field. Picklist fields are only supported in certain functions. 
 
This is what I tried but it's missing the mark. I wonder if I should have another new field that converts the stage to a text field to get around this or is there a formula that works with otu doing that?
 
AND (
   OR( ISPICKVAL(StageName= " RFP Received", StageName= "RFI Received", StageName= "RFI Submitted", StageName= "RFI Submitted")),
   NOT(
      CONTAINS("Pre-Proposal",  ProposalTypeSub__c )
))
 
Thanks,
 
Erik


MarkSilberMarkSilber
When building formulas using ISPICKVAL, you can't use the equal sign. The correct syntax is:
 
 
ISPICKVAL(FieldName, "Value")
 
If you click on help when building a formula, it will give you assistance on the correct syntax for each of the functions. It will also have links to some commonly used functions / validation rules that might help in this case.

When trying to catch multiple ISPICKVAL entries in a validation rule using AND or OR, you should use ISPICKVAL repeatedly for each value check. For example:
 
 
OR(ISPICKVAL(FieldName, "Value1"),ISPICKVAL(FieldName,"Value2"))
 
 
 
 


ErikNelke1ErikNelke1
Hi Mark,
 
thanks for the quick response. I was able to move the formula along a little further but then ran into:
 
Error: Syntax error. Extra ','
 
it occurs when there is a comma before NOT but if I take it out I then get an: Error: Syntax error. Extra 'NOT'
 
AND ( OR( ISPICKVAL(StageName, " RFP Received"), ( ISPICKVAL(StageName, "RFI Received")), ( ISPICKVAL(StageName, "RFI Submitted"))), ( ISPICKVAL(StageName, "RFI Submitted"))),
   NOT(
      CONTAINS("Pre-Proposal",  ProposalTypeSub__c )
)))


MarkSilberMarkSilber
Can you please paste your formula using the code insert option so I can see it without all the smilely faces? It's the clipboard icon with the "c" on it.


ErikNelke1ErikNelke1
Lol, sory about that. i just saw it when I opened you reply.
 
AND ( OR( ISPICKVAL(StageName, " RFP Received"), ( ISPICKVAL(StageName, "RFI Received")), ( ISPICKVAL(StageName, "RFI Submitted"))), ( ISPICKVAL(StageName, "RFI Submitted"))),
   NOT(
      CONTAINS("Pre-Proposal",  ProposalTypeSub__c )
)))
I've had good luck with other rules particularly those you mentioned in the online help. this one's less generic and it's tripping me up.


MarkSilberMarkSilber
You have extra parentheses before the ISPICKVAL function - they should be:
 
 
ISPICKVAL(fieldname, "value"), not (ISPICKVAL(fieldname, "value"))
 
 You also have to specify the field name for the CONTAINS function:

CONTAINS(fieldname, "string")
 

 


ErikNelke1ErikNelke1
Thanks Mark!
 
For the record I'm posting the code in case anyone needs it.
 
AND ( OR( ISPICKVAL(StageName, " RFP Received"), ISPICKVAL(StageName, "RFI Received")), ISPICKVAL(StageName, "RFI Submitted"), ISPICKVAL(StageName, "RFI Submitted"),
   NOT(
      CONTAINS( ProposalTypeSub__c , "Pre-Proposal")
))
 


ErikNelke1ErikNelke1
One last question. Sorry.
 
The workflow to update the Pickiest Proposal type to a Text field is working fine BUT the validation rule we've been corresponding on isn't catching it. I've changed the Stage back and forth to see what happens but nothing unfortunately. Any ideas?


ErikNelke1ErikNelke1
Anyone?


MarkSilberMarkSilber
When you posted your final code, it looks like you have repeated the ISPICKVAL condition for StageName - they both say "RFI Submitted". Shouldn't one of them be "RFP Submitted" ?

AND ( OR( ISPICKVAL(StageName, " RFP Received"), ISPICKVAL(StageName, "RFI Received")), ISPICKVAL(StageName, "RFI Submitted"), ISPICKVAL(StageName, "RFI Submitted"),
   NOT(
      CONTAINS( ProposalTypeSub__c , "Pre-Proposal")
))
 



ErikNelke1ErikNelke1
Hi,
 
Thanks. I did catch it and change it later on Friday. Still having the same issue with the val. rule not catching though. 
 
The workflow rule does fine in taking the picklist value and copying into the text field ProposalTypeSub_c. 
 
I'm not sure why it's missing it.
 
 


MarkSilberMarkSilber
The workflow rule will fire AFTER the record is saved so the validation rule won't work if it depends on the text field.


ErikNelke1ErikNelke1
I guess the next question is, is there a way to do this or is it just the way the system works and I'm out of luck?


MarkSilberMarkSilber
You can either change the validation rule to catch all the possible picklist values for proposal type (instead of the text field) or create a formula field that mimics and/or replaces the text field. Formula fields are calculated "real-time" so it should be captured in the validation rule


ErikNelke1ErikNelke1
Thanks again.
 
I think the reason this is such a pain is that I have 2 picklist fields, which is why I have the one picklist value converted to text. The problem is, the rule needs to runs and convert the proposalsub_c each time a record is created/edited so as it moves through the Received/Submitted Stages I can verify the proposal type is correctly set. That means it's writing each time hence the rule to catch it is missing it. 
 
Apparently comparing 2 picklist values is an issue as bet I understand it.