12 Days of Scriptmas - 2021
HowToSFMC
Published on 12/10/2021
As 2021 draws to a close, it’s time to open up the door on the 2021 edition of the HowToSFMC Scriptmas tradition where the team and the community share some of their top scripts, tips, hacks and workarounds.
New for 2021 we'll be including some specific API functionality that may otherwise go unnoticed or under utilised.
Every day in the lead up to the big day, starting on Monday 13th December up until Christmas itself, we’ll be revealing one scripted piece of goodness for you to grab and use to your heart's content for 2022 and beyond.
Day One
On the first day of Scriptmas Lesley Higgins gave to me... An awesome utility for validating an email address within SFMC.
All you need to do is pop in your Installed Package credentials and an email address and you'll find out if the email address has a valid Syntax, MX Record attached or if it's going to be rejected out through ListDetective! Winner.
Click here to see the Day One Script
<script runat="server">
Platform.Load("core","1.1.5");
try {
var email = "email@example.com"
var authEndpoint = "your auth endpoint"
var clientId = "your clientId"
var clientSecret = "your clientSecret"
var payload = {
client_id: clientId,
client_secret: clientSecret,
grant_type: "client_credentials"
};
var url = authEndpoint + '/v2/token'
var contentType = 'application/json'
var accessTokenRequest = HTTP.Post(url, contentType, Stringify(payload));
if (accessTokenRequest.StatusCode == 200) {
var tokenResponse = Platform.Function.ParseJSON(accessTokenRequest.Response[0]);
var mcAccessToken = tokenResponse.access_token
var rest_instance_url = tokenResponse.rest_instance_url
};
if (mcAccessToken != null && email != null) {
var headerNames = ["Authorization"];
var headerValues = ["Bearer " + mcAccessToken];
var jsonBody = {
"email": email,
"validators": [
"SyntaxValidator",
"MXValidator",
"ListDetectiveValidator"
]
}
var requestUrl = rest_instance_url + "/address/v1/validateEmail";
var validateEmail = HTTP.Post(requestUrl, contentType, Stringify(jsonBody), headerNames, headerValues);
var aRes = Platform.Function.ParseJSON(validateEmail.Response.toString());
var valid = aRes.valid;
if (valid) {
var status = true;
var message = "success";
}
else {
var failedValidation = aRes.failedValidation;
var status = false;
var message = "Please enter a valid email address";
};
}
else {
var status = false;
var message = "server error";
};
var response = {
"ok": status,
"message": message
}
Write(Stringify(response));
}
catch (e) {
Write("<br>" + Stringify(e))
}
</script>
Huge thank you to Baby Shark AMPscript competition Honourable mention, Lesley Higgins for this super useful script!
Day Two
On the second day of Scriptmas Greg Gifford gave to me... A robust way to find the first date of a given day of the week of next month. So, whether you have events on the first Friday of the month or if you update promotional offers on the 2nd Wednesday of the month, you can grab this snippet and adapt to your hearts content! No more manually reading calendars and working it out by hand!
Click here to see the Day Two Script
%%[
set @dayStr = "Tue,Mon,Sun,Sat,Fri,Thu"
/* Order and name of days included will determine what day of week (DOW) you are looking for */
/* You will want to exclude the DOW you are looking for from string and then go backwards of week in listing days */
/* Used in FOR statement for setting Day Shift */
set @dayRS = BuildRowSetFromString(@dayStr,",")
/* Setting up date pieces for next month */
set @now = now()
/* set @now = "2021-03-20" */
set @nextMonth_Date = dateadd(@now, 1, "M")
set @nextMonth_Month = DatePart(@nextMonth_Date, "M")
set @nextMonth_Year = DatePart(@nextMonth_Date, "Y")
/* Setting date to first of next month and then getting the day of week (e.g. Mon, Tue, etc.) */
set @firstOfnextMonth_Date = dateparse(concat(@nextMonth_Month,"/01/",@nextMonth_Year))
set @dayOfFirst = FORMATDATE(@firstOfnextMonth_Date,"DDDD")
IF @dayOfFirst == "Thu" THEN
SET @day_Shift = "-1"
ELSE
/* Default day shift of 0 meaning that the first is the correct day */
SET @day_Shift = 0
/* FOR loop to get the day shift to find first date matching day */
FOR @i = 1 to Rowcount(@dayRS) DO
set @row = Row(@dayRS,@i)
set @dayVal = Field(@row,1)
if @dayOfFirst == @dayVal then set @day_Shift = @i ENDIF
NEXT @i
ENDIF
/* Calculate actual date of first occurance of selected Day of Week */
SET @FirstWedOfMonth = DATEADD(@firstOfnextMonth_Date,@day_Shift,"D")
]%%
%%=V(@FirstWedOfMonth)=%%
Huge thanks for Greg Gifford for sharing this super useful snippet of code, so many use cases for this!
Day Three
Some people may tell you that the best things come in small packages, but here at H2 we know that isn't always the case.
So without further ado - On the third day of Scriptmas, Genna Matson gave to me... A quick and reliable way to create Data Extensions for SFMC Data Views. Grab this snippet of code and never worry about having to do the same old task every time you set up a business unit ever again!
Click here to see the Day Three Script
<script runat="server">
Platform.Load("core", "1.1");
var debug = 0;
var bizUnit = 10000000; // << business unit <<
var rootFolder = 123456; // << enter categoryId for top level in BU <<
//Create new folder
var folderCustomReports = "CustomReports_" + bizUnit
var folderDataViews = "DataViews_" + bizUnit
var newFolder = {
"Name" : folderDataViews,
"CustomerKey" : folderDataViews,
"Description" : "Data Views",
"ContentType" : "dataextension",
"IsActive" : "true",
"IsEditable" : "true",
"AllowChildren" : "false",
"ParentFolderID" : rootFolder
};
var folderStatus = Folder.Add(newFolder);
var viewsFolder = Folder.Retrieve({Property:"Name",SimpleOperator:"equals",Value:folderCustomReports});
var viewsFolderID = viewsFolder[0].ID;
if (debug == 1) {
Write('<br>folderStatus: ' + Stringify(folderStatus));
Write('<br>viewsFolderID: ' + Stringify(viewsFolderID));
}
var prox = new Script.Util.WSProxy();
// create DataView Report DEs
var dataViewRpt1 = bizUnit + "_DV_Unsubscribe";
var dataViewRpt2 = bizUnit + "_DV_Complaint";
var dataViewRpt3 = bizUnit + "_DV_Open";
var dataViewRpt4 = bizUnit + "_DV_Bounce";
var dataViewRpt5 = bizUnit + "_DV_Sent";
var dataViewRpt6 = bizUnit + "_DV_Click";
var dataViewRpt7 = bizUnit + "_DV_Job";
// Unsub
var de1 = {
"CustomerKey": dataViewRpt1,
"Name": dataViewRpt1,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "OYBAccountID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "ListID",
"FieldType": "Number"
},
{ "Name": "BatchID",
"FieldType": "Number"
},
{ "Name": "SubscriberID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "SubscriberKey",
"FieldType": "EmailAddress",
"IsRequired": true
},
{ "Name": "EventDate",
"FieldType": "Date",
"IsRequired": true
},
{ "Name": "IsUnique",
"FieldType": "Boolean"
},
{ "Name": "Domain",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
}
],
"CategoryID": viewsFolderID
};
// Complaint
var de2 = {
"CustomerKey": dataViewRpt2,
"Name": dataViewRpt2,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "OYBAccountID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "ListID",
"FieldType": "Number"
},
{ "Name": "BatchID",
"FieldType": "Number"
},
{ "Name": "SubscriberID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "SubscriberKey",
"FieldType": "EmailAddress",
"IsRequired": true
},
{ "Name": "EventDate",
"FieldType": "Date",
"IsRequired": true
},
{ "Name": "IsUnique",
"FieldType": "Boolean"
},
{ "Name": "Domain",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
}
],
"CategoryID": viewsFolderID
};
// Open
var de3 = {
"CustomerKey": dataViewRpt3,
"Name": dataViewRpt3,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "OYBAccountID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "ListID",
"FieldType": "Number"
},
{ "Name": "BatchID",
"FieldType": "Number"
},
{ "Name": "SubscriberID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "SubscriberKey",
"FieldType": "EmailAddress",
"IsRequired": true
},
{ "Name": "EventDate",
"FieldType": "Date",
"IsRequired": true
},
{ "Name": "IsUnique",
"FieldType": "Boolean"
},
{ "Name": "Domain",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
}
],
"CategoryID": viewsFolderID
};
// Bounces
var de4 = {
"CustomerKey": dataViewRpt4,
"Name": dataViewRpt4,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "OYBAccountID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "ListID",
"FieldType": "Number"
},
{ "Name": "BatchID",
"FieldType": "Number"
},
{ "Name": "SubscriberID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "SubscriberKey",
"FieldType": "EmailAddress",
"IsRequired": true
},
{ "Name": "EventDate",
"FieldType": "Date",
"IsRequired": true
},
{ "Name": "IsUnique",
"FieldType": "Boolean"
},
{ "Name": "Domain",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "BounceCategoryID",
"FieldType": "Number"
},
{ "Name": "BounceCategory",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "BounceSubcategoryID",
"FieldType": "Number"
},
{ "Name": "BounceSubcategory",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "BounceTypeID",
"FieldType": "Number"
},
{ "Name": "BounceType",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "SMTPBounceReason",
"FieldType": "Text",
"MaxLength": 2000
},
{ "Name": "SMTPMessage",
"FieldType": "Text",
"MaxLength": 2000
},
{ "Name": "SMTPCode",
"FieldType": "Number"
}
],
"CategoryID": viewsFolderID
};
// Sent
var de5 = {
"CustomerKey": dataViewRpt5,
"Name": dataViewRpt5,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "OYBAccountID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "ListID",
"FieldType": "Number"
},
{ "Name": "BatchID",
"FieldType": "Number"
},
{ "Name": "SubscriberID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "SubscriberKey",
"FieldType": "EmailAddress",
"IsRequired": true
},
{ "Name": "EventDate",
"FieldType": "Date",
"Ordinal" : 2,
"IsRequired": true
},
{ "Name": "IsUnique",
"FieldType": "Boolean"
},
{ "Name": "Domain",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
}
],
"CategoryID": viewsFolderID
};
// Click
var de6 = {
"CustomerKey": dataViewRpt6,
"Name": dataViewRpt6,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "OYBAccountID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "ListID",
"FieldType": "Number"
},
{ "Name": "BatchID",
"FieldType": "Number"
},
{ "Name": "SubscriberID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "SubscriberKey",
"FieldType": "EmailAddress",
"IsRequired": true
},
{ "Name": "EventDate",
"FieldType": "Date",
"Ordinal" : 2,
"IsRequired": true
},
{ "Name": "IsUnique",
"FieldType": "Boolean"
},
{ "Name": "Domain",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "URL",
"FieldType": "Text",
"MaxLength": 2000
},
{ "Name": "LinkName",
"FieldType": "Text",
"MaxLength": 2000
},
{ "Name": "LinkContent",
"FieldType": "Text",
"MaxLength": 2000
}
],
"CategoryID": viewsFolderID
};
// Job
var de7 = {
"CustomerKey": dataViewRpt7,
"Name": dataViewRpt7,
"Fields": [{
"Name": "AccountID",
"FieldType": "Number"
},
{ "Name": "AccountUserID",
"FieldType": "Number"
},
{ "Name": "JobID",
"FieldType": "Number",
"IsRequired": true
},
{ "Name": "EmailID",
"FieldType": "Number"
},
{ "Name": "FromName",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "FromEmail",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "SchedTime",
"FieldType": "Date",
"Ordinal" : 2
},
{ "Name": "PickupTime",
"FieldType": "Date",
"Ordinal" : 2
},
{ "Name": "DeliveredTime",
"FieldType": "Date",
"Ordinal" : 2
},
{ "Name": "EventID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "IsMultipart",
"FieldType": "Boolean"
},
{ "Name": "JobType",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "JobStatus",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "ModifiedBy",
"FieldType": "Number"
},
{ "Name": "ModifiedDate",
"FieldType": "Date",
"Ordinal" : 2
},
{ "Name": "EmailName",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "EmailSubject",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "IsWrapped",
"FieldType": "Boolean"
},
{ "Name": "TestEmailAddr",
"FieldType": "EmailAddress"
},
{ "Name": "Category",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "BccEmail",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "OriginalSchedTime",
"FieldType": "Date",
"Ordinal" : 2
},
{ "Name": "CreatedDate",
"FieldType": "Date",
"Ordinal" : 2
},
{ "Name": "CharacterSet",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "IPAddress",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "SalesForceTotalSubscriberCount",
"FieldType": "Number"
},
{ "Name": "SalesForceErrorSubscriberCount",
"FieldType": "Number"
},
{ "Name": "SendType",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "DynamicEmailSubject",
"FieldType": "Text",
"MaxLength": 500
},
{ "Name": "SuppressTracking",
"FieldType": "Boolean"
},
{ "Name": "SendClassificationType",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "SendClassification",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "ResolveLinksWithCurrentData",
"FieldType": "Boolean"
},
{ "Name": "EmailSendDefinition",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "DeduplicateByEmail",
"FieldType": "Boolean"
},
{ "Name": "TriggererSendDefinitionObjectID",
"FieldType": "Text",
"MaxLength": 50
},
{ "Name": "TriggeredSendCustomerKey",
"FieldType": "Text",
"MaxLength": 50
}
],
"CategoryID": viewsFolderID
};
var res = prox.createBatch("DataExtension", [ de1, de2, de3, de4, de5, de6, de7 ]);
</script>
A massive thank you to Genna Matson for this super useful script! Grab it, use it, save yourself some time!
Day Four
We may all be counting down the seconds to the big day, but sometimes you don't need to worry about that... So - on the fourth day of Christmas, Rafal Wolsztyniak gave to me - A nifty trick to remove seconds from a timestamp!
Click here to see the Day Four Script
Select Cast(a.dateField as DateTime2(0)) as DateTimeWithSeconds
, Concat(Year(a.dateField), '-', Right(Concat(0, Month(a.dateField)), 2), '-', Right(Concat(0, Day(a.dateField)), 2), ' ', Right(Concat(0, Datepart(hour, a.DateField)), 2), ':', Right(Concat(0, Datepart(minute, a.DateField)), 2)) as CustomDateTimeWithoutSeconds
From dataSet a
Big thanks Rafal for this useful little snippet! When you don't need seconds, this is a great way to simplify your data!
Day Five
On the Fifth day of Scriptmas Jason Cort gave to me... FIIIVE GOLD...Wait, we did that joke last time. Dangit. Well, he gave us a useful little script to see how your automations have performed over time! Just pop the names in and add in your MID and you'll be able to see what's happened! (Great for when you come back after Christmas and don't want to check everything manually!)
Click here to check out the Day Five Script
<table>
<tr>
<th>AutomationName</th>
<th>StartTime</th>
<th>Status</th>
</tr>
<script type="javascript" runat="server">
Platform.Load("Core","1.1.1");
var mid = ""; // Put the MID in here
var prox = new Script.Util.WSProxy(),
objectType = "AutomationInstance",
cols = ["Name","Status","StartTime"];
filter = {
Property: "Name",
SimpleOperator: "IN",
Value: [] }; // Fill in your list of Automations you want to see the history of here (include Quotes and commas between them!)
moreData = true,
reqID = null;
prox.setClientId({"ID": mid});
while(moreData) {
moreData = false;
var data = reqID == null ?
prox.retrieve(objectType, cols,filter) :
prox.getNextBatch(objectType, reqID);
if(data != null) {
moreData = data.HasMoreRows;
reqID = data.RequestID;
if(data && data.Results) {
for(var i=0; i< data.Results.length; i++) {
var automationName = data.Results[i].Name;
var automationStart = data.Results[i].StartTime;
var automationStatus = data.Results[i].Status;
Write("<tr><td>" + automationName + "</td><td>" + automationStart + "</td><td>" + automationStatus + "</td></tr>")
}
}
}
}
</script>
</table>
Massive thank you to Jason for sharing this!
Day Six
There are some things that if you don't make SFMC write it down when it happens and sometimes the out of the box Send Log just isn't enough! On the sixth day of Christmas, Aysha Marie Zouain gave to me... A template for a custom Send Logging solution! Just pop this in your email template and see everything logged for you to analyse at a later date!
Click here to see the Day Six Script
%%[ InsertDE('ent.B2C_SendLog','p1_utm_term',__AdditionalEmailAttribute1,'p2_utm_source',__AdditionalEmailAttribute2,'p3_utm_campaign',__AdditionalEmailAttribute3,'p4_campaigncode',__AdditionalEmailAttribute4,'p5_brand',__AdditionalEmailAttribute5,'p6_onsite',__AdditionalEmailAttribute6,'p7_business',__AdditionalEmailAttribute7,'p8_dept',__AdditionalEmailAttribute8,'p9_',__AdditionalEmailAttribute9,'p10_',__AdditionalEmailAttribute10,'dateadded',NOW(),'JobID',JobID,'BatchID',_JobSubscriberBatchID,'ListID',ListID,'SubscriberID',SubscriberID,'SubscriberKey',_subscriberKey,'ClientID',memberid) ]%%
Big thanks to Aysha for sending this super useful little solution for custom send logging!
Day Seven
Building user interfaces can be tricky, particularly when the content is dynamic.
If you ever find yourself needing to output a folder structure or navigation "breadcrumbs" - then have we got the SSJS script for you!
This handy little code snippet from @Adam Spriggs (inspired by none other than Zuzanna Jarczynska) will output a traditional breadcrumbs folder structure on your cloud page; great for helping to navigate your data extension or content folders!
Click to see the Day Seven Script
// via https://sfmarketing.cloud/2019/10/14/find-a-data-extension-and-its-folder-path-using-ssjs/
function getFolderPath(categoryID) {
var list = "";
var path = function(id) {
if (id > 0) {
var results = Folder.Retrieve({Property:"ID",SimpleOperator:"equals",Value:id});
list = results[0].Name + " > " + list;
return path(results[0].ParentFolder.ID);
} else {
return id;
}
};
path(categoryID);
return list;
}
Huge thank you to both Adam for this function and Zuzanna for the inspiration!
Day Eight
We all know that whilst it's best data practice to capture a forename and a surname as two different fields, some places just don't have that capability! But, don't worry Jacob Edwards has your back! On the Eighth day of Scriptmas, Jacob gave to me - a sweet template for splitting names in SQL in SFMC!
Click to see the Day Eight Script
SELECT
LEN(FullName) as firstNameLength
,CHARINDEX(' ',REVERSE(FullName),0) as lastSpaceIndex
,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName),0) as endFnameIndex
,Substring(FullName,1,LEN(FullName)-CHARINDEX(' ',REVERSE(FullName),0)) as FirstName
,RIGHT(FullName, (CHARINDEX(' ',REVERSE(FullName),0))) as LastName
,Substring(RIGHT(FullName, (CHARINDEX(' ',REVERSE(FullName),0))),1,2) as LastNameInitial
FROM [Names]
Thanks Jacob for this super useful little query!
Day Nine
We all know the limitations of building form when building a Cloud Page using the native Smart Capture capabilities. Whether you're looking for unique data capture options or some bespoke processing, sometimes you need something more unique to your business. On the ninth day of Scriptmas, Tony Zupancic gave to me - an epic starting point to build custom forms! Just add in your fields, set the RequestParameters and point the processing URL to your new Cloud Page itself you're good to go!
Click here to see the Day Nine script
%%[
set @EmailAddress = RequestParameter('EmailAddress')
set @FullName = RequestParameter('FullName')
]%%
<!DOCTYPE html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Page Title</title>
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
</head>
<body>
<!-- Check to see if EmailAddress is empty -->
%%[
if Empty(@EmailAddress) then
]%%
<!-- If EmailAddress is empty show the form -->
<!-- Make ACTION page URL -->
<form method="POST" action="processingURL.com">
<div class="form-group row">
<label for="EmailAddress">Email</label>
<div class="col-sm-10">
<input type="email" class="form-control" name="EmailAddress" id="EmailAddress" placeholder="What's your email address?">
</div>
</div>
<div class="form-group row">
<label for="FullName">Full Name</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="FullName" id="FullName" placeholder="What's your name?">
</div>
</div>
<div class="form-group row">
<div class="col-sm-10">
<button type="submit">Submit</button>
</div>
</div>
</form>
<!-- If EmailAddress is not empty process the form and show thank you message -->
%%[
Else
/*UpsertData and UpdateData*/
UpsertData('FormDE',1,'EmailAddress',EmailAddress,'FullName',FullName)
]%%
<h1> Thank you for your submission! </h1>
%%[EndIF]%%
</html>
Huge thanks to Tony Zupancic for this super useful template!
Day Ten
There are times when you really need to have one-to-many relationships in your data and there are times where a one-to-many relationship may just cause you problems (especially with things like Data Filters and Journey Builder decision splits). Fortunately, there are ways and means to bring your data to single rows where you'll be able to use different operators to get the result you need, whether it's 1:M or 1:1 with delimited strings!
So without any further ado... On the tenth day of Scriptmas, Greg Gifford gave to me - a super helpful SQL query to convert a set of many rows into a delimited string! (Just make sure your target Data Extensions can handle all the data that could come their way!)
Click here to see the Day Ten script
SELECT a.id,
emailStr = STUFF(
(
SELECT ',' + b.email
FROM [myDE] b
WHERE a.id = b.id
FOR XML PATH('')
), 1, 1, '')
FROM [myDE]
GROUP BY ID
Thanks Greg for this super little SQL query to help make data more accessible to more of SFMC!
Day Eleven
One of those little pains that many SFMC users need to overcome is the fact that you can't get details about a Job from a child business unit in the enterprise BU from the _Job Data View (even though you can get everything else!). Thankfully it's still possible to get those details without having to switch business units thanks to the SOAP API. On the eleventh day of Scriptmas, Jason Cort gave to me - A script that pulls all of the JobIDs and EmailNames from a child BU thanks to WSProxy.
Click here to see the Day Eleven script
<script type="javascript" runat="server">
Platform.Load("Core","1.1.1");
var targetDE = "" // Put in the name of a Data Extension configured with JobID as a PrimaryKey, EmailName and SendDate
var mid = ""; // Put the target business unit MID in here
var prox = new Script.Util.WSProxy(),
objectType = "Send",
cols = ["EmailName","ID","SendDate"]; // These are the attributes retrieved from the Send SOAP API Object - https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/send.html
filter = {
Property: "ID",
SimpleOperator: "greaterThan",
Value: "0" }; // This will get you all of the send jobs for the BU in the MID field
moreData = true,
reqID = null;
prox.setClientId({"ID": mid});
while(moreData) {
moreData = false;
var data = reqID == null ?
prox.retrieve(objectType, cols,filter) :
prox.getNextBatch(objectType, reqID);
if(data != null) {
moreData = data.HasMoreRows;
reqID = data.RequestID;
if(data && data.Results) {
for(var i=0; i< data.Results.length; i++) {
var emailName = data.Results[i].EmailName;
var emailJobID = data.Results[i].ID;
var emailSendDate = data.Results[i].SendDate;
var rowUpdate = Platform.Function.UpsertData(targetDE,["JobID"],[emailJobID],["EmailName","SendDate"],[emailName,emailSendDate]);
}
}
}
}
</script>
Thanks Jason for this helpful little script!
Day Twelve
As we all wrap up for the final day of Scriptmas 2021, we thought that we would finish up with an epic little trick that will save time for everyone who uses Postman to interact with the SFMC APIs. Simply set up a new collection and add todays script in the collection Pre-request Script tab (and make sure the variables match your environment!) to feel the benefits for all of 2022.
On the twelfth and final day of Scriptmas 2021, Adam Spriggs gave to me - An awesome tool to make Postman pre-fetch your authentication token so you don't have to jump between tabs when you're building or testing some of the SFMC APIs! Huge time saving and it's neater and easier to boot.
Click here to see the Day Twelve script
var authEndpoint = pm.environment.get("authEndpoint")
var clientId = pm.environment.get("clientId")
var clientSecret = pm.environment.get("clientSecret")
pm.sendRequest({
"url": `${authEndpoint}v2/token`,
"method": "POST",
"header": {
"Content-Type": "application/json"
},
"body": {
"mode": "raw",
"raw": JSON.stringify({
"grant_type": "client_credentials",
"client_id": clientId,
"client_secret": clientSecret
})
}},
function(err, response) {
var jsonData = response.json()
if(jsonData && jsonData.access_token) {
pm.environment.set("accessToken", jsonData.access_token)
}
}
)
Massive thank you to Adam Spriggs for this time saving revelation.
Thank you everybody for your contributions, thank you to the community for an amazing 2021 in the face of such a tricky year. Thank you everyone for reading all of these scripts, tips and tricks. If you've read any of these and want to get involved with the H2 community, come and join us on slack!