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 '16 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 '16 at 14:34
  • opp.Contact wouldn't work as that is an object, not a list – cropredy Apr 22 '16 at 15:34

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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