0

I'm trying to build a newsletter manager that allows users to easily update and clear newsletter campaign members based on their subscription and region without having to run multiple reports. I've got a custom object called Newsletter Map where the user plugs in the related campaign, newsletter type (which corresponds to a subscription multi-select list on a contact record) and a region (which corresponds to a region list on the contact record). I've added a custom button that links to a VF page calling a class that runs an update to clear Campaign Members that shouldn't be there based on criteria and add new ones.

Full disclosure, I'm somewhat new to this and learning the ropes but I'm running into a DML limit error and I can't figure out why. Any help would be appreciated.

System.LimitException: npsp:Too many DML statements: 151 Error is in expression '{!updateSubscriptions}' in component in page newslettersubscriptionsupdatepage: (npsp)

VF Page:

<apex:page Controller="NewsletterSubscriptionsUpdate"
    action="{!updateSubscriptions}">
    The Campaign is being updated. If you are not redirected, there's an error.
    Contact your admin.
</apex:page>

Class:

public without sharing class NewsletterSubscriptionsUpdate {
    public PageReference updateSubscriptions () {

        //pull ID from Newsletter Map Object and store ID, Region,
        //Newsletter Type(subscription) and Newsletter Campaign ID
        String mapID = ApexPages.currentPage().getParameters().get('mapID');
        Newsletter_Subscription_Map__c newsletterMap = [
            SELECT ID, Region__c, Newsletter_Type__c, Campaign__r.ID
            FROM Newsletter_Subscription_Map__c WHERE ID = :mapID
        ];

        //If the Newsletter is not National, delete any existing members who have
        //removed the subscription OR changed regions
        if(newsletterMap.Region__c != 'National'){
            list<CampaignMember> membersToDelete = [
                SELECT ID FROM CampaignMember
                WHERE CampaignID = :newsletterMap.Campaign__r.id
                AND (
                    Contact.Newsletter_Subscriptions__c EXCLUDES (:newsletterMap.Newsletter_Type__c)
                    OR Contact.Contact_Region__c != :newsletterMap.Region__c)
            ];
            database.delete(membersToDelete);       
        }

        //If the newsletter is National, delete any existing members who have
        //removed the subscription
        if(newsletterMap.Region__c == 'National'){
            list<CampaignMember> membersToDelete = [
                SELECT ID FROM CampaignMember
                WHERE CampaignID = :newsletterMap.Campaign__r.id
                AND Contact.Newsletter_Subscriptions__c EXCLUDES (:newsletterMap.Newsletter_Type__c)
            ];
            database.delete(membersToDelete);
        }

        //build list of contact IDs already in Campaign to avoid trying to
        //create duplicates
        list<CampaignMember> membersToAvoid = [
            SELECT ContactID FROM CampaignMember
            WHERE CampaignID = :newsletterMap.Campaign__r.ID
        ];
        list<ID> contactsToAvoid = new list<ID>();
        for (CampaignMember currentMember : membersToAvoid){
            contactsToAvoid.add(currentMember.ContactID);
        }

        //if the Newsletter is a not National Newsletter, add all Contacts who are mapped
        //to the Newsletter and the Region and not in the list of already existing Members
        if(newsletterMap.Region__c != 'National'){
            list<Contact> contactsToAdd = [
                SELECT ID FROM Contact
                WHERE Contact_Region__c = :newsletterMap.Region__c
                AND Newsletter_Subscriptions__c includes (:newsletterMap.Newsletter_Type__c)
                AND ID NOT IN :contactsToAvoid
            ];
            list<CampaignMember> membersToAdd = new list<CampaignMember>();
            for (Contact currentContact : contactsToAdd){
                CampaignMember newMember = new CampaignMember();
                newMember.ContactID = currentContact.ID;
                newMember.CampaignID = newsletterMap.Campaign__r.ID;
                newMember.Status = 'Member';
                membersToAdd.add(newMember);
            }
            database.insert(MembersToAdd);
        }

        //if the Newsletter is a National Newsletter, add all Contacts who are
        //mapped to the Newsletter and not in the list of already existing Members
        if(newsletterMap.Region__c == 'National'){
            list<Contact> contactsToAdd = [
                SELECT ID FROM Contact
                WHERE  Newsletter_Subscriptions__c includes (:newsletterMap.Newsletter_Type__c)
                AND ID NOT IN :contactsToAvoid
            ];
            list<CampaignMember> membersToAdd = new list<CampaignMember>();
            for (Contact currentContact : contactsToAdd){
                CampaignMember newMember = new CampaignMember();
                newMember.ContactID = currentContact.ID;
                newMember.CampaignID = newsletterMap.Campaign__r.ID;
                newMember.Status = 'Member';
                membersToAdd.add(newMember);
            }
            database.insert(MembersToAdd);
        }

        PageReference pageRef = new PageReference('/' + mapId);
        pageRef.setRedirect(true);
        return pageRef;
    }
  • First, it seems as though there are opportunities to organize and structure your code with if-else statements that would make it easier to follow. I can see where you could easily get the error that you're receiving. Perhaps approaching this from a dynamic SOQL builder would be a more effective approach for your purposes, allowing you to reduce the queries. It appears your having to run too many queries before you get to a final map is largely code structure related. – crmprogdev Feb 22 '15 at 19:31
  • Any triggers on CampaignMember? Seems like a likely culprit. – Adrian Larson Feb 22 '15 at 19:36
  • Yeah, I checked on that. There's a few very old inactive ones and then the NPSP Trigger on Campaign Members. I changed the delete and update statements to if-else. Any good resources to point me to on building dynamic queries? Thank you! – Guest Feb 22 '15 at 20:05
  • Have a look at your debug logs to see if there is an repeating DML in it. If you search for DML_BEGIN you will see all the references, unless the log has been truncated. – BarCotter Feb 23 '15 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.