Trigger to send email based on Multi-Select Picklist

Question

I have an object called Project__c. It has a multi select picklist field called Markets__c.

So a Project can have multiple Markets. On the other side, Contact object has a field called Market__c.

I need to have a trigger to send an email to each Contact record where Project__c.Markets__c field contains the value of Contact.Market__c.

Answer 1

This is actually a fairly simple query.

public static List<Contact> getMarketContacts(List<Project__c> projects)
{
    Set<String> markets = new Set<String>();
    for (Project__c project : projects)
    {
        if (String.isNotBlank(Project__c.Markets__c))
            markets.addAll(Project__c.Markets__c.split(';'));
    }
    return [
        SELECT Email FROM Contact
        WHERE Market__c IN :markets
        LIMIT 50000
    ];
}

Then you just need to define the method to send emails, whose stub would look like:

public static void sendEmails(List<Contact> contacts)
{
    // logic here
}

Answer 2

One way - use apex trigger and soql to save values from the picklist on create/edit and then query the object contacts to send emails.

Second way (better one) - don't create a picklist on project_c. Instead create an object that holds all value for markets. Then form the following lookup relations: 1. Lookup from market__c to project__c 2. Lookup from contact to market__c

What u have now is a 3 level relaionship. A workflow or a trigger should now work.

lemme knw what you think. Mann.

Answer 3

From a DB design perspective and from what Ive understood your design should be as follows:

> {Markets}--{Jn}--{Projects}
>        | {Contact}

(Contact is the child of markets)

Now to send the email to contacts who are in markets which have a project (this is my understanding of your requirement), do the following: pseudocode begins:-

1. Query object Jn and get all Market names in a list (ie. Select market__c.name from jn group by market__c)
2. Query Contact object where contact.market__c IN list from step1
3. Send email to these contacts