We're setting up a workflow to notify the finance department when an Opportunity owner flags an invoice as "ready to be sent".

I've got a VF email template, relatedTo="invoice", and I want to pull out:


and then cycle through that to find the billing contact.

When I do that, I get the error: Aggregate Relationship is used in an unsupported complex expression

I've done some digging and understand that there is limit to the depth you can traverse in parent/child relationships, but is there any other workaround? It is possible to include some kind of lookup field on the Invoice object (which is custom), to pull or make the OpportunityContactRoles directly related to the invoice object so it can be referenced directly.

Any ideas / experience with this?

Sample Code when Nested:

<apex:repeat value="{!relatedTo.Opportunity__r.OpportunityContactRoles}" var="opp">
    <apex:repeat value="{!opp.Contact}" var="cx">
  • the expression you are using returns a list. Are you using it in a VF repeat or pageblocktable or datatable component value= attribute?
    – cropredy
    Apr 22, 2016 at 14:04
  • I've tried it putting it in a repeat block, and even nesting to see if that would solve it, but as soon as I try to reference the Contact in any way, it breaks. I've updated my post with a sample from the nested version, which throws the same error as above.
    – McB
    Apr 22, 2016 at 14:34
  • opp.Contact wouldn't work as that is an object, not a list
    – cropredy
    Apr 22, 2016 at 15:34

1 Answer 1


Your problem is that SOQL doesn't allow you to go up to a parent and then down to a child in a single expression.

Opportunity is parent of Invoice, OpportunityContactRoles is child of Opportunity

Your solution will require a custom VF component with its own controller that you'll insert into your VF template

<c:OCR oId="{!relatedTo.opportunity__c}"/>

The component's VF markup will be

<apex:component controller="OpportunityOcrController" access="global">
  <apex:attribute name="oId" assignTo="{!oppoId}" type="id" description="Opportunity id"/>

  <apex:repeat value="{!ocrs}" var="ocr">
    <apex:outputField value="{!ocr.role}"/>

The OpportunityOcrController has a method getOcrs() that uses the value of property oppoId to retrieve the OCRs for that Oppo

You must log in to answer this question.

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