Hi,
Today I want to discuss you about one scenario which I came across recently and how I find the best way to do it. Here is the scenario:-

Scenario:
Suppose you have Case object which have two parent records, One is Contact Lookup and another is "MyCustomObject" object lookup. All three objects have one 'Email' field each. There is one another Custom Object called 'ChildObject' which is the child of Case object. The object structure is as below.


What we want to do is. On Insert of a new 'ChildObject' record we need to Notify Parent 'Case' Record using 'Workflow'. But the email should go to as logic below:-

If Contact.Email is filled then send email to that, otherwise if MyCustomObject.Email__c is filled then send email to that, Otherwise send email to Case.Email__c field.

Suggested Solutions and Problems:
1) Sending mail using Trigger on 'ChildObject'. Either do bunch of SOQL on all parent records and use final email to send mail within 'Trigger' OR you can use one custom email field on ChildObject it self to set email from parent in trigger and use workflow to use that email field to send emails but you can't avoid SOQLs.

2) One Before insert/update trigger on Parent Case Object. Which will set one extra Email field based on our conditions but for that you need again bunch of SOQL and logics. You can do same thing for ChildObject also.

3) Formula field on either ChildObject or Parent Case object which will filled our Email according to our logic. It is good way, it will avoid all SOQL and Logics. BUT main problem with this is that Formula Field CAN NOT be used in workflows.

My Solution (Somehow Better).

1) Create one Formula Field 'Formula_Email__c' on 'ChildObject which have following condition for fill this field

If(Case__r.Contact.Email != null, Case__r.Contact.Email, if(Case__r.MyCustomObject__r.Email__c != null, Case__r.MyCustomObject__r.Email__c, Case__r.Email__c));

2) Create one Email type field 'Case_Email__c' on 'ChildObject which will have the value from above formula field as below:

trigger on ChildObject (before insert, before update){
for(Case c: Trigger.NEW){
c.Case_Email__c = c.Formula_Field__c;
}
}

3) Thats it. See you saved so many SOQL and complex logic. Now used this final 'Case Email' field on 'ChildObject' workflow to send emails.

Is it good!!!

Thanks
Aslam Bari