Sort or Filter Data Records

This section covers functions that are used to sort, filter, and combine collections of data records. These functions operate on structured data, such as lists or key/value collections, and are most commonly used with related data and ForEach loops.

Typical use cases include:

• Sorting records by a specific column (for example, price or date)

• Filtering records based on values or date ranges

• Removing duplicate records

• Merging multiple data sources into a single collection

These functions return collections of records, not single values. To process or display the results, they are typically used inside a ForEach loop.

Function Overview

​ecx:sort​

The function ecx:sort returns entries sorted in ascending or descending order based on the values in a target column. The value returned is an object type.

Example

A related data set includes an updated list of discount fares for popular destinations. This data is inserted into a message and sorted from lowest to highest fare.

The data is stored in an unlinked related data set named Fares, which uses the date the data was imported as the key column. The date used in this example is January 14, 2012. The sort function is constructed as follows:

${ecx:sort(ecx:related('Fares', '01142012'), 'CurrentFare', 'asc')}

Note that in this example, the function ecx:related determines the key column in the unlinked related data set. Because the key column for this data set is not unique, more than one value can be returned. To insert the sorted data into a message, the function must be set inside a ​ForEach loop. The values shown are set in a table with a column that displays the destination and a second column for the current fare. The HTML that defines the table and inserts the data is constructed as follows:

<table> <tbody> <%ForEach var="count" items="${ecx:sort(ecx:related('Fares','123457'), 'CurrentFare', 'desc')}"%> <tr> <td><% ${count['Destination']}%></td> <td><% ${count['CurrentFare']}%></td> </tr> <%/ForEach%> </tbody> </table>

Structure​

ecx:sort(object, string, string)

Parameters​

​ecx:filter​

The function ecx:filter searches a column of data for a specific value and returns matching entries. The value returned is an object type.

Example

The related data set ​Purchases is linked to the attribute CustomerID. The data sets saved with the key CustomerID are not unique, as one customer can purchase multiple items. In the related data set, the name of the purchased article is saved in the column ​Articles, according to the key.

A message sent to the customer displays all articles purchased on or after 05 July 2011. Because a single customer can have purchased multiple items, the information is inserted into the message using a ​ForEach​ loop:

<%ForEach var="count" items="${ecx:filter(user.relatedAttribute['Purchases'], 'Date', '<=','2011-07-05')}"%> <%${count.Article}%> <%/ForEach%>

Structure​

ecx:filter(object, string, string, string)

Parameters​

​ecx:filterDateRange​

The function ecx:filterDateRange searches a column for date values between specified start and end dates and returns the entries that match. The value returned is an object type.

Example

A message includes all purchases from a linked related data set named ​Purchases which took place between 1 April 2011 and 7 April 2011. Purchase dates are saved in a column named ​Date. For purchases that took place in the selected time period, the data in the columns ​Article and ​Description are inserted into the email message. The ​ForEach​ loop that inserts the information into a message is constructed as follows:

<%ForEach var="count" items="${ecx:filterDateRange(user.relatedAttribute['Purchases'],'Date', '01.04.2011','01.07.2011')}"%>
<%${count.Article}%>
<%${count.Description}%>
<%/ForEach%>

Structure​

ecx:filterDateRange(object, string, string, string)

Parameters​

​ecx:filterDateTimeRange​

The function ecx:filterDateTimeRange searches a column for values between a specified start and end date and time and returns the entries that match. The value returned is an object type. In contrast to the function ecx:filterDateRange, this function also considers hours, minutes, and seconds.

Example

A message includes all purchases from a linked related data set named ​Purchases which took place on 1 April 2011 between 1:00 p.m. and 10:00 p.m. The date and time of each purchase are saved in a column named ​DateTime​. For the selected purchases, the data in the columns ​Article​and ​Description are inserted into the message. The ​ForEach​ loop that inserts the information into a message is constructed as follows:

<%ForEach var="count" items="${ecx:filterDateRange(user.Related Attribute['purchases'], ['DateTime'], ecm:toDate('2011-04-01 13:00:00', ecm:timeZone('Europe/London')),ecm:toDate('2011-04-01 22:00:00', ecm:timeZone('Europe/London')))}"%> <%${count.Article}%> <%${count.Description}%> <%/ForEach%>

Structure​

ecx:filterDateTimeRange(object, string, string, string)

Parameters​

​ecx:filterDuplicates​

The function ecx:filterDuplicates returns only distinct entries from a target source with duplicate rows filtered out. The value returned is an object type. This function is only useful for non-unique data sets. Data sets with unique keys will not have duplicates.

Example

A related data set named ​"Q12021"​ contains purchase records for the first quarter of the year. The key column contains non-unique values. The data set contains a column that identifies the customer who made the purchase called ​"User ID​", which is linked to the user attribute user. Email in Engage. Records from the related data set that corresponds to the recipient's email address are to be displayed in a message. Duplicate records should not be inserted into the message a second time.

The records are inserted into the message using a ​ForEach​ loop:

<%ForEach var="item" items="${ecx:filterDuplicates(ecx:related('Q12021','User ID'))}"%> <%item.ProductID%> <%/ForEach%>

In this example, the function ecx:related is used to designate the ​User ID​ as the key column in the related data set. The function ecx:filterDuplicates filters for multiple records with identical values. For each recipient with an email address that matches the value in the ​User ID​ column, the value in the ​ProductID column is inserted in the message.

This function only considers a record to be a duplicate when the values in all columns are duplicated. If the related data set in this example contains purchase records for a customer who purchased the same item on two different dates, the product ID for this item would be displayed twice in the message. The record would not be considered a duplicate because the values in the purchase date column are different.

Structure​

ecx:filterDuplicates(object)

Parameters​

​ecx:merge​

The function ecx:merge merges multiple target sources into a single object. Values are kept in a set to prevent duplicates. The value returned is an object type.

Example

All customer purchases are saved in a related data set named ​Purchases. The data set is linked to the customer ID.

A message includes a special offer to customers who have either:

• purchased an electronic product (the value ​Electronic​ appears in the ​ProductType​ column ), or

• paid with their credit card (the value ​Credit appears in the ​Payment column).

The expression that combines these values into a single data set is constructed as follows:

${ecx:merge(ecx:filter(user.relatedAttribute['Purchases'], 'ProductType', '==','Electronic'), ecx:filter(user.relatedAttribute['Purchases'], 'Payment', '==', 'Credit'))}

Structure​

ecx:merge(object...)

Parameters​

​ecx:related​

The ecx:related function retrieves the value associated with a specified key from a related data set. It returns an object type and is commonly used with unlinked related data sets. These values can be filtered or sorted using functions such as ecx:filter or ecx:sort.

Key Characteristics

• Retrieves object values from related data sets based on a specified key

• Supports both unique and non-unique keys

• Frequently used to access data in unlinked related data sets

• Can be combined with other functions such as ecx:filter and ecx:sort

Syntax

${ecx:related('dataset', 'key')}

Parameters:

• dataset: Name of the related data set.

• key: Key value used to look up data in the related data set.

Use with Unlinked Related Data Sets

The function supports unlinked related data sets, including both unique and non-unique key scenarios.

Unique Set

Use this syntax when the key returns a single result:

<%${ecx:related('dataset', 'key')['column']}%>

Non-Unique Set

Use this syntax when the key may return multiple results:

<%ForEach var="count" items="${ecx:related('dataset', 'key')}" max="5"%>
  <%${count['column']}%>
<%/ForEach%>

Example: Unique Set

Consider a related data set named Event_Attendance that stores RSVP statuses for an event:

<%${ecx:related('Event_Attendance', user.Email)['rsvp_status']}%>

Result:

• A contact with alice@example.com will see: Going

• A contact with bob@example.com will see: Not Going

Example: General Use Case

A related data set, Event_Calendar, stores event details by location. It is not linked to contact attributes.

To return events for London:

${ecx:related('Event_Calendar', 'London')}