I have written a nested SOQL query for Invoice and Payment object.

select Id,Name, Zuora__External_Id__c, Zuora__Balance2__c, Zuora__PaymentAmount__c,(select Zuora__EffectiveDate__c FROM Zuora__Payments__r) from Zuora__ZInvoice__c

How can I assign the Zuora__EffectiveDate__c value to one of the fields on another object?

2 Answers 2


If you want to access child records, you can use following syntax:

List<Zuora__ZInvoice__c> invoices = 
  [SELECT Id,Name,Zuora__External_Id__c,
           (SELECT Zuora__EffectiveDate__c
            FROM Zuora__Payments__r)  
    FROM Zuora__ZInvoice__c];

Zuora__ZInvoice__c invoice = invoices.get(0);
DateTime child_effective_date = invoice.Zuora__Payments__r.get(0).Zuora__EffectiveDate__c;

Basically, child objects behave like a lists, so consider scenarios when there are no child records or couple of them.

  • 1
    Child records in a parent-child subquery behave like a list because it is actually a list (similarly, if you query for parent object fields from a child object, child.parent__r is itself an instance of the parent SObject). I don't think I've really been able to exploit that fact (except for one or two unit tests), but I still find it interesting to know.
    – Derek F
    Jun 29, 2017 at 12:59
  • Subquery relationships behave somewhat like likes, and are assignable to those, but they are not equivalent to lists. In this example, you can do this: List<Zuora__Payments__c>myList = invoices[0].Zuora__Payments__r; myList.add(new Zuora__Payments__c()); but not invoices[0].Zuora__Payments__r.add(new Zuora__Payments__c()); Also, when doing the assignment to myList and adding a SObject to the list, the original list does not change, rather a shallow copy of the list in created in the assignment
    – Jochen
    Jun 29, 2017 at 14:23

This style of coding - where the query is built into the for loops - and where you loop over all parent and child objects can be a good starting point:

for (Zuora__ZInvoice__c invoice : [
        SELECT Id, Name,
                (SELECT Zuora__EffectiveDate__c FROM Zuora__Payments__r)  
        FROM Zuora__ZInvoice__c
        ]) {
    // Add code that uses the invoice fields here
    for (Zuora__Payments__c payment : invoice.Zuora__Payments__r) {
        // Add code that uses the payment fields here

Note that you should add a WHERE clause to limit the query to the specific rows you need to process.

On the logic of this, consider that each parent object may have many child objects. So if you are trying to assign a value from the child objects to the parent object, you need to think about which child object "wins" and build that into your logic.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .