4

It seems odd to me that this would be simply impossible to do, but the aggregate query below returns more than 2000 records, and it appears that there is no possible way to even run the query, let alone try to export a CSV. The same error message appears in the developer console or Dataloader.io, whether I use the bulk API or not:

SELECT Contact.Account.Name Account, Contact.AccountID, MIN(FirstRespondedDate) Date
FROM CampaignMember WHERE Is_Contact__c = true AND FirstRespondedDate != null GROUP BY Contact.Account.Name, Contact.AccountID

Of course using LIMIT is no help, because I need a full report.

Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch

I can use batch to process the records, but what if I need a CSV export? Could it be that Salesforce really makes this impossible?

2
  • Create a dataset via batch, then email a link that would allow the recipient to export as CSV. Processing a click of that link would presumably require a custom component that would act accordingly.
    – identigral
    Apr 5, 2020 at 22:35
  • Thanks, but...how would one do that?
    – number41
    Apr 5, 2020 at 23:59

1 Answer 1

2

I found a better way – it's actually fairly easy to create a CSV in Apex and email it to yourself, and remarkably this gets around the 2000 record restriction for aggregate queries. I have no idea why this is so easy in Apex, and yet all bulk API extraction tools can't do it.

Here ya go:

public class GenerateCSV {

    public static void GenerateCSV(){                           

            AggregateResult[] CMs = [SELECT Contact.Account.Name Account, Contact.AccountID, Contact.Name Contact, ContactID, Campaign.Name Campaign, CampaignID,  MIN(FirstRespondedDate) Date
                                     FROM CampaignMember 
                                     WHERE Is_Contact__c = true AND FirstRespondedDate != null 
                                     GROUP BY Campaign.Name, CampaignID, Contact.Name, ContactID, Contact.Account.Name, Contact.AccountID];

            system.debug('CMs = '+CMs);

            String csv = 'Account,AccountID,Contact,ContactID,Campaign,CampaignID,Date\n';

            for(AggregateResult CM: CMs){
                csv += CM.get('Account') + ',' + CM.get('AccountID') + ',' +
                       CM.get('Contact') + ',' + CM.get('ContactID') + ',' + 
                       CM.get('Campaign') + ',' + CM.get('CampaignID') + ',' + 
                       CM.get('Date') + '\n';       
            }                      

            Messaging.EmailFileAttachment csvAttachment = new Messaging.EmailFileAttachment();
                Blob csvBlob = blob.valueOf(csv);
                String csvName = 'CMs CSV';
                csvAttachment.setFileName(csvName);

                csvAttachment.setBody(csvBlob);

            Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();
                String[] toAddresses = new String[]{'[email protected]'};
                String subject = 'CMs CSV';
                email.setSubject(subject);
                email.setToAddresses(toAddresses);
                email.setPlainTextBody('CMs CSV');
                email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttachment});
            Messaging.SendEmailResult[] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[]{email});
        }  
    }

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .