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.

Hello!

 

I cannot find anything in your help pages referring to this so I logged a case. They couldn't answer, they said the question required a hire level of expertise, and suggested I post here. 

I would like to know how to reference a field in another object record within the same account.

I have a formula in a custom field that is calculated to give value to one of my fields in the ASSET object. It figures out that if the expiration date for an asset is greater than TODAY() then it is covered under the support agreement, else it's not. This is the formula:
IF( Expiration_Date__c > TODAY() , "Yes", "No")

That's great, but the expiration dates that I want to upload will not be manually entered in the asset.

There will be a field in a contract that defines the expiration date

We have an object called CONTRACT where these details are uploaded.  Within the same account (that owns the asset) I want to use the expiration date defined in a field in the contract record.

 

I want to grab the value of Expiration_Date_c in a CONTRACT record if there is not one in Expiration_Date_c in the ASSET record and execute my IF statement.

In the forumula above, Expiration_Date_c comes from the ASSET object not the CONTRACT object within the same account. I want to connect the two in a new formula.
That formula should read, in pseudo code:

IF ASSET.Expiration_Date_c != <Blank> then
IF( ASSET.Expiration_Date__c > TODAY() then Yes else No)
Else
IF( CONTRACT.Expiration_Date__c > TODAY() then Yes else No)
Else
No


How can I do this?
Thanks,
Constantine

 However, this gives me an error of "Incorrect parameter for function VLOOKUP(). Expected Record Name field." which is rather inscrutable, I'm sure you'll agree.

From experimentation, I've found that it works if I replace those Id fields with Name fields.  However, I don't want to do that, as Family Group names are not necessarily unique.

Why doesn't it work with the Id's?  Is there some reason that this function won't accept Id fields as parameters?  If so, is that something that could be changed?  This is probably the most important potential use of this function in validation rules.

BTW, the documentation is not clear on this.  The only thing it says is "The field_on_lookup_object must be an indexed field."  But I can't find anywhere what constitutes an Indexed Field in SFDC, so that's not too helpful.

Thanks for any help!

M.