I have a journey which includes a response capture element via Interactive Forms and a bespoke CloudPages landing page. There's a daily extract automation to pull all responses within the last 24hrs and transfer these to an internal-user directory. At the end of the automation, I have a notification email, alerting the internal user that a new file is available at X location.
At send, I wanted to be able to update a column in the sending DE for rows that were included in the latest file.
Here's what I have so far:
Email / SMS comms directing users to an interactive form or CloudPages landing page Responses captured into the send DE in the FormData_ columns Daily automation which selects, extracts, formats, transfers and sends a notification email
However, I'm unable to get the sending DE updated at the time of sending the notification email with success info i.e. Is_Process = '1' and Processed_Date = SystemDateToLocalDate(Now())
Here's my notification email code:
%%[
var @localDate, @fileName, @dataSourceName, @subscriberKey, @rowsUpdated
set @localDate = SystemDateToLocalDate(Now())
set @dataSourceName = "AMI_NC_Responses_V3"
%%[
SET @fileName = Concat('AMI_NC_Responses_Extract', FormatDate(@localDate, 'YYYYMMDD'),'.csv')
SET @archiveURL = Concat('F:\docs\ma\C25_Correspondence\01_General\MAP - letters','\AMI_BillData\',@fileName)
SET @subscriberKey = IsNullDefault(RequestParameter("sk"), "")
/* Update record in journey DE to identify where the record has been processed */
set @rowsUpdated = UpdateDE(@dataSourceName, 1,"Primary_Contact_ID", @subscriberKey,"Is_Processed", 1,"Processed_Date",@localDate)
]%%
<!doctype html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Notification Email</title>
<style>
body {
background-color: #f6f6f6;
font-family: sans-serif;
-webkit-font-smoothing: antialiased;
font-size: 14px;
line-height: 1.4;
margin: 0;
padding: 0;
-ms-text-size-adjust: 100%;
-webkit-text-size-adjust: 100%;
}
.body {
background-color: #f6f6f6;
width: 100%;
}
.content {
box-sizing: border-box;
display: block;
margin: 0 auto;
max-width: 580px;
padding: 10px;
}
.main {
background: #ffffff;
border-radius: 3px;
width: 100%;
}
.footer {
clear: both;
margin-top: 30px;
width: 100%;
}
.footer p,
.footer span,
.footer a {
color: #666666;
font-size: 12px;
}
h1 {
color: #000000;
font-family: sans-serif;
line-height: 1.4;
margin: 0;
margin-bottom: 30px;
font-size: 24px;
font-weight: 300;
}
p {
font-family: sans-serif;
font-size: 14px;
font-weight: normal;
margin: 0;
margin-bottom: 15px;
}
a {
color: #3498db;
text-decoration: underline;
}
.apple-link a {
color: inherit !important;
font-family: inherit !important;
font-size: inherit !important;
font-weight: inherit !important;
line-height: inherit !important;
text-decoration: none !important;
}
</style>
</head>
<body>
<div class="content">
<h1>AMI File Notification</h1>
<h2>Details</h2>
<p>The following file is ready: %%=v(@fileName)=%%</p>
<p><strong>Date Processed:</strong> %%=FormatDate(@localDate, 'l', '', 'en_NZ')=%%</p>
<p><strong>Time Processed:</strong> %%=FormatDate(@localDate, '', 'hh:mm', 'en_NZ')=%%</p>
<p><strong>File Location:</strong> <a href="file:///%%=v(@archiveURL)=%%">%%=v(@archiveURL)=%%</a></p>
<div class="footer"><p>Sent from %%Member_Busname%%, %%Member_Addr%%, %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%<span style="display:none;">%%profile_center_url%%</span></p></div>
<custom name="opencounter" type="tracking"/>
</div>
</body>
</html>
