---
title: "Sort or Filter Data Records"
slug: "sort-or-filter-data"
description: "Mapp Engage functions that sort, filter, merge, and deduplicate related data records for use in ForEach loops."
status: "update"
updated: 2026-11-04T12:37:34Z
published: 2026-11-04T12:37:34Z
canonical: "docs.mapp.com/sort-or-filter-data"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.mapp.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

| Function | Description |
| --- | --- |
| `ecx:sort` | Returns entries sorted in ascending or descending order based on the values in a target column. |
| `ecx:filter` | Searches a column for specific values and returns matching entries. |
| `ecx:filterDateRange` | Searches a column for date values between the specified start and end dates and returns the entries that match. |
| `ecx:filterDateTimeRange` | Searches a column for values between a specified start and end date and time and returns the entries that match. |
| `ecx:filterDuplicates` | Returns only distinct entries from a target source with duplicate rows filtered out. |
| `ecx:filterDuplicatesByField` | Returns the entries of a target source without entries that have the same value in a specified column. |
| `ecx:merge` | Merges multiple target sources into a single object. |
| `ecx:related` | Gets the value for the specified key from the given related data set. |

---

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

```plaintext
${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:

```plaintext
<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

| Parameter | Description |
| --- | --- |
| `object` | Specifies the source of the data to sort. |
| `string` | Specifies the target column to sort. The column sorted does not necessarily have to be the column displayed. For example, you could sort the data according to the purchase date column even if only the price and product name are displayed in the message. |
| `string` | Specifies the sort order. If left empty, the default is ascending. To sort in descending order, enter 'desc'. Enter 'asc' to specify ascending sort order. |

---

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

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

### Structure

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

### Parameters

| Parameter | Description |
| --- | --- |
| `object` | Specifies the location of the data to filter. |
| `string` | Specifies the column to search. |
| `string` | The operator used for the filter: `==`, `!=`, `empty`, `notempty`, `&lt;`, `&gt;`, `&lt;=`, `&gt;=`, `startswith`, `endswith`, `contains`, `containsignorecase`, or `regex`. If the operator used is not compatible with the data type of the searched column, an empty result is returned. |
| `string` | The value to be used to query the column. |

---

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

```plaintext
<%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

| Parameter | Description |
| --- | --- |
| `object` | Specifies the location of the data to filter. |
| `string` | Specifies the column that contains the date value evaluated by the filter. |
| `string` | Specifies the beginning of the date range. The date format is either DD.MM.YYYY or yyyy-MM-dd. If left empty, the selection is limited by end date only. |
| `string` | Specifies the end of the date range. The date format is either DD.MM.YYYY or yyyy-MM-dd. If left empty, the selection is limited by start date only. |

---

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

```plaintext
<%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

| Parameter | Description |
| --- | --- |
| `object` | Specifies the location of the data to filter. |
| `string` | Specifies the column that contains the date value evaluated by the filter. |
| `string` | Specifies the beginning of the date range. The date format is either dd.MM.yyyy hh:mm:ss or yyyy-MM-dd hh:mm:ss. If left empty, the selection is limited by the end date only. |
| `string` | Specifies the end of the date range. The date format is either dd.MM.yyyy hh:mm:ss or yyyy-MM-dd hh:mm:ss. If left empty, the selection is limited by start date only. |

---

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

```plaintext
<%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]
> 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

| Parameter | Description |
| --- | --- |
| `object` | Specifies the source of the data to filter. |

---

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

```plaintext
<%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](/v1/docs/filter-and-sort-ecommerce-data).

### Structure

`ecx:filterDuplicatesByField(object, string)`

### Parameters

| Parameter | Description |
| --- | --- |
| `object` | Specifies the source of the data. |
| `string` | Specifies the column used to identify duplicates. |

---

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

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

### Structure

`ecx:merge(object...)`

### Parameters

| Parameter | Description |
| --- | --- |
| `object` | Specifies the first set of data to be included in the output. Additional data sets are added as a list, separated by commas. |

---

## 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

```plaintext
${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:

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

#### Non-Unique Set

Use this syntax when the key may return multiple results:

```plaintext
<%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:

| email | rsvp_status |
| --- | --- |
| alice@example.com | Going |
| bob@example.com | Not Going |

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

```plaintext
<%${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:

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

## Related

- [Use Related Data in a Message](/use-related-data-in-a-message.md)