Question
Can pega Send Email in batches?
Hello Team,
We have a requirement to display the Total number of open cases in our system, so we have utilized the Report Definition and displayed it on the UI. The problem arises when the Export is clicked with more than 1Lakh records, users either get a timeout, logged out, or the standard Pega Error.
In order to avoid this, we came up with a scenario to remove the export options and rather to do the export with a click of a button->Create a case at the back end with the Report fetching a max of 1Lakh records->Route to WB->Process through an SLA to the next connector->Send EMail shape.
This is working perfect, but I am unsatisfied, I wanted the remaining records which exceeds 1Lakh records to be in a separate email.
I thought of two approaches, but both have their cons. please suggest if there is any functionality that I can leverage.
Approach 1:
An Activity to have the Report Definition fetching all the records, and the logic to Eliminate the first 1Lakh records.
Cons:
1. DB might get timed out.
2. Email might not be sent or sent with limited records.
Approach 2:
Wite an Activity to set a flag on each case processed, which is an efficient solution. so that the next set of records would not have this flag set.
Cons:
1. A new property needs to be exposed as a column.
2. This flag should be set as false the moment all the records are Emailed.
Additional Details: we are using Pega Internal table. Our current version of Pega is 8.3.2
***Edited by Moderator Marissa to update Content Type from Discussion to Question***
Accepted Solution
Yes, Second Approach Isn't suitable.
I found a way, a better one.
Write an Activity/DT
1. Write 2 RD's one for fetching all the records, with the sort on WB createDateTime low to highest. have a Param.counter to know the number of records retrieved.
Divide by the number of records which are needed to fetch, as per my ask "1 Lakh Records".
eg: 200000/100000 = 2
The first iteration is the first RD.
Write a For loop with the Param.Counter-1, call the 2nd RD
2. The Second RD should also contain a sort with WB CreateDatetime low to Highest and a filter with WB create Date Time with "greater than filter" from the first RD.
So how would the first RD results not collate with the 2nd RD results? I do a resume flow after successful sent Email.so the records are always new. which would help me attain an email with 1Lakh records in each set.
To not overburden the server, I do it asynchronously[all the above said in a flow] and invoke it through an Agent with pxCreateTopCase.
This would solve the issue.
Hi
For the second approach as the records are more so we have to do multiple save operations and commits due to this it might impact the DB performance and also extract column we have to maintain.Instead of creating extra column in the table.
Following is the approach which I can suggest.
1.Write an SQL Query that gives the count of records by day or by month based on pxCreateDateTime column with group by as day or month .
2. Invoke the select query. Now take the first date time value and start summing up the count of records day by day until the sum becomes 1 lakh or nearest to that. Now take the end date.
3.Run the report with the following dates and then once the results are obtained E-mail to the user.
4. Now take the end date time and from there start counting and repeat the same.
Pros :
This approach will eliminate adding new column and multiple save operations
If the user changes or lost the mail we need to reconsider the previously mailed records. So we should again reset the flag and mail the data. In order to eliminate that we can go with above approach which will avoid creation of other dependency tasks.