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
AdminNewbieAdminNewbie 

Validation Rule Assistance Needed

    OK, my company wants to be able to set a minimum amount for each product in a picklist in opportunities.  So i know I need to write a validation rule I just don't know where to begin.  Would I use CASE or ISPICKVAL?  The idea is that each picklist value (product) would have a different minimum amount (amount).

Thanks

Lisa


Message Edited by AdminNewbie on 04-01-2008 10:53 AM
NPMNPM

The thing to remember is for a validation rule to trigger the formula must resolve to TRUE.  Since you have a set of some number of combinations of  product and amount  that you would want any of the combinations to equal TRUE one way you could do this is using OR and  AND and NOT and something like:

OR(

AND(ISPICKVAL(Product, "a"),NOT(Amount=1)),

AND(ISPICKVAL(Product, "b"),NOT(Amount=2)),

AND(ISPICKVAL(Product, "c"),NOT(Amount=3)),

)

This is not tested so you may need to work through the correct number of parentheses, syntax.  But it should evaluate to true if picklist value "a" is selected and the amount is not equal to 1, and so on for each of the picklist values.  I'm not sure how CASE can help you here.

 

 

 
AdminNewbieAdminNewbie
    Thank you I will try this asap!  I wasnt sure if CASE could help me either it just kept coming up when I searched help :)
NPMNPM
Instead of the "NOT(Amount=1)"
 
"Amount<>1"
 
would work.
tmactmac

Hello Everyone,

I need some assitance.  I am having problems writing a validation rule for a text field.  Basically what I want to happen is everytime users select that the opportunity is a candidate (checkbox field) then they would be required to fill in the text field which indicates the status of opportunity. 

Can someone help me?  Thanks a bunch!
NPMNPM

Try

AND(Candidate=TRUE, Status="")

Again untested but this formula should evaluate to TRUE if the Candidate check box is Checked and there is nothing (null) in the Status.

 
AMartinAMartin
If you are using a text field for Status, use:
 
And(
Candidate__c = True,
(Status__c) = ""
)
 
 
If it's actually a picklist, use
 
And(
Candidate__c = True,
IsPickval(Status__c ,"")
)
 
 
Aiden
AdminNewbieAdminNewbie
    The validation rule worked perfectly, thank you!  I do have another issue however, is there anyway to over-ride the rule for our admin users?  Thanks :)

To be more specific what i want to say is: if opportunity owner is not manuel then run validation formula.

As I move forward with this I will keep updating!

Manuel is rep id number 009, which is a picklist value, so I have now got this far:

OR(

AND(ISPICKVAL( Rep_ID__c. "009"),

My problem is how to i go forward, saying if rep id is 009 do not run validation formula?

Thanks for any help :)


Message Edited by AdminNewbie on 04-08-2008 10:29 AM
NPMNPM

Something like this should work.  The formula will only be TRUE (trigger the rule) when the rep is not manuel

AND(ISPICKVAL( NOT(Rep_ID__c. "009")),

AMartinAMartin
It might be better to reference the User's profile instead of their name or id number.  If Manuel leaves his position or the company, it's a lot easier to put the new person into his profile rather than searching and updating all your validation rules.
 
ex.
 
 $Profile.Id <> 00e70000000xxxx
 
hth.
 
Aiden
AdminNewbieAdminNewbie
    Error: Syntax error. Missing ')'

I get this error when i plug in the code.  I have tried adding a ) but to no avail.  This is the code as it is now:

OR(
AND(ISPICKVAL( NOT( Rep_ID__c,"O09")),
AND(ISPICKVAL(Product_List__c, "1.0 M"), Amount<179.00),
AND(ISPICKVAL(Product_List__c, "1.5 M"), Amount<199.00),
AND(ISPICKVAL(Product_List__c, "2.0 M"), Amount<279.00),
AND(ISPICKVAL(Product_List__c, "2.5 M"), Amount<369.00),
AND(ISPICKVAL(Product_List__c, "3.0 M"), Amount<449.00),
AND(ISPICKVAL(Product_List__c, "5.0 M"), Amount<899.00),
AND(ISPICKVAL(Product_List__c, "6.0 M"), Amount<799.00),
AND(ISPICKVAL(Product_List__c, "8.0 M"), Amount<1099.00),
AND(ISPICKVAL(Product_List__c, "10.0 M"), Amount<1399.00),
AND(ISPICKVAL(Product_List__c, "12.0 M"), Amount<1699.00),
AND(ISPICKVAL(Product_List__c, "15.0 M"), Amount<1999.00),
AND(ISPICKVAL(Product_List__c, "20.0 M"), Amount<2499.00),
AND(ISPICKVAL(Product_List__c, "45.0 M"), Amount<2499.00),
AND(ISPICKVAL(Product_List__c, "Covad Wired T1"), Amount<349.00)
)

The error wants me to add a ) after rep_id__c, which i find very strange!

Thanks you SO much, you have really saved my bacon as we say in the UK :)
NPMNPM

You could probaly try

AND(ISPICKVAL( NOT( Rep_ID__c,"O09"))),

 

or go with Alex's' idea (probably better) 

 

 AND($Profile.Id <> 00e70000000xxxx),

 
NPMNPM
Sorry,
Aiden
not Alex.
AMartinAMartin

Hi,

By the way, in your first post about the user id, you had the user id as  009.

In your formula, you had the id as O09.

Not sure which is incorrect, but just thought you might want to verify.

Aiden
AdminNewbieAdminNewbie
    The code is actually O09, but thanks for looking out :)

I am still getting error messages about the ')' its driving me nuts!!  You have all been amazing, thank you for all the help, I really feel like I am a hop skip and a jump away!
AdminNewbieAdminNewbie
    What is the profile id?
NPMNPM

It may be my fault the way I set up the NOT (untested)

try this way with your field and value (tested):

     NOT(ISPICKVAL( Test_Values__c , "None"))
NPMNPM

With the AND of course

AND(NOT(ISPICKVAL( Test_Values__c , "None")),
AMartinAMartin

Every record in sfdc has a 15 digit unique id.  You can see the id in the URL or browser address field when you open the record (account, opportunity, user record, etc).  $Profile.ID refers to the 15 digit id of the specific user profile record (System Admin, General User, etc) that you want to test against.  In your case, it's Manuel's user profile.

Aiden
AdminNewbieAdminNewbie
       I tried both ways and I still get missing ')' even when I add one, I also get INSERT OPERATOR.  Basically its saying I must insert an operator!


Message Edited by AdminNewbie on 04-08-2008 12:28 PM
NPMNPM
Could you copy/paste your formula again so we can see it in it's current form?
tmactmac

Hi,

I know we are talking about Validation Rules here but I was wandering if you know about s-controls also.

I was told that I could use s-controls for the following:

I want the NAICS code to be in the account record and the opportunity record.  However, I want it to populate in the opportunity record after I have entered it into the account or vice versa.  Is this possible with s-control?
NPMNPM
I have no experience with s-controls, maybe someone else following this thread can help.
tmactmac
Thanks
AdminNewbieAdminNewbie
    OR(
AND(NOT(ISPICKVAL( Rep_ID__c  , "O09")),
AND(ISPICKVAL(Product_List__c, "1.0 M"), Amount<179.00),
AND(ISPICKVAL(Product_List__c, "1.5 M"), Amount<199.00),
AND(ISPICKVAL(Product_List__c, "2.0 M"), Amount<279.00),
AND(ISPICKVAL(Product_List__c, "2.5 M"), Amount<369.00),
AND(ISPICKVAL(Product_List__c, "3.0 M"), Amount<449.00),
AND(ISPICKVAL(Product_List__c, "5.0 M"), Amount<899.00),
AND(ISPICKVAL(Product_List__c, "6.0 M"), Amount<799.00),
AND(ISPICKVAL(Product_List__c, "8.0 M"), Amount<1099.00),
AND(ISPICKVAL(Product_List__c, "10.0 M"), Amount<1399.00),
AND(ISPICKVAL(Product_List__c, "12.0 M"), Amount<1699.00),
AND(ISPICKVAL(Product_List__c, "15.0 M"), Amount<1999.00),
AND(ISPICKVAL(Product_List__c, "20.0 M"), Amount<2499.00),
AND(ISPICKVAL(Product_List__c, "45.0 M"), Amount<2499.00),
AND(ISPICKVAL(Product_List__c, "Covad Wired T1"), Amount<349.00)
)

I get INSERT OPERATOR as well as MISSING ')'

Thanks you for your help on this, I really appreciate it :)
NPMNPM

Does this help - I added ) at the end of the first AND line, and closed up some blanks before the comma after Rep_ID__c.

Let's see if there are error messages with this.

    OR(
AND(NOT(ISPICKVAL( Rep_ID__c, "O09"))),
AND(ISPICKVAL(Product_List__c, "1.0 M"), Amount<179.00),
AND(ISPICKVAL(Product_List__c, "1.5 M"), Amount<199.00),
AND(ISPICKVAL(Product_List__c, "2.0 M"), Amount<279.00),
AND(ISPICKVAL(Product_List__c, "2.5 M"), Amount<369.00),
AND(ISPICKVAL(Product_List__c, "3.0 M"), Amount<449.00),
AND(ISPICKVAL(Product_List__c, "5.0 M"), Amount<899.00),
AND(ISPICKVAL(Product_List__c, "6.0 M"), Amount<799.00),
AND(ISPICKVAL(Product_List__c, "8.0 M"), Amount<1099.00),
AND(ISPICKVAL(Product_List__c, "10.0 M"), Amount<1399.00),
AND(ISPICKVAL(Product_List__c, "12.0 M"), Amount<1699.00),
AND(ISPICKVAL(Product_List__c, "15.0 M"), Amount<1999.00),
AND(ISPICKVAL(Product_List__c, "20.0 M"), Amount<2499.00),
AND(ISPICKVAL(Product_List__c, "45.0 M"), Amount<2499.00),
AND(ISPICKVAL(Product_List__c, "Covad Wired T1"), Amount<349.00)
)
NPMNPM

You know, I do not think as is you need the AND for the first line  - that is probably where the enter operator error is coming from.  But there may also be a logic error - The rule will trigger when any of the conditions of each line are true. It will trigger in every case where the rep is not O09, Which I believe is not what you want.  You want the rule to trigger when the rep is not O09 and and any of the other lines are true. 

AND(

NOT(ISPICKVAL( Rep_ID__c, "O09")),

   OR(
          AND(ISPICKVAL(Product_List__c, "1.0 M"), Amount<179.00),
          AND(ISPICKVAL(Product_List__c, "1.5 M"), Amount<199.00),
         AND(ISPICKVAL(Product_List__c, "2.0 M"), Amount<279.00),
         AND(ISPICKVAL(Product_List__c, "2.5 M"), Amount<369.00),
         AND(ISPICKVAL(Product_List__c, "3.0 M"), Amount<449.00),
         AND(ISPICKVAL(Product_List__c, "5.0 M"), Amount<899.00),
         AND(ISPICKVAL(Product_List__c, "6.0 M"), Amount<799.00),
         AND(ISPICKVAL(Product_List__c, "8.0 M"), Amount<1099.00),
         AND(ISPICKVAL(Product_List__c, "10.0 M"), Amount<1399.00),
         AND(ISPICKVAL(Product_List__c, "12.0 M"), Amount<1699.00),
         AND(ISPICKVAL(Product_List__c, "15.0 M"), Amount<1999.00),
         AND(ISPICKVAL(Product_List__c, "20.0 M"), Amount<2499.00),
         AND(ISPICKVAL(Product_List__c, "45.0 M"), Amount<2499.00),
         AND(ISPICKVAL(Product_List__c, "Covad Wired T1"), Amount<349.00)
       )

   )
AdminNewbieAdminNewbie
    YOU ROCK!!  It works just as needed and I am eternally grateful :)  If you lived in San Diego I would take you out for a slap up meal :)

Thank you to everyone who helped with this, I have learnt a lot from this experience and will be a much better formula writer in the future.  It a lot to learn for a psychology major!
NPMNPM

I would love to live in San Diego!  My uncle lives there and occassionally when there is a conference or training we get out there and love it every time.

 

Good job!

 

Best of luck!
AdminNewbieAdminNewbie
    Next time you're here drop me a line :)
tmactmac

Hi!

Do you know if a validation code can be used to make sure that users opportunities start at a particular stage?  My situation is that I need all opportunities to start at the Project Pending Approval Stage.  The users should not be able to alter this stage until the opportunity has been approved.
NPMNPM
I am not familiar with opportunities because our company does not use them, but a first blush it seems like it should be possible for a rule to check the stage and not allow the opportunity to be saved if it is not at that stage and not approved.  I'll look at it in the morning.  In the meantime can you tell me what constitutes approved?  Is it a separate field than stage? 
tmactmac
Thank you for your help!
 
Approved is the stage.  We have it named Approved/Assigned. 
AMartinAMartin

Hi,

This validation should prevent anyone from creating an opportunity at a stage other than "Project Pending Approval".

Not knowing how approvals are done, can't help there.

Might be a good idea to start a new thread on this question.

Aiden

and(
isnew(),
NOT(ISPICKVAL(STAGENAME,"Project Pending Approval"))
)
tmactmac

Thank you Aiden!

I am going to try this validation rule and see how it works with our approval process.
tmactmac

Thanks Aiden it works perfectly!

I have another question...I was looking for a thread on S-controls but cant seem to find one.  Do you know about these?  If I already asked you I apologize.
AMartinAMartin

Basically, S-controls are custom code that you can write in sfdc that will allow you to manipulate data in standard objects (ex. Accounts, Contacts, Opportunities), custom objects or even to pass information from sfdc to other applications.  Customizing a URL to pass information from one webpage (ex Account) to another (ex Contracts) is also considered an s-control.

Here's a brief introductory presentation about scontrols.

https://admin.acrobat.com/_a13852757/customscontrols

Salesforce has a whole site devoted to the development of s-controls and to be honest, I find a lot of it overwhelming. (My programming knowledge is fairly limited. But I can copy and paste other people's work with the best).
 
Here's the link to the developer site. http://www.salesforce.com/developer/
Even if you've decided that you're never going to write an s-control, sign up for an account on this site.  It will give you access to a developer edition of salesforce.com.  Having access to a test version will allow you to test changes to the application before you actually deploy them to your real sfdc.
 
Aiden