function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
luc4luc4 

Find duplicate CampaignMembers in trigger

I have the requirement to set a custom CampaignMemberStatus ('Duplicate') for new added duplicate CampaignMembers (duplicate in my case means the underlying Contac/tLead have the same email address).

 

Because a CampaignMember trigger can contain CampaignMembes from different campaigns i'm not sure what will be the best/efficent way to query the already existing CampaignMembers without hitting governer limits.

 

My first thoughts are:

1. Collecting all email addresses that are existst in current the trigger bulk.

2. Collecting all campaign ids that are exist in the current trigger bulk.

3. Querying all CampaignMembers that uses an email address from the current bulk and are in a campaign from the current bulk.

 

Something like this:

SELECT Id, campaignId, email__c
FROM CampaignMember
WHERE CampaignId IN :campaignIds
AND email__c IN :emailAddresses
AND Status != 'DUPLICATE'
AND email__c != null

 

 

Notes about the query:

1. email_c is a formula field which contains the email address from the underlying object depending if the object is a Contact or Lead.

2. select all members that are IN the bulk campaign ids _AND_ are IN the bulk email addresses.

4. exclude already as duplicate marked members (only get the originals) to reduce the result even more (to know there exists at least one member with this email address is sufficient).

 

I'm aware of that the condition in 2. will also fetching members that are not interesting for me (example: member with email 'a' that is in campaign 'b', but the bulk contains the email 'a' only for campaign 'c'). So i must check the result against the current bulk in apex afterwards (check if member in same campaign with same email).

 

But i think this should be ok or at least should not break the govenor limits.

 

 

In a worst case scenario we can have a bulk with:

1. 200 members from different campaigns.

2. each member has a different email.

3. each of the 200 campaigns contain at least 200 members with this email addresses.

 

So this would mean that we can get a maximum query result of 40000 members (limit is 50000). Because we also must iterate over the result (to check against the current bulk) we must also keep the logic there very keen/compact to not hit the script lines limit. I think the heap limit can also be passed by using a soql loop.

 

For me this approach looks very complex for on the first sight very simple requirement. Because i'm new to apex development i'm also not so confident in my expectations about the limits. ;)

 

I would be happy to hear any thoughts on this approach and i'm also open to other solutions for the problem.

 

Thanks for taking the time to read this!

 

Greetings

Luca

 

P.S. The code should run in a managed package and therefore it should be robust and also pass the security review!

JFraileJFraile

Hi.

 

If delegating the process to batch or @future is not an option, I'd try to use maps and sets to get rid of some iterations. The code could look something like this (there may be some syntax errors...)

1.- Retrieve The campaigns with their child CampaignMembers using a map

Map<ID,Campaign> cIdToCampaignWCampaignMembersMap = new Map<ID,Campaign> ([select id (select id, email__c from CampaignMember where email__c IN :emailAddresses
AND Status != 'DUPLICATE' AND email__c != null) from Campaign where Id IN :campaignIds]);

2.- For each CampaignMember you are inserting, create a set with the emails of the CampaignMembers related to the Campaign. Then check if the CampaigMember's email is in the list. If it is then it means it is a duplicate. 

for(CampaignMember cm: trigger.new){
Campaign c = cIdToCampaignWCampaignMembersMap.get(cm.CampaignId);
Set<string> cmEmailsSet = new set<string>(); 
for(CampaigMember cmList : c.CampaignMember)cmEmailsSet.add(cmList.email__c);
if (cmEmailsSet.contains(cm.email__c)) -->Duplicate

 Creating sets will consumes a lot of lines, but as it only uses one line to create each set, the total will be 40.000 lines.

 The query will return a maximum of 40.200 rows (200 Campaigns + 200*200 CampaignMembers)

There may be a way to optimize this, but meanwhile this approach could work.