Filter and Sort eCommerce Data

You can filter, sort, and remove duplicates from the eCommerce tables, for example to show only the three most expensive products of the last seven days. The functions work with all eCommerce tables (wishlist, abandoned cart, abandoned browse, transactions, and recommended products) and with both data sources, the Product Catalog and related data.

The functions return a list of products. Insert the list with a ForEach loop. For the references of the eCommerce tables, see Placeholders for eCommerce Data. For the function reference, see Sort or Filter Data Records.

Filter by Date

The function ecx:filterDateRange returns the products whose date is within a range. The eCommerce tables store the date in the field timestamp.

Last 6 days

An empty end date means that the range has no end.

<%ForEach var="item" items="${ecx:filterDateRange(user.wishlistProducts, 'timestamp', ecx:formatDate(ecm:addInterval(date.Today, '-6d'), 'yyyy-MM-dd', ecm:timeZone('Europe/Berlin'), 'de', false), '')}"%>
  <%${item['productName']}%><br>
<%/ForEach%>

Between two dates

<%ForEach var="item" items="${ecx:filterDateRange(user.wishlistProducts, 'timestamp', ecm:toDate('2024-10-10 13:45:00', null), ecm:toDate('2024-12-10 14:45:00', null))}"%>
  <%${item['productName']}%><br>
<%/ForEach%>

Filter by Field

The function ecx:filter returns the products whose field matches a value. The following example returns the products whose name starts with shoes:

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

Operators

Note

If the operator does not fit the data type of the field, the result is empty.

Sort and Remove Duplicates

Remove duplicates

The function ecx:filterDuplicatesByField removes products with the same value in a field, for example the same SKU:

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

Sort

The function ecx:sort sorts the products by a field, in ascending (asc) or descending (desc) order. The attribute max of the ForEach loop limits the number of products. The following example shows the 5 newest products:

<%ForEach var="item" items="${ecx:sort(user.wishlistProducts, 'timestamp', 'desc')}" max="5"%>
  <%${item['productName']}%><br>
<%/ForEach%>

Combine sorting and removing duplicates

The following example shows the 5 newest products without duplicates:

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

Examples

Top 3 abandoned cart products of the last 7 days, sorted by price

The abandoned cart of a contact contains the following products:

The following loop filters the products of the last 7 days, sorts them by price in descending order, and shows the first 3:

<%ForEach var="cart" items="${ecx:sort(ecx:filterDateRange(user.abandonedCartProducts, 'timestamp', ecx:formatDate(ecm:addInterval(date.Today, '-7d'), 'yyyy-MM-dd', ecm:timeZone('Europe/Berlin'), 'de', false), ''), 'productPrice', 'desc')}" max="3"%>
  <%${cart['productName']}%> - <%${cart['productPrice']}%><br>
<%/ForEach%>

Top 3 wishlist products from related data, sorted by name

The related data set myrdlistwithproductdata contains the following products of the wishlist:

The following loop sorts the products by name in ascending order and shows the first 3:

<%ForEach var="wishlist" items="${ecx:sort(user.wishlistProductsRD['myrdlistwithproductdata'], 'productName', 'asc')}" max="3"%>
  Wishlist Description: <%${wishlist['description']}%><br>
  Wishlist Price: <%${wishlist['price']}%><br><br>
<%/ForEach%>