Yes, the root of this is that cross object formula fields can't be of type email and email alerts on your child object can't reference email fields on parent objects unless parent is a Contact)
Option 1
If Issue__c is a detail object in a master-detail relationship, then when Issue__c.status = 'Solved', use a workflow to set a field update on Office__c, say, has_resolved_issue__c
, a boolean.
Then, a subsequent workflow can fire the email alert to the current point person on the Office and clear via field update the has_resolved_issue__c
to false. You may need to copy more fields from the solved issue to the Office to make available to the email template -- or, if using a VF email template, that template, can through a component controller, access the Office's child Issues.
Option 2
As you suggested, a trigger on Office__c that when point person changes, propagates that point person email down into all child Issue objects.
// after update trigger -- this is untested
Set<ID> officeIdSyncSet = new Set<ID> ();
for (Office__c of : Trigger.new)
if (of.status__c != Trigger.oldMap.get(of.id).status__c)
officeIdSyncSet.add(of.id);
List<Issue__c> issUpdList = new List<Issues__c> ();
for (Office__c of : [select id, point_of_contact__c, // fetch children issues
(select id, point_of_contact__c
from Issues__r where status != 'Solved')
from Office__c where id in : officeIdSyncSet] )
for (Issue__c is : of.issues__r) {
is.point_of_contact__c = of.point_of_contact__c; // sync POC from parent
issUpdList.add(is);
}
update issUpdList; // does the sync via DML
You could try creating a timed based workflow rule which would run 2 days after the Due_Date. Have it update the email field and then send the email. According to this doc here you cannot predict the order the actions will occur but field updates will be executed first:
The order in which individual actions and types of actions are
executed is not guaranteed. However, field update actions are executed
first, followed by other actions.
Best Answer
U can achive it by either process builder or flow. https://success.salesforce.com/answers?id=9063A000000swe9QAA