Expand my Community achievements bar.

SOLVED

How to display Campaign sent data each recipient in HTML table for email template

Avatar

Level 2

Hi Everyone,

 

I have requirement to display Campaign sent data each user in table.

I have prepare below script using workflow data.

 

Workflow Data:

iSantoshk_1-1717075718055.png

 

 

JS Code:

vars.content = '<TABLE style="border: 1px solid black; border-image: none; width: 100%;"><TBODY><TR><TH style="border: 1px solid black; border-image: none; width: auto;">Event Date</TH> <TH style="border: 1px solid black; border-image: none; width: auto;">RCP ID</TH><TH style="border: 1px solid black; border-image: none; width: auto;">Name</TH><TH style="border: 1px solid black; border-image: none; width: auto;">Campaign</TH><TH style="border: 1px solid black; border-image: none; width: auto;">Delivery</TH><TH style="border: 1px solid black; border-image: none; width: auto;">Status</TH></TR>';

 

var query = NLWS.xtkQueryDef.create(
{queryDef: { schema: vars.targetSchema, operation: "select",
select: {
node: [ {expr: "@rcpId"},
{expr: "@name"},
{expr: "@date"},
{expr: "@campaign"},
{expr: "@delivery"},
{expr: "@status"}]
},

orderBy: {
node: {expr: "@date", sortDesc: "true"}
}

}})

var res = query.ExecuteQuery()

var records = res.getElementsByTagName("*")

for each (var w in records)
{ vars.content += '<TR>';
vars.content += '<TD style="border: 1px solid black; border-image: none;">'+ formatDate((w.getAttribute("date")), "%4Y/%2M/%2D %2H:%2M:%2S")+'</TD>';
vars.content += '<TD style="border: 1px solid black; border-image: none;">'+ w.getAttribute("rcpId")+'</TD>';
vars.content += '<TD style="border: 1px solid black; border-image: none;">'+ w.getAttribute("name")+'</TD>';
vars.content += '<TD style="border: 1px solid black; border-image: none;">'+ w.getAttribute("campaign")+'</TD>';
vars.content += '<TD style="border: 1px solid black; border-image: none;">'+ w.getAttribute("delivery")+'</TD>';
vars.content += '<TD style="border: 1px solid black; border-image: none;">'+ w.getAttribute("status")+'</TD>';
vars.content += '</TR>';
}

vars.content += '</TBODY></TABLE>';

  

here storing table content into a variable as content and calling into Email template like below.

 

iSantoshk_0-1717075523713.png

 

Note: But output displaying all all the users same table content like below

 

Table_Content_output.png

 

Need to restrict data each recipient in table.

 

Please share your solution approach for this Case.

 

Best Regards,

Santosh

 

1 Accepted Solution

Avatar

Correct answer by
Level 4

Hmm, this gets a little more fiddly then if there are lots of table rows which need to be aggregated for single end result recipients.

 

I would do something like....

  • Add an empty string field to each record to store table code
  • Run a JS Code activity in your workflow before your delivery. 
  • In your code, I'd use a similar xtkQueryDef setup access the target
  • Loop through the output and record the rcpID + field values to a storage library, something like: 

 

var library = {}

for each (var w in records){
if(!library[w.rcpID]){
    library[w.rcpID] = {
    rcpId : w.rcpID,
    content : w.value
    }
}else{
library[w.rcpID].content += w.value
}
}

but add in a bunch of <tr><td> stuff around it like you did in your code. 

Finally, you can loop again, based on the ID, and then write the table values back using some SQL like: 

var sql = "UPDATE " + vars.tableName + " SET stableContent=" + newVariable + " WHERE iId='" + row.@rcpId + "'";
sqlExec(sql);

 

Does that make sense? You're already like 85% there with your current code

View solution in original post

4 Replies

Avatar

Level 4

Hey Santosh, 

You've probably overcomplicated this one a bit if it's just a matter of "each recipient needs to see their own data".

Once you've got the values enriched to the target, you can access them directly in the email itself in a very similar manner to the recipient.salutation you have in use. 

From the "variable" dropdown, head to "recipient" and then "other". 
recipientVariables.png

 

Scroll down until you find the "additional data" section, expand it and you should be able to find all the fields you have enriched and add them in directly! 

 

additionalData.png

 

The code you've put together works great for a report email or something to be sent to business users however!

 

Alex 

 

 

Avatar

Level 2

Hi @ALangridge,

Thanks for the response on this.

I will include the additional data as you suggested, this is I know.

But what if any recipient has more than one records, how we can display multiple records in the table. Like below:

Label_DeliveryLabel_CampaignSatusEvent Date
Delivery 1Campaign 1Sentdate
Delivery 2Campaign 2Sentdate
    

Should we use for each loop for this additional data?

Avatar

Correct answer by
Level 4

Hmm, this gets a little more fiddly then if there are lots of table rows which need to be aggregated for single end result recipients.

 

I would do something like....

  • Add an empty string field to each record to store table code
  • Run a JS Code activity in your workflow before your delivery. 
  • In your code, I'd use a similar xtkQueryDef setup access the target
  • Loop through the output and record the rcpID + field values to a storage library, something like: 

 

var library = {}

for each (var w in records){
if(!library[w.rcpID]){
    library[w.rcpID] = {
    rcpId : w.rcpID,
    content : w.value
    }
}else{
library[w.rcpID].content += w.value
}
}

but add in a bunch of <tr><td> stuff around it like you did in your code. 

Finally, you can loop again, based on the ID, and then write the table values back using some SQL like: 

var sql = "UPDATE " + vars.tableName + " SET stableContent=" + newVariable + " WHERE iId='" + row.@rcpId + "'";
sqlExec(sql);

 

Does that make sense? You're already like 85% there with your current code

Avatar

Level 2

Hi @ALangridge ,

 

Thanks for your sample code.

 

Can you please update your loop into my code and share here. I am got confused  sorry.