@DattaSutar,
To achieve this use case by designing two interconnected workflows: an hourly one to process incoming segment files and handle immediate welcome emails for enrolled students, while accumulating non-enrolled student data in a persistent temporary table grouped by department or List. A separate daily workflow then aggregates this accumulated data, generates a consolidated notification email listing pending students per department, and sends it to the respective department heads at day's end.
Hourly Workflow Design - Start the hourly workflow with an scheduler activity then followed by file collect, data loading activity to load the segment data file into the workflow's working table, mapping fields like studentEmail, studentLibEnrolStatus, studentDepartment, studentDepartmentHead, and studentDepartmentEmail.
Follow with a Use a Split activity to branch based on studentLibEnrolStatus: one path for enrolled students (status = 'enrolled' or true) and another for non-enrolled.
Filter and validate the data if needed, ensuring no duplicates by checking against an existing recipient schema or using a unique key like studentEmail.
On the enrolled path, connect to a Delivery activity configured as an email template for the welcome message, personalizing it with studentEmail and other relevant fields like studentDepartment. Ensure the delivery targets the working table's enrolled subset, proving and sending immediately to comply with the hourly processing requirement.
For the non-enrolled path, use an Enrichment activity to add a processing timestamp (e.g., current date) and link to a custom temporary schema or list (create via Data Schemas if needed, with fields for student details and department grouping). Insert this data into the temporary table using a Update Data activity, inserting by studentEmail to handle potential duplicates across hourly files.
Daily Consolidation Workflow
Schedule a second workflow to run once daily using a Scheduler activity set to "Daily" mode with end-of-day timing. Begin with a Query activity pulling from the temporary table, filtering for today's date and grouping by studentDepartment to aggregate pending students. Use an Aggregation activity to summarize per department, collecting lists of studentEmail, studentDepartmentHead, and studentDepartmentEmail into a single output per group.
Generating and Sending Consolidated Emails
In the daily workflow, you can use a Query Activity with a GroupBy function on studentDepartment to retrieve the required data.
Alternatively, you can use a JavaScript Activity to query the custom schema we’ve built, perform the grouping within the script, and generate the HTML email content for each department.
The script provided below is an example. You can adapt it to fit your schema and test it to ensure it works as expected.
var queryTemp = xtk.queryDef.create(
<queryDef schema="custom:NonEnrolledTemp" operation="select">
<select>
<node expr="@studentEmail"/>
<node expr="@studentDepartment"/>
<node expr="@studentDepartmentHead"/>
<node expr="@studentDepartmentEmail"/>
<node expr="@processingDate"/>
</select>
<where>
<condition expr="@studentLibEnrolStatus != 'enrolled'"/>
<condition expr="Year(@processingDate) = Year(Now()) and Month(@processingDate) = Month(Now()) and Day(@processingDate) = Day(Now())"/>
</where>
<orderBy>
<node expr="@studentDepartment"/>
<node expr="@studentEmail"/>
</orderBy>
</queryDef>
);
var res = queryTemp.ExecuteQuery();
var html = 'Hi Department Heads,<br><br>';
if (res == '') {
html += 'No students have pending library enrollments today.<br><br>';
} else {
if (res.length() > 0) {
html += 'Below is the consolidated list of students whose library enrollment is still pending, grouped by department:<br><br>';
// Group by department using a JavaScript object
var depts = new Object();
for each (var record in res) {
var dept = record.@studentDepartment;
var head = record.@studentDepartmentHead;
var headEmail = record.@studentDepartmentEmail;
var email = record.@studentEmail;
if (!depts[dept]) {
depts[dept] = {
head: head,
headEmail: headEmail,
students: new Array()
};
}
// Check for duplicates without indexOf if needed; simple loop alternative:
var isDuplicate = false;
for (var i = 0; i < depts[dept].students.length; i++) {
if (depts[dept].students[i] == email) {
isDuplicate = true;
break;
}
}
if (!isDuplicate) {
depts[dept].students.push(email);
}
}
// Build HTML sections per department using for...in
for (var dept in depts) {
html += '<h3>Department: ' + dept + '</h3>';
html += '<p>Department Head: ' + depts[dept].head + ' (' + depts[dept].headEmail + ')</p>';
html += '<TABLE CELLPADDING="0" CELLSPACING="0" BORDER="1" WIDTH="700"><TR bgcolor="#C0C0C0"><TH WIDTH="700">Pending Student Emails</TH></TR>';
for (var j = 0; j < depts[dept].students.length; j++) {
html += '<TR style="text-align:left"><TD WIDTH="700">' + depts[dept].students[j] + '</TD></TR>';
}
html += '</TABLE><br><br>';
}
}
}
html += 'Kind regards<br>Adobe Campaign Team';
vars.html = html;
Thanks
Sushant Trimukhe
