3

I have two picklist fields:

  1. Customer Management

  2. Account Type

If Account Type = "Client" or "Client - Group Contract", Customer Management must not be left blank.

If Account Type = "Prospect" or "Provider", then Customer Management must be left blank.

I know how to do these rules separately, but not together.

AND(
OR(
ISPICKVAL( Type , "Client"),
ISPICKVAL( Type ,  "Client - Group Contract"),
ISPICKVAL( Type , "Provider"),
ISPICKVAL( Type ,  "Prospect"),
ISBLANK( TEXT( Customer_Management__c ))))

I know there should be a NOT function in there somewhere, but I'm not sure where.

  • Unfortunately, I haven't gotten that far. I can do them individually (two types= must have blank client management, two types=must not have blank client management), as two separate validation rules, but I'm struggling to put them together as a single validation rule. I've tried IFs, ISPICKVALs, ANDs, ORs, and still haven't figured it out. So I'm stuck at the beginning, I'm afraid. – user34868 Sep 2 '16 at 13:11
  • 1
    Please take a look at the help center and read How to Ask. Your questions will receive wildly different answers when you have no example than when you show what you have tried. The community's perception of your level of effort put in so far will also be affected. I recommend you edit your post to include your current attempts, and specifically why they did not work. – Adrian Larson Sep 2 '16 at 13:13
  • You want to include both scenarios in a single validation rule.@Esil – Pavan tej Sep 2 '16 at 14:31
  • Exactly, @pavan tej. – user34868 Sep 2 '16 at 14:51
9

You should be able to piece this one together. Here are the functions you'll need:

  • OR(logical1, logical2...) and replace any number of logical references with the expressions you want evaluated.
  • AND(logical1, logical2...) and replace any number of logical references with the expressions you want evaluated.
  • ISPICKVAL(picklist_field, text_literal) and replace picklist_field with the merge field name for the picklist; replace text_literal with the picklist value in quotes. text_literal cannot be a merge field or the result of a function.
    • For a blank picklist, you want to use "" for the text_literal.
  • NOT(logical) and replace logical with the expression that you want evaluated.

You should be able to compose the above into one working expression! I recommend you make an attempt, and you will learn more from that than from someone posting something for you to copy-paste.

In plain English your rule should read something like: Throw an error either when Type is X or Y and Customer Management is blank, or when Type is A or B and Customer Management is not blank.


Now that you've added your attempt to the question, I feel better about showing you how I would write it. I do think it might be better to split into separate validations. You'll give your end users clearer error messages. That said, here are the two separate validations you want to perform. If you want to use just one rule, you would do OR(validation1, validation2).

Enforce Not Blank

AND(
    OR(
        ISPICKVAL(Type, "Client"),
        ISPICKVAL(Type,  "Client - Group Contract")
    ),
    ISPICKVAL(Customer_Management__c, "")
)

Enforce Blank

AND(
    OR(
        ISPICKVAL(Type, "Provider"),
        ISPICKVAL(Type,  "Prospect")
    ),
    NOT(ISPICKVAL(Customer_Management__c, ""))
)
| improve this answer | |
  • 2
    +1, you will learn more from that than from someone posting something for you to copy-paste – martin Sep 2 '16 at 13:11
  • 1
    "You should be able to compose the above into one working expression! I recommend you make an attempt, and you will learn more from that than from someone posting something for you to copy-paste." Very true, thank you. – user34868 Sep 2 '16 at 13:18
  • 1
    @Adrian Larson I was mostly answering about the "Does it work on picklists". My curiosity was triggered ! – Simon Govaert Sep 2 '16 at 13:19
  • 1
    As I said, you need to use OR(validation1, validation2). So replace validation1 with the formula from the first validation, and replace validation2 with the formula from the second validation. – Adrian Larson Sep 2 '16 at 16:07
  • 1
    Perhaps I should learn to read before learning to code :/ Thanks again. – user34868 Sep 2 '16 at 16:38
1

Hi @ESil i don't think so it is not a best practice because we need to throw an error to user if data is not accurate.Because you specified more than one condition in your requirement.But you can't specify different error messages on different conditions.

But for your purpose i'm sharing my validation rule with my fields.Please change it to your requirement accordingly

OR(
    IF(
        ISPICKVAL(StageName, "Closed Won") || ISPICKVAL(StageName, "Closed Lost"),
    ISPICKVAL(Plan_Details__c, "") , null),
    IF(
        ISPICKVAL(StageName, "Need Analysis") || ISPICKVAL(StageName, "Prospecting"),
        NOT(ISPICKVAL(Plan_Details__c, "")),
        null
    )
)
| improve this answer | |
  • @Adrian Larson put a lot of time and effort into this question (thanks Adrian!), but this answer provided a solution to my problem (thanks Pavan!), while Adrian's didn't at first, even though his input has been more valuable in some ways. – user34868 Sep 2 '16 at 16:21
  • 1
    @ESil notice that the helptext on the accept arrow says Click to accept this answer because it solved your problem or was the most helpful in finding your solution (click again to undo). Note that it can be the one you found most helpful. See also: How does accepting an answer work?. That article has some great guidance. You should accept whatever answer you want! – Adrian Larson Sep 2 '16 at 16:33
  • 1
    That said, I think mixing OR and || leads to lower legibility standards, which was my main reason for not liking this answer. I do think that using IF functions here is also more likely to confuse you, and not really help you build your own validation formulas in the future. IMO the question you should ask yourself is, if you had to extend this strategy to a slightly different situation down the road, could you implement it from scratch? Or is it just a recipe for copy pasta? – Adrian Larson Sep 2 '16 at 16:34
  • 1
    Hey sorry for delay @AdrianLarson I didn't think about using same multiple conditions in a single validation rule.I just wrote the above rule with my own knowledge and moreover i want to include both the scenarios in a single rule.But thanks i learned one more best practice from you.And moreover i learned so many things from your answers you teach me so many things thanks once again. – Pavan tej Sep 2 '16 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy