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
bmartinez76bmartinez76 

Validation help-VLookup

Hello, I've done quite a few validation rules, but I'm no expert and I'm currently stumped.  In our CASE object we have a custom picklist field which has analyst names.  The field is changed to the analyst that the case is being assigned to.  so lets say a issue is reported to our support center. A case is created by the helpdesk then they select the person from the L3 Analyst field to escalate the issue to.  The level 1 analyst saves the record and clicks on change owner to assign to the analyst selected for L3 .  This cause problems as sometimes the L3 analyst is on vacation or out sick.  I'm trying to create validation that if L3_Analyst_Assigned__c is equal to "John Doe" it checks a custom object i created to store a check box named John Doe.  If the check box is true then I want the validation rule to go off and tell the level 1 analyst that the person they are trying to assign the case to is not available.
 
 
I saw the VLOOKUP feature but to be honest with you I have read just about every post but i can't get it to work.  Any assistance would be greatly appreciated.  There might be a different way to accomplish my goal, but so far this is what I have been able to come up with:
 
AND(ISPICKVAL( L3_Analyst_Assigned__c , "John Doe"),

VLOOKUP( $ObjectType.Support_Analyst_Availability__c.Fields.John_Doe__c , $ObjectType.Support_Analyst_Availability__c.Fields.Name , ***not sure what to put here*** ))
 
John_Doe__c in the support analyst object is a checkbox.


Message Edited by bmartinez76 on 06-26-2008 02:00 PM
bmartinez76bmartinez76
So, I came up with a solution that works but might be a little redundant.  I created a look up relationship field in case relating it to the user object.  I created a work flow that updates the look up field with the users id that was selected from the pick list.  So if the pick list name was John Doe it would auto populate the look up field with John Doe's user ID.  I then created this validation rule.

AND(ISPICKVAL( L3_Analyst_Assigned__c , "John Doe"), ( John_Doe__r.Unavailable__c ))

Using this setup I was able to get salesforce to stop a user from changing the case owner to someone who had the unavailable field checked in their user id record.  The only problem is that Salesforce apparently has a limit of 5 cross object validation rules and we have 8 analysts to apply this this validation.  Does any one know if this can be overriden? Or can anyone assist in possibly simplifying what i've done?  Thank you in advance.
MarkTNMarkTN
This may be over simplifing your question, but aren't you just wanting to know if the analyst is unavalable or not?  If so, it seems you only need to link to the user object and validate that the Unavailable__c is false for any analyst that is selected.  This way, you don't need to validate on each analyst, but only on your custom field.