+ Start a Discussion
gregusagregusa 

SOQL Query to pull Account Name via relationship to custom object.

I have the following query that works just like I want it:

 

 

Select a.Status__c, a.Notes__c, a.Id, a.Contracted_As__c, a.Confirm_Date__c, a.Commission_Level__c, a.Agent__c, a.Account__c From Appointment__c a WHERE Status__c = 'Active' AND RecordTypeId = '012400000005AKK'

 

except, instead of the Account ID (which is what is returned by a.Account__c), I want the account name.  I've tried looking through the SOQL  docs for relationship querries, I've tried Apex Explorer and Eclipse, but I can't seem to get it right.

 

Here's what I came up with in Apex Explorer except none of the Appointment__c fields are returned, just the account name, and I can't figure out how to put the where clause relating to items in the custom object:

 

 

Select a.Name, (Select Agent__c, Commission_Level__c, Confirm_Date__c, Contracted_As__c, Id, Notes__c, Status__c From Appointments__r) from Account a

 

Can someone point me in the right direction?

Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell
take you first query and use account__r.name instead of account__c

All Answers

SuperfellSuperfell
take you first query and use account__r.name instead of account__c
This was selected as the best answer
ethanoneethanone

I have a similar issue, however it is complicated by a couple of relationships.

 

I have a real estate appliction where a Property__c could have one or more Opportunities on it and each of those Opportunities pays a Commission__c to one or more Person Account. In other words, Property__c is a parent to Opportunity which is a master detail to Commission__c which has a look up to Account. I need to run a query by which I pull a field from the Property__c object, the opportunity object and I have the name of the Person Account.

 

The query below ALMOST gets me there, except that it returns the Id of the Account. So,

SELECT Preceding_Property__r.State__c, 
(SELECT Agent__c FROM Commissions__r)
FROM Opportunity LIMIT 10

 Will return:

|  State__c  |       Agent_c      |
|------------|--------------------|
| California | 0013000000T5Oo6AAF |
|------------|--------------------|

 

But I need the Agent Name.  I've tried:

SELECT Preceding_Property__r.State__c, 
(SELECT Agent__r.Name FROM Commissions__r)
FROM Opportunity LIMIT 10

 But Agent__r.Name just comes back empty. (and I mean really empty, like there is no column for the result). It doesn't seem like I'm exceeding relationship limits and I don't get any errors, but why isn't it working?

 

Thanks for any help you can provide.

 

P.S. I'm testing this in the salesforce schema tool.

SuperfellSuperfell

The query looks fine to me, this is probably a limitation in the tool you are using.

ethanoneethanone

Simon,

You were right, the problem was a limitation of the salesforce schema browser. Output looks like this:

Array ( [attributes] => Array ( [type] => Opportunity [url] => /services/data/v20.0/sobjects/Opportunity/0063000000dxOvNAAU ) [Id] => 0063000000dxOvNAAU [Preceding_Property__r] => Array ( [attributes] => Array ( [type] => Property__c [url] => /services/data/v20.0/sobjects/Property__c/a073000000AAkG5AAL ) [State__c] => California [Classification__c] => Partially Improved Lots;Single Family ) [Commissions__r] => Array ( [totalSize] => 2 [done] => 1 [records] => Array ( [0] => Array ( [attributes] => Array ( [type] => Commission__c [url] => /services/data/v20.0/sobjects/Commission__c/a0B30000006FdbTEAS ) [Agent__r] => Array ( [attributes] => Array ( [type] => Account [url] => /services/data/v20.0/sobjects/Account/0013000000T5Oo6AAF ) [LastName] => Power ) ) [1] => Array ( [attributes] => Array ( [type] => Commission__c [url] => /services/data/v20.0/sobjects/Commission__c/a0B30000006FdbOEAS ) [Agent__r] => Array ( [attributes] => Array ( [type] => Account [url] => /services/data/v20.0/sobjects/Account/0013000000T5UKhAAN ) [LastName] => Hepp ) ) ) ) ) 

 This result is an array of arrays, which requires a nested foreach at each "join."