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 returns the records with the key 01142012 from 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', '01142012'), 'CurrentFare', 'asc')}"%> <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 Article, 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', '07.04.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:filterDateTimeRange(user.relatedAttribute['Purchases'], 'DateTime', '2011-04-01 13:00:00', '2011-04-01 22:00:00')}"%> <%${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 User ID contains the email address of the customer who made the purchase. Because a customer can make several purchases, the key is not unique. The records that match the recipient's email address are 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.Email))}"%> <%${item.ProductID}%> <%/ForEach%>

In this example, the function ecx:related returns all records whose key matches the recipient's email address. The function ecx:filterDuplicates removes records with identical values. The value in the ProductID column is inserted in the message for each remaining record.

Note

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:filterDuplicatesByField

The function ecx:filterDuplicatesByField removes entries that have the same value in a specified column. In contrast to ecx:filterDuplicates, it compares only this column, not all columns. The value returned is an object type.

Example

A product can appear on the wishlist of a contact more than once. The following loop inserts each product only once, based on its SKU:

<%ForEach var="item" items="${ecx:filterDuplicatesByField(user.wishlistProducts, 'productSKU')}"%>
  <%${item['productName']}%><br>
<%/ForEach%>

For more examples with eCommerce data, see Filter and Sort eCommerce Data.

Structure

ecx:filterDuplicatesByField(object, string)

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:

To personalize the RSVP status in your email content, use the following syntax:

<%${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')}