+ Start a Discussion
Roshan TamrakarRoshan Tamrakar 

VLOOKUP to validate checkbox field type

Hi Experts,

I have a custom object SP__c having a checkbox field named 'Apply_To_All__c' and a text field P1__c. The standard object 'Account' has a lookup field to this custom object (Account.SP__c). Only SPs that have 'Apply_To_All' checked can be set as a lookup SP for an Account.

Name        SP__c
Sonixx        Standard
Beltronix     Custom (This is invalid as 'Apply_To_All__c' is false for Custom SP)
Airtel           Standard

Name           Apply_To_All__c        P1__c
Standard      True                           Critical
Custom        False                          Normal

To achieve this, I wrote the following validation rule in Account:

VLOOKUP($ObjectType.ENT_2__SP__c.Fields.ENT_2__Apply_To_All__c, $ObjectType.ENT_2__SP__c.Fields.Name, 'True')=True

(ENT__2 is the namespace prefix)

However, this validation is allowing me to save no matter the lookup SP has 'Apply_To_All__c' checked or unchecked.

I even could not validate for attaching only SPs that have P1__c='Critical' with the following rule:

VLOOKUP($ObjectType.ENT_2__SP__c.Fields.ENT_2__P1__c, $ObjectType.ENT_2__SP__c.Fields.Name, 'Critical')='Critical'

What is wrong in my validation rule?

Roshan TamrakarRoshan Tamrakar
Hi All,

I think I solved the issue. By using 'Insert Field' button, I came to the following rule:

ENT_2__SP__r.ENT_2__Apply_To_All__c = False

 and it worked!

Please confirm, if this is correct.

Considering above is correct, now I would like a parallel validation in SP__c too so that the SP, that is a lookup of an Account, cannot be modified for 'Apply_To_All__c' =False. For that I need to write another validation rule in SP__c where it needs to see if this SP has any child Account or not. If so, the SP cannot be saved if 'Apply_To_All__c' is unchecked.

But I didn't find any function that gives me no. of child records.

I think, I am pushing 'validation rule' a little harder. Is it bad to use validation rule for complex validation? Am I supposed to use 'trigger' for complex validation instead of 'validation rule'?

Please suggest.

Yes, what you're doing is correct -- you don't need VLOOKUP here because the Account will be looking up directly to the Support Program, so you can reference it directly as you have done.