Email Alerts and Formula (Lookup) Fields

Question

I'm hoping a SF guru can help me think through this problem.

Here's an example: imagine that I have two objects: Offices, and Issues, a child object. In Office, I have an email field called 'Point Person.'

When an issue's status changes to 'Solved,' I want to email the point person field listed in Office using an email alert. So, when I create a new issue, I copy the 'Point Person' field from Office to the Issue, and when the issue is solved a workflow triggers the email alert to the email address I've listed. Great!

However, sometimes the point person changes in an office. When this occurs, all of the existing unsolved issues are now pointing to the prior point person, and my email updates improperly go to him or her. I can prevent this by creating a lookup formula field, but email alerts can't be sent to formula fields.

At this point, it seems like I might be able to resolve this problem by creating a trigger -- before updating an issue, I could grab the current Office Point Person and update the relevant field in the Issue. (Or I could create a trigger that updates the Point Person on each of the issues after an Office is updated.)

However, I'm wondering if there's a simpler solution to my problem. What would be great would be to just send the email alert to the email in my formula field, but it doesn't look like that's possible. I'd appreciate any feedback or thoughts!

Answer 1

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

Answer 2

You didn't say, but this answer assumes you only have a couple of point persons. If you have a large number of them, this would not be an appropriate solution for you.

I solved a similar issue for a client by creating a custom setting. In your case, you could create a Custom Setting called Point_Person. Then create a field for Name and another one for email address. You could create a couple of sets of data for different types of point people and use those as references to look-up or point to whenever they're needed.

As your point people change, you can easily edit your existing custom settings for them. You can also add new records (PointPerson1, PointPerson2, PointPerson3...) as your needs change or expand.