This article explains how to retrieve records using complex filters with server-side Javascript in Salesforce Marketing Cloud.
Not a fan of reading? Jump to the code snippet.
Why do we need complex filters?
More often than not, retrieving records from a Data Extension is a matter of using the Lookup or LookupRows function in AMPscript.
But matters get complicated when there are multiple conditions involved and they need to be more complex than a simple x = y.
In AMPscript, this implies working with objects and creating lines upon lines of code to make something unnecessary complicated and unreadable.
Instead, we are going to examine how the server-side Javascript can be used to create complex conditions (or filters) and write a clean and readable code.
Exercice
For the purpose of this tutorial, let’s imagine a situation where we need to retrieve the data about a person from a Data Extension, but only the records of the people who are adults.
The problem is that adulthood is a concept that changes depending on the country and legal rights.
We therefore need to create a set of universal rules to determine if a person has reached adulthood.
Data Extension
The data about a person’s age is stored in 2 different fields: Age and Adult.
- Age is a Number field that can also be empty.
- Adult is a Boolean field with False as a default value.
The Adult field is only set to True when the country of the person considers him or her an adult, no matter the age.
| FirstName | Age | Adult |
| Jon | 19 | False |
| Martha | False | |
| Warlock | 16 | True |
| Roger | True |
Simple filter
As the name says, this filter is simple and identifies the adult based only on the Age field, which should be greater than 17.
Note that in order for this to work, we need to use an External Key and not a Data Extension name.
<script runat="server">
/*************************************************
Filter: (Age > 17)
*************************************************/
Platform.Load("core","1.1.1");
var ExternalKey = "0000-1111-2222-4444-6666"
var DE = DataExtension.Init(ExternalKey);
var simpleFilter = {
Property: "Age",
SimpleOperator: "greaterThan",
Value: 17
}
var adults = DE.Rows.Retrieve(simpleFilter);
</script>Complex filters
Let’s add a layer of complexity by adding another rule: the Age field can’t be empty.
In order to combine the 2 filters with simple operators, we to place a logical operator in between.
<script runat="server">
/*************************************************
Filter: (Age != null && Age > 17)
*************************************************/
Platform.Load("core","1.1.1");
var ExternalKey = "0000-1111-2222-4444-6666"
var DE = DataExtension.Init(ExternalKey);
var filter1 = {
Property: "Age",
SimpleOperator: "isNotNull"
}
var filter2 = {
Property: "Age",
SimpleOperator: "greaterThan",
Value: 17
}
var complexFilter = {
LeftOperand: filter1,
LogicalOperator: "AND",
RightOperand: filter2
}
var adults = DE.Rows.Retrieve(complexFilter);
</script>Nested complex filters
As we are advancing towards our goal, let’s define a final rule!
A person is considered an adult if the Age field is not empty and greater than 17, or… if the Adult field is set to True.
In server-side Javascript, it’s just a matter of nesting a complex condition within a left or right operand of the parent complex condition.
<script runat="server">
/*******************************************************
Filter: (Adult == true || (Age > 17 && Age != null))
*******************************************************/
Platform.Load("core","1.1.1");
var ExternalKey = "0000-1111-2222-4444-6666"
var DE = DataExtension.Init(ExternalKey);
var filter1 = {
Property: "Adult",
SimpleOperator: "equals",
Value: true
}
var filter2 = {
Property: "Age",
SimpleOperator: "isNotNull"
}
var filter3 = {
Property: "Age",
SimpleOperator: "greaterThan",
Value: 17
}
var filter23 = {
LeftOperand: filter2,
LogicalOperator: "AND",
RightOperand: filter3
}
var complexFilter = {
LeftOperand: filter1,
LogicalOperator: "OR",
RightOperand: filter23
}
var adults = DE.Rows.Retrieve(complexFilter);
</script>Rewrite and simplify
As a matter of fact, we don’t need to create separate objects to store the filters. Combining everything in a single object is a more clean and elegant solution.
<script runat="server">
Platform.Load("core","1.1.1");
var DE = DataExtension.Init("0000-1111-2222-4444-6666");
var complexFilter = {
LeftOperand: {
Property: "Adult",
SimpleOperator: "equals",
Value: true
},
LogicalOperator: "OR",
RightOperand: {
LeftOperand: {
Property: "Age",
SimpleOperator: "isNotNull"
},
LogicalOperator: "AND",
RightOperand: {
Property: "Age",
SimpleOperator: "greaterThan",
Value: 18
}
}
}
var adults = DE.Rows.Retrieve(complexFilter);
</script>Object operators
There is a good number of object operators that we can use to build our filters. Please consider the official documentation on filter operators and logical operators to get the full picture.
Filter operators (most objects)
- equals
- notEquals
- greaterThan
- lessThan
- isNull
- isNotNull
- greaterThanOrEqual
- lessThanOrEqual
- between
- IN
- like
Logical operators
- AND
- OR
Full code
And we are finished! Keep in mind that this technique
<script runat="server">
Platform.Load("core","1.1.1");
var DE = DataExtension.Init("0000-1111-2222-4444-6666");
var complexFilter = {
LeftOperand: {
Property: "Adult",
SimpleOperator: "equals",
Value: true
},
LogicalOperator: "OR",
RightOperand: {
LeftOperand: {
Property: "Age",
SimpleOperator: "isNotNull"
},
LogicalOperator: "AND",
RightOperand: {
Property: "Age",
SimpleOperator: "greaterThan",
Value: 18
}
}
}
var adults = DE.Rows.Retrieve(complexFilter);
if(adults.length > 0 ) {
var message = "We have " + adults.length + " adult(s) in our group.";
} else {
var message = "We have no adults in our group."
}
Write("<h1>" + message + "</h1>");
</script>
Have I missed anything?
Please poke me with a sharp comment below or use the contact form.
