1

Is it possible to do a custom object nested query on a standard object?

Like so:

[SELECT (SELECT Id, Case__c FROM Accounting_Instructions__c WHERE Accounting_Complete__c = TRUE AND Case__r.Id =:currentPageId), AccountId, CaseNumber FROM Case WHERE AccountId = :strAccountId AND Id IN :accountIds]; 

There is a relationship between Accounting_Instructions__c and Case (similar to Task and Case). I know you can do two standard objects and use the relationship name. An example of this is in this post: Query of Tasks for Custom Object.

Because of this format my initial thought would be to change Accounting_Instructions__c to Accounting_Instructions (like Task to Tasks) but this doesn't make sense because it's a custom object so I have no idea how Salesforce would understand this naming convention.

All of the documentation I am finding is examples of standard to standard object or custom to custom objects. Any advice?

1

You can find the relationship name by running a script like this one:

for (ChildRelationship relation : SObjectType.Case.getChildRelationships())
    if (relation.getChildSObject() == MyObject__c.sObjectType)
        system.debug(relation.getRelationshipName());

Custom object child relationships end in __r. So the likely relationship name would be Accounting_Instructions__r.

| improve this answer | |
  • So, duplicate of this? – sfdcfox May 23 '17 at 17:54
  • thanks guys for the references. Sorry for the dup. I can delete. – Olivia May 23 '17 at 17:55
  • @Olivia No need to delete, leave it as a signpost. – Adrian Larson May 23 '17 at 17:56
  • I just found out why I couldn't do a nested query the way I wanted- our admin changed the Object name from Payment_Instructions__c to Accounting_Instructions__c. Although the API name in Salesforce appears to be changed, when I did @adrians suggestion I found that the API name actually did not changing for some reason and is still under Payment_Instructions__c. When I update the query to this API name, I am able to do the nested query. SELECT (SELECT Id FROM Payment_Instructions__r), AccountId, CaseNumber FROM Case WHERE AccountId = :strAccountId with the correct returned results. – Olivia May 23 '17 at 19:30
  • It's little surprise that whoever changed the API Name forgot to change the Child Relationship Name. It certainly doesn't happen automatically. – Adrian Larson May 23 '17 at 19:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.