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!