+ Start a Discussion
haripriya.maturiharipriya.maturi 

VLOOKUP with checkbox

Hi,

 

I have a check box in one of my object and its functioanlity is, if a record is created with that check box "checked", no other records can be created with that checkbox "checked" , need to show an error that " other record has been checked "

 

So I have written a validation rule as below :

 

AND(VLOOKUP($ObjectType.xenonproto__Level2__c.Fields.xenonproto__Goal_Level2__c , $ObjectType.xenonproto__Level2__c.Fields.Name , Name)==True,
VLOOKUP($ObjectType.xenonproto__Level2__c.Fields.Id , $ObjectType.xenonproto__Level2__c.Fields.Name , Name) != Id)

 

 

Note: here Goal_Level2__c is of type checkbox.

 

but the above validation is not working for me.

 

Please anyone suggest me.

 

Thanks and Regards,

Haripriya.

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

I don't think VLOOKUP() is going to be able to do what you need it to do here. All VLOOKUP() does is fetch a single record -- based on a pased in value -- of a certain object type, and return a value from some other field on that record. It doesn't check multiple records. For example, you could have a Zip Code object where the name is a 5-digit zip code, and return the State field -- but you would be passing in a single zip code and returning a value from a single record.

 

It sounds like what you want to do is loop through all records to see if they have a certain field checked, and return true if any one of them does. You're going to need to use a trigger for that.

 

More help on VLOOKUP(): http://help.salesforce.com/help/doc/en/customize_functions_i_z.htm#VLOOKUP

All Answers

Suresh RaghuramSuresh Raghuram

AND(VLOOKUP($ObjectType.xenonproto__Level2__c.Fields.xenonproto__Goal_Level2__c , $ObjectType.xenonproto__Level2__c.Fields.Name , Name)==True,
VLOOKUP($ObjectType.xenonproto__Level2__c.Fields.Id , $ObjectType.xenonproto__Level2__c.Fields.Name , Name) != Id)

 

 

I think In red text there is no need of == single = is enough since it is not a if condition in OOPS program.

Shannon HaleShannon Hale

I don't think VLOOKUP() is going to be able to do what you need it to do here. All VLOOKUP() does is fetch a single record -- based on a pased in value -- of a certain object type, and return a value from some other field on that record. It doesn't check multiple records. For example, you could have a Zip Code object where the name is a 5-digit zip code, and return the State field -- but you would be passing in a single zip code and returning a value from a single record.

 

It sounds like what you want to do is loop through all records to see if they have a certain field checked, and return true if any one of them does. You're going to need to use a trigger for that.

 

More help on VLOOKUP(): http://help.salesforce.com/help/doc/en/customize_functions_i_z.htm#VLOOKUP

This was selected as the best answer
haripriya.maturiharipriya.maturi

Thanks Shannon Hale.....Created a trigger to achieve this functionality. Its working fine.