Apex Code Development You need to sign in to do that
Don't have an account?
sparky new VLOOKUP function can't use object IDs?
I'm trying to create a validation rule using the new VLOOKUP function. The idea is:
* Registrations are detail-master to Programs
* Faimly Groups are also detail-master to Programs
* Registrations has lookup relationship to Family Groups
* want to limit so that you can only choose a Family Group that belongs to the same Program that the Registration belongs to
So basically this is a workaround to get around the lack of filtered lookups.
I tried writing the validation rule to look like this:
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.
* Registrations are detail-master to Programs
* Faimly Groups are also detail-master to Programs
* Registrations has lookup relationship to Family Groups
* want to limit so that you can only choose a Family Group that belongs to the same Program that the Registration belongs to
So basically this is a workaround to get around the lack of filtered lookups.
I tried writing the validation rule to look like this:
Code:
NOT ( VLOOKUP( $ObjectType.Family_Group__c.Fields.Program__c ,
$ObjectType.Family_Group__c.Fields.Id , Family_Group__r.Id ) = Program__r.Id )
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.
I just setup a cross object formula field that I think will give you what you need. Here's what I did:
1. Added a new lookup field on the Account object that looks up back to the Account
2. Created a new formula field (text) on the Opportunity object that pulls the Account Name (partner) for the Account related to the Account.
Field names for my example:
Account Object -> Partner Lookup Account, (Lookup to Account)
Opportunity Object -> Partner Account Name, (Formula, Text).
Formula: Account.Partner_Lookup_Account__r.Name
By using the drill-though capabilities of the formula builder, I as able to drill through the Opportunity to the Account to the "Partner" Account related and return the name.
All Answers
I filed a doc bug and this should be fixed next week. Thanks for pointing this out!
For now, here are the tips for VLOOKUP:
* The field_to_return must be an auto number, roll-up summary, lookup relationship, master-detail relationship, checkbox, date, date/time, email, number, percent, phone, picklist, text, text area, or URL field type. [NOTE: long text area is not supported, that was also a doc bug]
* The field_on_lookup_object must be the Record Name field on a custom object.
* The field_on_lookup_object and lookup_value must be the same data type.
* If more than one record matches, the value from the first record is returned.
* The value returned must be on a custom object.
* You cannot delete the custom field or custom object referenced in this function.
This is an issue for my organization as well. I created a custom object and Zip Code to validate that the zip code resides in the correct city, state and county. My issue is that I need to also validate County against State. We have exceptions to allow a contact to be moved to a different state and county, even if his address is another state or county so that Contact will be "counted" as a valid record for mulitple states and counties even when he only has one physical location.
According to these posts, I would have to create an additional custom object to validate County to State since my current custom object has Name tied to Zip Code and I need it to be Name of either County or State to accomplish my goal. This wastes valuable storage space in my instance due to amount of data we are capturing.
It seems logical that as long as my Name field on the custom object is referenced that I should be able to use any other custom field in that object to validate against any other field on other objects?
Any thoughts or suggestions are greatly appreciated,
Mark
"The value returned must be on a custom object"
does that mean that if I have a custom object that has a lookup relationship with the Account object, and I want to have VLookup return the value of a custom field within Account to feed a formula field in the custom object, that it can't be done because Account is a built-in object and not a custom object?
All I'm trying to do is have a critical custom field (Account Manager) in Account get added as a formula field in my custom object, that has a lookup relationship with Account, so that I can show the Account Manager in list views in my custom object. If this can't be done, is there a different or better way to get around this limitation?
Thanks,
Michael
As for the VLOOKUP function, yes, it would have to be a custom object that is used as the source of the VLOOKUP. It was primarily designed to validate typical lookup data -- for example, validate a zip code, state, etc.
Mark
Hi Matthew,
I have a similar requirement - did you ever find a good solution?
Rgds,
Per
Checking to see if there has been any updates to this...
What I am trying to solve for...
I have a custom lookup field in the accounts to lookup account name (partner name). I want this field to be populated in the Opportunity as well. When I used a cross-object reference to populate this value instead of giving me the my partner account name it gives the account id. Vlookup of this id fails.. due to the above reason. If there is a alternate way to do the same please let me know.
I just setup a cross object formula field that I think will give you what you need. Here's what I did:
1. Added a new lookup field on the Account object that looks up back to the Account
2. Created a new formula field (text) on the Opportunity object that pulls the Account Name (partner) for the Account related to the Account.
Field names for my example:
Account Object -> Partner Lookup Account, (Lookup to Account)
Opportunity Object -> Partner Account Name, (Formula, Text).
Formula: Account.Partner_Lookup_Account__r.Name
By using the drill-though capabilities of the formula builder, I as able to drill through the Opportunity to the Account to the "Partner" Account related and return the name.