+ Start a Discussion
Luciano Castro 2018Luciano Castro 2018 

Validation Rule on lookup based on a multi-select

I am trying to create a validation rule to enforce users to fill out a lookup field based on a multi-select. Here is the trick

Multi-Select has 3 options(A,B,C) and we have 3 different lookups called A,B,C

If I select A from the picklist then I have to fill out lookup A
If I select B then fill out lookup B
If I select C then fill out lookup C 

However,

if I select A,B fill out lookup A,B
if I select A,C - then lookup A, C
if I select B,C - then lookup B,C 

Any thoughts how I can write this validation?

 
Best Answer chosen by Luciano Castro 2018
Mahesh DMahesh D
Hi Luciano,

Try this:
AND( C_S_Status_Evaluation_Request__c = true ,
OR(AND(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS')),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS'))),Referred_LS_Sales_Rep__c <> ''), 
AND(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR')),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR') )),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> '')))

Regards,
Mahesh

All Answers

Mahesh DMahesh D
Hi Luciano,

Please find the Validation Rule:
 
OR(AND(INCLUDES(Mul_Picklist__c, 'A'),ISBLANK(Account_A__c)),
AND(INCLUDES(Mul_Picklist__c, 'B'),ISBLANK(Account_B__c)),
AND(INCLUDES(Mul_Picklist__c, 'C'),ISBLANK(Account_C__c)))

I already tested it in my DE environment and everything looks good.

Please do let me know if it helps you.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
Mahesh, 

That worked. One last question what if my boss wants to include more options later in the future?

Do I just add them separating by commas like the one below?

OR(AND(INCLUDES(Mul_Picklist__c, 'A','A1',A2'),ISBLANK(Account_A__c)),
AND(INCLUDES(Mul_Picklist__c, 'B','B1',B2'),ISBLANK(Account_B__c)),
AND(INCLUDES(Mul_Picklist__c, 'C','C1',C2'),ISBLANK(Account_C__c)))

Once again thank you 
Mahesh DMahesh D
Hi Luciano,

We can achieve this in multiple ways:

One of the way is below:
 
OR(AND(INCLUDES(Mul_Picklist__c, 'A'),ISBLANK(Account_A__c)),
AND(INCLUDES(Mul_Picklist__c, 'A1'),ISBLANK(Account_A__c)),
AND(INCLUDES(Mul_Picklist__c, 'A2'),ISBLANK(Account_A__c)),
AND(INCLUDES(Mul_Picklist__c, 'B'),ISBLANK(Account_B__c)),
AND(INCLUDES(Mul_Picklist__c, 'C'),ISBLANK(Account_C__c)))

Please do let me know if it helps you.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
I am receiving an error Manesh

OR(AND(INCLUDES(CS_Type__c,'Cross Selling LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Exclusive CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id)),
AND(INCLUDES(CS_Type__c,'Preferred CR',ISBLANK(Referred_CR_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__r.Id ))))

 Error: Incorrect number of parameters for function 'INCLUDES()'. Expected 2, received 3
Mahesh DMahesh D
Hi Luciano,

Please find the corrected one:
 
OR(AND(INCLUDES(CS_Type__c,'Cross Selling LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Exclusive CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id)),
AND(INCLUDES(CS_Type__c,'Preferred CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__r.Id )))

Please do let me know if it helps you.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
Mahesh

It is awesome how it works but there is only one thing I found in this validation rule

I selected Cross Selling LS and Exclusive CR  It asked to fill out the LS and CR lookup field which is great, but also if I fill out the MS lookup field, it lets me save. I understand the validation rule is working because the other 2 fields are already populated. 

Not sure how far a validation rule will go and maybe another validation rule needs to be created or a trigger but it would be nice to get an error message if I fill out the MS lookup field and the Referral MS is not selected then it should give an error.
 
Mahesh DMahesh D
Hi Luciano,

Please check the modified one:
 
OR(AND(INCLUDES(CS_Type__c,'Cross Selling LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )),
AND(NOT(INCLUDES(CS_Type__c,'Cross Selling LS')),Referred_LS_Sales_Rep__r.Id <> ''),
AND(INCLUDES(CS_Type__c,'Referral LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Exclusive CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id)),
AND(INCLUDES(CS_Type__c,'Preferred CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )),
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__r.Id )))

I added for the first option (Cross Selling LS) only. You can change it accordingly.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
Am I doing something wrong? It didnt give any errors but it is not working at all now. 

I cant save anything :( 

OR(AND(INCLUDES(CS_Type__c,'Cross Selling LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )), 
AND(NOT(INCLUDES(CS_Type__c,'Cross Selling LS')),Referred_LS_Sales_Rep__r.Id <> ''), 
AND(INCLUDES(CS_Type__c,'Referral LS'),ISBLANK(Referred_LS_Sales_Rep__r.Id )), 
AND(NOT(INCLUDES(CS_Type__c,'Referral LS')),Referred_LS_Sales_Rep__r.Id <> ''), 
AND(INCLUDES(CS_Type__c,'Exclusive CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id)), 
AND(NOT(INCLUDES(CS_Type__c,'Exclusive CR')),Referred_CR_Sales_Rep__r.Id <> ''), 
AND(INCLUDES(CS_Type__c,'Preferred CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )), 
AND(NOT(INCLUDES(CS_Type__c,'Preferred CR')),Referred_CR_Sales_Rep__r.Id <> ''), 
AND(INCLUDES(CS_Type__c,'Referral CR'),ISBLANK(Referred_CR_Sales_Rep__r.Id )), 
AND(NOT(INCLUDES(CS_Type__c,'Referral CR')),Referred_CR_Sales_Rep__r.Id <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__r.Id)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__r.Id <> ''))

 
Mahesh DMahesh D
Hi Luciano,

Please check the below code:
 
OR(AND(INCLUDES(CS_Type__c,'Cross Selling LS'),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(INCLUDES(CS_Type__c,'Cross Selling LS')),Referred_LS_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral LS'),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(INCLUDES(CS_Type__c,'Referral LS')),Referred_LS_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Exclusive CR'),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Exclusive CR')),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Preferred CR'),ISBLANK(Referred_CR_Sales_Rep__c )), 
AND(NOT(INCLUDES(CS_Type__c,'Preferred CR')),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral CR'),ISBLANK(Referred_CR_Sales_Rep__c )), 
AND(NOT(INCLUDES(CS_Type__c,'Referral CR')),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> ''))

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
I am really sorry Mahesh but it does not work. There is no error on syntax but when I go to the record it does not work at all. 
I choose Cross Selling LS and Referral CR , I fill out the LS lookup and CR lookup and it gives me the validation rule error
Luciano Castro 2018Luciano Castro 2018
User-added image
Mahesh DMahesh D
Hi Luciano,

I didn't understand your test scenario.

Here you selected Crosee Selling LS and Referred LS Sales Rep Selected --- which is correct.
         you selected Referral CR and Referred CR Sales Rep selected --- which is correct.

Hence there is no error message.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
No Mahesh

I selected Cross Selling LS and Referred CR (not LS)  then I filled out the LS and CR lookup field . It was supposed to save but it did not save
Mahesh DMahesh D
Hi Luciano,

Here is your Validation Rule:
 
OR(AND(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS')),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS'))),Referred_LS_Sales_Rep__c <> ''), 
AND(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR')),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR') )),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> ''))

As I don't have full setup here I can't test this but it looks like a complicated Validation Rule. :( :).

Please do let me know if it helps you.

Regards,
Mahesh

 
Luciano Castro 2018Luciano Castro 2018
Good Morning Mahesh, 

Thanks for all your help, it works my friend.  
This is insane :)   
What if I want this whole thing to work only if a checkbox gets checked. Is that possible?
Luciano Castro 2018Luciano Castro 2018
Do I add in the beggining 

IF(checkbox__c = TRUE) &&
the rest of the validation?
Mahesh DMahesh D
Hi Luciano,

Please check the modified validation rule. Hope this will be your last requirement for this Validation Rule.
 
IF(Checkbox__c, OR(AND(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS')),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS'))),Referred_LS_Sales_Rep__c <> ''), 
AND(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR')),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR') )),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> '')), false)

Please do let me know if it helps you.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
Thanks Mahesh, 

The validation rule does show any errors but when I go to the record and make the checkbox to true, it lets me save without the other fields being populated. 

If I check the checkbox and I dont have those other fields populated then it should throw the error
Mahesh DMahesh D
Hi Luciano,

You can try this:
 
AND(Check_Box__c, OR(AND(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS')),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS'))),Referred_LS_Sales_Rep__c <> ''), 
AND(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR')),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR') )),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> '')))

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
I am sorry Mahesh but the validation rule did not fire. I checked the CS Status Evaluation and it saved the record instead of throwing the error.


AND( C_S_Status_Evaluation_Request__c ,
OR(AND(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS')),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS'))),Referred_LS_Sales_Rep__c <> ''), 
AND(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR')),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR') )),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> '')))
Luciano Castro 2018Luciano Castro 2018
If I can get this to work, it would be awesome !
Mahesh DMahesh D
Hi Luciano,

Try this:
AND( C_S_Status_Evaluation_Request__c = true ,
OR(AND(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS')),ISBLANK(Referred_LS_Sales_Rep__c )), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Cross Selling LS'), INCLUDES(CS_Type__c,'Referral LS'))),Referred_LS_Sales_Rep__c <> ''), 
AND(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR')),ISBLANK(Referred_CR_Sales_Rep__c)), 
AND(NOT(OR(INCLUDES(CS_Type__c,'Exclusive CR'), INCLUDES(CS_Type__c,'Preferred CR'), INCLUDES(CS_Type__c,'Referral CR') )),Referred_CR_Sales_Rep__c <> ''), 
AND(INCLUDES(CS_Type__c,'Referral MS'),ISBLANK(Referred_MS_Sales_Rep__c)), 
AND(NOT(INCLUDES(CS_Type__c,'Referral MS')),Referred_MS_Sales_Rep__c <> '')))

Regards,
Mahesh
This was selected as the best answer
Luciano Castro 2018Luciano Castro 2018
Bad News :(  Still does not work. It is still letting me save without throwing the error message

Tough Validation Rule right Mahesh. If we get this to work, it will go to my Hall of Fame. 

 
Luciano Castro 2018Luciano Castro 2018
You know what is funny.. The validation rule is behaving as if the string: AND( C_S_Status_Evaluation_Request__c = true  was not there. 
Mahesh DMahesh D
Hi Luciano,

Actually I tested it in my environment and it is working fine.

Not sure what is happening on your side, can you paste some screenshots.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
User-added image
If you see on this example, it allowed me to save the record withouth throwing the error. 
So if I check the CS Evaluation checkbox and if the  CS Type and the lookups are blank,  it should give me an error
Mahesh DMahesh D
Hi Luciano,

Please provide me your Email Id and will connect through Email and resolve the issue.

Regards,
Mahesh
Luciano Castro 2018Luciano Castro 2018
luciano.castro@dtiglobal.com
Luciano Castro 2018Luciano Castro 2018
Look forward to hear from you Mahesh