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
CFlevarisCFlevaris 

Cross-object Reference

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
indicatorindicator
What is the relationship between the Account object and the Contract object?  Is there a lookup from Contract to Account?  If so, how will salesforce determine which Contract you are requesting fields from?
CFlevarisCFlevaris

Excellent question... I understand in SFDC we can have several contracts listed under the accounts object.  I only plan on having one.  I can take the one field I need from CONTRACT and make it a separate field inside ACCOUNT (it would be the same for every asset I'd add), but each asset will only have contract's terms apply to it.  In my case, to make it easier, there will only be one contract per account.

I do not have any look up relationships between Contract, Account, and Asset, only that they are all related to Account. 

Does that answer your question?  Does that make it easier to suggest something or would I have to do something drastic to implement this?

Thanks,
Constantine



Message Edited by CFlevaris on 06-18-2008 11:46 AM
werewolfwerewolf
You'll have to add a lookup relationship from Asset to Contract to indicate what contract the asset is covered under.  Then your formula field will be trivial -- when that lookup is in place you can just reference rows from the associated Contract.

You can't use Account as an intermediate because Account is many-to-many to both Contract and Asset -- formulas won't work that way.  But a straight up lookup from Asset to Contract will do the trick.
CFlevarisCFlevaris

That's great!  I see it was a matter of discussion before:

http://ideas.salesforce.com/article/show/22283/Lookups_from_standard_objects_to_standard_objects

But I can't find a page that tells me how to do it, nor can I figure out exactly how to do it myself on Setup.

Do you have a document describing it or can you tell me how to get it done?
werewolfwerewolf
Just make a custom field on the Asset object, make its type a Lookup Relationship, and select Contract as the object you're looking up to.
CFlevarisCFlevaris

Perfect!  Now when I import asset information, I'll make sure to reference the default contract and make special considerations for the specific accounts with different relationships.

Another question, probably also easy to answer I hope: How do I reference a field in the related contract?  It's currently:

IF( Expiration_Date__c > TODAY() , "Yes", "No")

And Expiration_Date__c is a field in Asset.  After referencing the contract, is it something like:

IF( CONTRACT.M_S_Expiration__c > TODAY() , "Yes", "No")

where M_S_Expiration__c is the field in Contract I have to reference?  Or is there another reference convention?

Thanks for all your help!!!

Constantine
werewolfwerewolf
Almost.  You have to reference it by the API name of the field you create.  So if the lookup field you just created on Asset is called Contract, then your formula reference would be like Contract__c.M_S_Expiration__c.
CFlevarisCFlevaris

I got it to work as you said!  Only that when I inserted the field it came out:

Contract__r.M_S_Expiration__c

Thanks a lot!
MarkTNMarkTN
Thank you!  This has gotten me rolling again!
 
Mark
rockchick322004rockchick322004
Thanks, werewolf, for the help with this issue.  I also want to point out that all of the merge fields are available when you click the  Insert Field button in the Formula Editor -- this opens a multi-pane overlay that lets you navigate through all of the relationships that exist for the object you are one.  This way you do not need to know the api name or the relationship name (contract__r is the relationship name).  All of the Master-Detail fields and Lookup fields on the object will show up in the Insert Field overlay as both the field label and also the field label with a > after it -- click on that to navigate to the fields on that related object.

Note that you cannot reference self-relationships (for example, if you create a relationship from Contract to Contract, you will not be able to reference the fields on the parent Contract from the child Contract).