Generate a weekly email for each user based on role, who have custom opportunity objects that meet a criteria

Question

I've got a custom object "Invoices" tied to my Opportunities, so Opps have 1 or more invoices. Each invoice has a "date to be sent" field.

What I'd like to do is auto-generate an email every Monday morning that notifies each User of their Opportunities and with Invoices scheduled to go out < Next 7 Days. The purpose is for them to go in and edit the Invoice to either flag is as "Ok to send" or to move the date back to a more appropriate time.

I'm trying to accomplish this using a VisualForce email template, but can't for the life of my figure out if the relatedTo should = User or Opportunity since I want it to list all of the relevant invoices for Opportunities owned by the User.

Anyone have any guidance on how best to implement something like this?

Adding in some code from VF Email.

messaging:emailTemplate subject="Review Invoices" recipientType="User" relatedToType="Opportunity"    

//then the loop for invoices
apex:repeat var="cx" value="{!relatedTo.Invoice__c}"

//show this stuff in a table. Can use outputtext to skip if date is out of range
{!relatedTo.name}
{!cx.Invoice_name__c}
{!cx.Invoice_amount__c}

/apex:repeat

This code isn't finding the invoice__c object, and I've tried all kinds of variations. Can't seem to find an easy reference for how object elements relate via API name anywhere.

Answer 1

Your line apex:repeat var="cx" value="{!relatedTo.Invoice__c}" would be an invalid field if Opportunity is a parent of Invoices. In Salesforce, a custom relationship is typically (default) named as plural of child sobject appended by __r.

Hence, the apex:repeat should be:

apex:repeat var="cx" value="{!relatedTo.Invoices__r}"

Standard relationships don;t use the __r convention, for example, the relationship from Account to Contacts is defined as Contacts, the relationship from Account to Opportunities is Opportunities. You can find the relationship names by examining the schema (specifically, the metadata property childRelationships for any SObject.

You had a follow-on question:

Now that it's tied as a template to the opportunity, is there any way to pull in all opportunities? That was why I was confused between starting with relatedTo=User or Opportunity

An email template's context depends on how it is invoked:

• If invoked as an email alert from a workflow or flow, then the template applies only to a single target object instance.
• If invoked by a sendEmail() Apex method, the template also applies only to a single object instance, the one specified by the setWhatId(...) method

To get all Opportunities onto a single email to a user, you will need to use a custom VF component with associated controller that will query all Opportunities and their child Invoices for a given user. The custom component exists inside of a VF email template where the relatedToType=User and you'll pass the relatedTo.id as a parameter to the custom component

<c:myCustomComponent_opposAndInvoices userid="{!relatedTo.id}"/>

Answer 2

think value="{!relatedTo.Invoice__c}" should be value="{!relatedTo.Invoice__r}" as value="{!relatedTo.Invoice__c}" is the ID not the relationship.