System.LimitException: reports:Too many query rows: 50001

[Jalps1018]

Hi Doug,

I have installed the managed package to 'Add campaign members by report' and it has been fantastic for past month. I have set up several reports for different campaigns and it has been working fine. However, now I am receiving an error as below. Can you please advise what can I do to resolve this limit error?

**Apex script unhandled exception by user/organization: 00520000004a1gh/00D20000000mpp0

Failed to process batch for class 'dca_cmpgn_apps.AddCampaignMembersByReportBatchable' for job id '7070O00004DD1cV'

caused by: System.LimitException: reports:Too many query rows: 50001

External entry point (dca_cmpgn_apps)**

Thanks Jalpa

[douglascayers]

Hi @Jalps1018,

Can you confirm if your Campaign already has 50,000 campaign members or is it the report that has that many rows in it?

If it's only the report, as workaround perhaps try using multiple reports that filter to less than 50,000 rows each and map each report to the same campaign.

50,000 rows in SOQL query is a Salesforce governor limit that I may not be able to work around since the report data is already being batched.

Thanks,

Doug

[Jalps1018]

Hi @DouglasCAyers

Thanks for the response.

Looking at the campaign and the report, neither of them has more than 50,000 records. However, I have set up 5 different report subscriptions which probably runs at the same time. Could that cause the Salesforce governor limit? If I change the time each report runs, will that make a difference?

thanks Jalpa

[douglascayers]

Hi @Jalps1018,

Unfortunately, I think this may be major limitation of the Reports API in Apex.

Iñigo PuigPey reported similar issue on the developer forums, https://developer.salesforce.com/forums/?id=9060G000000IBaOQAW

And I found on Salesforce Foundation (who developed the underlying Report querying code my project uses) also says this is a limitation, https://github.com/SalesforceFoundation/CampaignTools/issues/88. According to one commenter, they say the limit may even be seen with reports with as few as 14,000 records =(

The limitation is not based on how many reports you have scheduled in the tool as each runs as their own background job with their own governor limits. This is an unfortunate quirk of the reporting API.

[Jalps1018]

Hi @DouglasCAyers thanks for the update. It doesn't seem like currently there is any solution for this!

[mvenman]

@DouglasCAyers I may be seeing this issue with ~11k records in my report - could that be possible?

[douglascayers]

Yes @mvenman, sorry. Until the Reports API is changed on the Salesforce backend, then this solution is limited to reports with probably no more than 10,000 rows =(

[mvenman]

AHA. Thanks @DouglasCAyers! I'm going to give the ListView a Shot and see if that fixes things. Thanks for getting back to me!

[danhowellnz]

@mvenman Try the ListView if problems you can implement a simple workaround/hack of splitting it into two reports. Filter on the characters in an id or name to arbitrarily create smaller reports.

[SenseiJP]

I am also receiving this error? "dlrs_ContactTrigger: System.LimitException: dlrs:Too many query rows: 50001"

Have there been any workarounds to correct/fix this issue?

Also, does this error only occur when a report is generated?

Any help would be much appreciated. Thank you in advance.

[douglascayers]

Hi @SenseiJP,

That is still a limitation fo the Reports REST API.

You may be interested in a new more robust tool I've developed, Mass Action Scheduler. It lets you declaratively schedule Process Builder, Flows, Quick Actions, Email Alerts, Workflow Rules, and Apex to process records from Reports, List Views, and SOQL.

Check out this example.

To get around the query rows issue, you can use a List View or SOQL source instead of Reports.