Pulling Data From Multiple Related Objects

Controls available in the Get Data dialog can make your life easier by providing you with a view into available tables, table fields, and even picklist values (if you select a field of type ‘picklist’). However, they in no way limit what you can write in the Query box. We support any SOQL query you can throw at Salesforce.com. As a rule of thumb, whatever query you can run from Developer Console – should work in the Get Data dialog (please write to support@xappex.com if you discover a query that is not working), except that you don’t get a developer console if you are not an admin or if you’re on a Professional or Group Edition of Salesforce. But you can always run queries and do mass updates/inserts/deletes.

In the Get Data dialog you are not limited to simple queries like:

SELECT field1, field2, ... FROM Account WHERE CreatedDate < 2012-12-12

You can select multiple levels of relationship fields and they will all come back in a flat table:

SELECT name, id, Account.Name, Account.Owner.Name FROM Opportunity

Up to 7 levels of related tables can be pulled in this way. The most important thing to remember when using related fields is that you always need to start with the lowest object in the hierarchy to be able to pull in all data. For example, in a hierarchy of Account -> Opportunity -> Activity, you will need to pull from the Event object (which represents Activity in SFDC) and work your way up to the Account as follows:

SELECT name, id, Opportunity.Name, Opportunity.StageName, Opportunity.Account.Name, Opportunity.Account.Owner.Name FROM Event

You can even do nested selects to pull fields from a related list on your object:

SELECT Name, Id, (Select Pricebook2Id, Product2Id From PricebookEntries), (select id,name from assets) From Product2

And for custom objects:

SELECT id, name, (SELECT id, name FROM MyCustomObjects__r) FROM Contact

You can do nested selects in your WHERE clause as well:

SELECT Name, Id FROM Opportunity WHERE AccountId IN (SELECT Id FROM Account WHERE RecordType = 'My Account Record Type')

Notes:

1. Replace __c with __r to indicate a relationship, don’t use __c__r
2. In the event that the name of the relationship doesn’t match the name of the object you’re relating to, use the name as it is in the primary object used for the query, not the ‘true’ object name. For example, when MyCustomObject references the Account object as “Client”, use client__r not account__r:
   SELECT field1, field2, client__r.name FROM MyCustomObject__c
3. If you are pulling a related list of custom objects, you need to replace __c with __r in the object name, for example:
   SELECT id, name, (SELECT id, name FROM MyCustomObject__r) FROM Contact
   In fact, it’s the relationship name that you need to use, so if your relationship name is in plural, your query will look as follows:
   SELECT id, name, (SELECT id, name FROM MyCustomObjects__r) FROM Contact