This article shows you how you access data from a related record ( __r
) in Salesforce. In our example, we have created a custom field of type Lookup Relationship
in the Product2 table. The custom field is called "AccRel".
Using SOQL in the Salesforce Developer Console, we can run the following query:
select ProductCode, Name, AccRel__r.Name from Product2 where ProductCode='ETP001'
To query the data by using the Salesforce ODBC Driver, you first need to work out which table the relationship is pointing to. You can find this within Salesforce, if you choose Setup > Customize > Table > Fields where Table is the name of your table that contains the link.
In our example, we have one API column called "AccRel__c"
and that uses a Lookup(Account), so we know the Account table is involved. This means that the query you need to use with the Salesforce ODBC Driver, is now translated to:
select ProductCode as "Product Code", Product2.Name as "Product Name", Account.Name as "Account Name" from Product2, Account where AccRel__c=Account.Id and ProductCode='ETP001'
as "Product Code"
renames the column in the query results. The two key parts to the relationship are from Product2, Account
. You need to include both tables in the query's FROM clause. You also need to join the tables. In our case, this was done in the WHERE clause: AccRel__c=Account.Id
.
Again, using our sample data, the query produces these results, when run by using the Salesforce ODBC driver:
Product Code,Product Name,Account Name ETP001,Easysoft Test Product,Edge Communications2014