You need to sign in to do that
Don't have an account?
Michael M
How to organize soql results based on task.subject
Hello, I have written some apex code that currently does 3 things:
1) fetches the results of EmailStatuses for listemails
2) puts that into a csv
3) emails that csv
I need to do a 4th thing: I need to summarize the results on the body of the email itself. In other words, I need to organize the queried data by "emailstatus.task.subject", and for each different subject, I need to show a summary of:
1. number of contacts it was sent to
2. number of contacts who opened the email
Is this possible, and can you please show me how to do so. Here is my code so far:
// fetch Data using SOQL from Salesforce
List<Contact> contList = [Select id, name, CreatedDate, lastModifiedDate, email,
(SELECT id, taskId, createdDate, firstOpenDate, lastOpenDate, timesOpened, emailTemplateName, task.subject, task.TaskSubtype
FROM EmailStatuses where createddate = last_month and task.tasksubtype = 'ListEmail')
from Contact where email != null ];
//Set Header values of the file
string csvHeader = 'Contact Name, Contact Email , Subject, Date Sent, Times Opened, Task Subtype \n';
string mainContent = csvHeader;
for (Contact cnt: contList){
for (emailstatus es : cnt.EmailStatuses){
integer month = es.createddate.month();
integer year = es.createddate.year();
integer day = es.createddate.day();
if (month == 12 && year == 2020 && day == 8){
//Adding records in a string
string recordString = cnt.name+','+cnt.email +','+es.task.subject +','+es.CreatedDate+','+es.timesOpened +','+es.task.tasksubtype +'\n';
mainContent += recordString;
}}}
Messaging.EmailFileAttachment csvAttcmnt = new Messaging.EmailFileAttachment ();
//Create CSV file using Blob
blob csvBlob = Blob.valueOf (mainContent);
string csvname= 'Contact.csv';
csvAttcmnt.setFileName (csvname);
csvAttcmnt.setBody (csvBlob);
Messaging.SingleEmailMessage singEmail = new Messaging.SingleEmailMessage ();
String [] toAddresses = new list<string> {'test@test.org'};
//Set recipient list
singEmail.setToAddresses (toAddresses);
String subject ='Contact CSV';
singEmail.setSubject (subject);
singEmail.setPlainTextBody ('Contact CSV');
//Set blob as CSV file attachment
singEmail.setFileAttachments (new Messaging.EmailFileAttachment []{csvAttcmnt});
Messaging.SendEmailResult [] r = Messaging.sendEmail (new Messaging.SingleEmailMessage [] {singEmail});
1) fetches the results of EmailStatuses for listemails
2) puts that into a csv
3) emails that csv
I need to do a 4th thing: I need to summarize the results on the body of the email itself. In other words, I need to organize the queried data by "emailstatus.task.subject", and for each different subject, I need to show a summary of:
1. number of contacts it was sent to
2. number of contacts who opened the email
Is this possible, and can you please show me how to do so. Here is my code so far:
// fetch Data using SOQL from Salesforce
List<Contact> contList = [Select id, name, CreatedDate, lastModifiedDate, email,
(SELECT id, taskId, createdDate, firstOpenDate, lastOpenDate, timesOpened, emailTemplateName, task.subject, task.TaskSubtype
FROM EmailStatuses where createddate = last_month and task.tasksubtype = 'ListEmail')
from Contact where email != null ];
//Set Header values of the file
string csvHeader = 'Contact Name, Contact Email , Subject, Date Sent, Times Opened, Task Subtype \n';
string mainContent = csvHeader;
for (Contact cnt: contList){
for (emailstatus es : cnt.EmailStatuses){
integer month = es.createddate.month();
integer year = es.createddate.year();
integer day = es.createddate.day();
if (month == 12 && year == 2020 && day == 8){
//Adding records in a string
string recordString = cnt.name+','+cnt.email +','+es.task.subject +','+es.CreatedDate+','+es.timesOpened +','+es.task.tasksubtype +'\n';
mainContent += recordString;
}}}
Messaging.EmailFileAttachment csvAttcmnt = new Messaging.EmailFileAttachment ();
//Create CSV file using Blob
blob csvBlob = Blob.valueOf (mainContent);
string csvname= 'Contact.csv';
csvAttcmnt.setFileName (csvname);
csvAttcmnt.setBody (csvBlob);
Messaging.SingleEmailMessage singEmail = new Messaging.SingleEmailMessage ();
String [] toAddresses = new list<string> {'test@test.org'};
//Set recipient list
singEmail.setToAddresses (toAddresses);
String subject ='Contact CSV';
singEmail.setSubject (subject);
singEmail.setPlainTextBody ('Contact CSV');
//Set blob as CSV file attachment
singEmail.setFileAttachments (new Messaging.EmailFileAttachment []{csvAttcmnt});
Messaging.SendEmailResult [] r = Messaging.sendEmail (new Messaging.SingleEmailMessage [] {singEmail});
https://salesforce.stackexchange.com/questions/244253/soql-query-description-field-on-task-object-contain-any-string-from-a-list-of-st
But you can check the above once.