Filters

Filters

Filter definitions for selecting data in fetch and delete operations.

When to Use

Rules

• Filter ID MUST match file name without .filter.xml (e.g., fetch-products.filter.xml → id="fetch-products.filter")

• PX/CX fetch MUST include <criterion fieldName="name" operator="equals" value="{TableName}"/>

• Dynamic values MUST use simple: prefix: value="simple:${header.myHeader}" — without it, the value is literal

• Use equals operator (NOT equal — common mistake)

• Delta filters: use BOTH bounds (greaterThan lower + lessOrEqual upper) to prevent missing records

• Filter file format: <filters> root element, no pfx: namespace prefix, no <?xml> declaration

Filter Structure

<filter>

The root filter element. It may contain at most one top-level logical combinator (<and>, <or>, or <not>).

Attributes

Child Elements (choice, 0..1)

Logical Combinators

<and>

All child criteria/combinators must be true for a record to match.

<or>

At least one child criterion/combinator must be true for a record to match.

<not>

Negates all child criteria/combinators. A record matches if the children evaluate to false.

All combinators share these attributes:

All combinators may contain: <criterion>, <and>, <or>, <not> (0..unbounded).

Criterion Element

<criterion>

Defines a single condition on a field.

All Operators

Equality Operators

Comparison Operators

String Matching Operators (Case-Sensitive)

String Matching Operators (Case-Insensitive)

Null-Check Operators

Set Operators

Custom Operator

resultFields

The resultFields attribute is available on <filter>, <and>, <or>, and <not> elements. It specifies a comma-separated list of field names to include in the fetch result. When omitted, all fields are returned.

Using resultFields improves performance by reducing the data transferred from the Pricefx server.

sortBy

The sortBy attribute is available on the <filter> element.

• Plain field name for ascending order: sortBy="sku"

• Prefix with - for descending order: sortBy="-modifiedDate"

Examples

Simple Filter -- Single Criterion

One filter per file, wrapped in <filters>:

<filters>
    <filter id="active-products-filter" resultFields="sku,label,attribute1">
        <and>
            <criterion fieldName="attribute1" operator="equals" value="ACTIVE"/>
        </and>
    </filter>
</filters>

AND Filter -- Multiple Criteria

<filters>
    <filter id="expensive-electronics-filter" sortBy="-attribute2">
        <and>
            <criterion fieldName="attribute1" operator="equals" value="Electronics"/>
            <criterion fieldName="attribute2" operator="greaterThan" value="500"/>
            <criterion fieldName="attribute3" operator="notNull"/>
        </and>
    </filter>
</filters>

OR Filter -- Match Any Condition

<filters>
    <filter id="multi-region-filter">
        <or>
            <criterion fieldName="attribute1" operator="equals" value="EMEA"/>
            <criterion fieldName="attribute1" operator="equals" value="NA"/>
            <criterion fieldName="attribute1" operator="equals" value="APAC"/>
        </or>
    </filter>
</filters>

Combined AND/OR -- Nested Logic

<filters>
    <!-- Products that are Electronics AND (in EMEA or NA region) -->
    <filter id="electronics-by-region-filter" resultFields="sku,label,attribute1,attribute2">
        <and>
            <criterion fieldName="attribute1" operator="equals" value="Electronics"/>
            <or>
                <criterion fieldName="attribute2" operator="equals" value="EMEA"/>
                <criterion fieldName="attribute2" operator="equals" value="NA"/>
            </or>
        </and>
    </filter>
</filters>

NOT Filter -- Exclusion

<filters>
    <filter id="non-obsolete-filter">
        <not>
            <criterion fieldName="status" operator="inSet" value="OBSOLETE,DELETED"/>
        </not>
    </filter>
</filters>

Usage with Commands

Filters are referenced by their id in fetch, delete, and mass-edit commands.

Truncate Filter Pattern

Delete old records before a full-refresh load. Commonly used with PPV parameters and DMDS data sources.

<filter id="truncateByNameFilter" resultFields="name">
  <and>
    <criterion fieldName="name" operator="equals" value="simple:headers.entityName"/>
  </and>
</filter>

Usage in route:

<toD uri="pfx-api:delete?objectType=${headers.objectType}&amp;filter=truncateByNameFilter&amp;connection={{pfx:connection}}"/>

Truncate Flushed Records

After a DMDS flush, clean up the data feed:

<filter id="truncateFlushedFilter" resultFields="name">
  <and>
    <criterion fieldName="formulaResult" operator="equals" value="OK"/>
  </and>
</filter>

Date-Range Filters

Fixed Time Window

<filter id="dateRangeFilter">
  <and>
    <criterion fieldName="lastUpdateDate" operator="greaterOrEqual" value="simple:headers.startTimestamp"/>
    <criterion fieldName="lastUpdateDate" operator="lessOrEqual" value="simple:headers.endTimestamp"/>
  </and>
</filter>

Relative: Last N Days

Set the header dynamically in the route, then reference it:

<setHeader name="cutoffDate">
  <groovy>
    import java.time.LocalDate
    LocalDate.now().minusDays(60).toString()
  </groovy>
</setHeader>

<filter id="deleteOlderThan60Days">
  <and>
    <criterion fieldName="lastUpdateDate" operator="lessThan" value="simple:headers.cutoffDate"/>
  </and>
</filter>

Composite Filter Nesting

AND + OR Combination

<filter id="activeApprovedFilter">
  <and>
    <criterion fieldName="attribute4" operator="equals" value="Approved"/>
    <or>
      <criterion fieldName="attribute1" operator="equals" value="TypeA"/>
      <criterion fieldName="attribute1" operator="equals" value="TypeB"/>
    </or>
  </and>
</filter>

NOT (Exclusion)

<filter id="excludeDeletedFilter">
  <and>
    <not>
      <criterion fieldName="attribute5" operator="equals" value="DELETED"/>
    </not>
    <criterion fieldName="lastUpdateDate" operator="greaterOrEqual" value="simple:headers.startTimestamp"/>
  </and>
</filter>

FetchLatest Pattern

Fetch only the most recently updated record per key:

<filter id="fetchLatestByKeyFilter" resultFields="key1,attribute1,attribute2,lastUpdateDate">
  <and>
    <criterion fieldName="lastUpdateDate" operator="greaterOrEqual" value="simple:headers.lastExportTimestamp"/>
    <criterion fieldName="lastUpdateDate" operator="lessOrEqual" value="simple:headers.currentTimestamp"/>
  </and>
</filter>

Combine with sortBy=lastUpdateDate on the fetch to get records in chronological order. See Incremental Timestamp Export Pattern.

Groovy Expressions in Filter Values

For dynamic calculations that go beyond simple header/property references, use groovy: prefix:

<!-- Delete records older than 90 days (calculated at runtime) -->
<filter id="deleteOlderThan90Days">
  <and>
    <criterion fieldName="lastUpdateDate" operator="lessThan"
        value="groovy:java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC).minusDays(90)"/>
  </and>
</filter>

<!-- Match against a runtime-computed value -->
<criterion fieldName="key2" operator="equals" value="groovy:request.headers.actionName"/>

Three value prefixes are available:

Exchange Property Values

Use exchangeProperty when the value is set earlier in the route via <setProperty>:

<!-- In route: set property before filter call -->
<setProperty name="deleteBeforeDate">
  <groovy>new Date().format("yyyy-MM-dd'T'HH:mm:ss")</groovy>
</setProperty>

<!-- In filter: reference the property -->
<filter id="truncateBeforeDateFilter">
  <and>
    <criterion fieldName="lastUpdateDate" operator="lessThan"
        value="simple:exchangeProperty[deleteBeforeDate]"/>
  </and>
</filter>

inSet Operator (Batch Filtering)

Match a field against a comma-separated list of values:

<filter id="filterBySkuBatch">
  <and>
    <criterion fieldName="sku" operator="inSet" value="simple:headers.skuList"/>
  </and>
</filter>

The header value must be a comma-separated string with no spaces: SKU001,SKU002,SKU003

Set in route:

<setHeader name="skuList">
  <groovy>body.collect { it.sku }.join(',')</groovy>
</setHeader>

The inverse is notInSet — excludes matching records.

Empty Filter (Truncate All)

To delete ALL records without any criteria, use an empty <and/>:

<filter id="truncateAllFilter">
  <and/>
</filter>

This selects every record. Use with caution — typically only for full-refresh scenarios where all old data must be removed before loading new data.

Reverse Sort and Multi-Column Sort

sortBy with multiple columns

<filter id="fetchOrderedFilter" sortBy="lastUpdateDate,uniqueName,version">
  <and>
    <criterion fieldName="lastUpdateDate" operator="greaterThan" value="simple:headers.since"/>
  </and>
</filter>

Reverse sort (descending)

Prefix column name with - for descending order:

<filter id="fetchLatestFirstFilter" sortBy="-lastUpdateDate">
  <and>
    <criterion fieldName="status" operator="equals" value="ACTIVE"/>
  </and>
</filter>

OR Wrapping AND Blocks

The standard pattern for complex OR logic: each OR branch is an AND block:

<filter id="fetchPendingOrExpiredQuotes" sortBy="lastUpdateDate" resultFields="uniqueName,quoteStatus,createDate">
  <or>
    <and>
      <criterion fieldName="quoteStatus" operator="equals" value="OFFER"/>
      <criterion fieldName="createDate" operator="greaterThan" value="simple:headers.cutoffDate"/>
    </and>
    <and>
      <criterion fieldName="quoteStatus" operator="equals" value="DRAFT"/>
      <criterion fieldName="createDate" operator="greaterThan" value="simple:headers.cutoffDate"/>
      <criterion fieldName="attribute4" operator="equals" value="Not Pursuing"/>
    </and>
  </or>
</filter>

Note: In practice, OR is always used at the top level wrapping AND blocks — never as a leaf alongside individual criteria.

iStartsWith (Case-Insensitive Prefix)

Match field values that start with a prefix, ignoring case:

<filter id="filterByLabelPrefix">
  <and>
    <criterion fieldName="label" operator="iStartsWith" value="simple:headers.labelPrefix"/>
  </and>
</filter>

Also available: startsWith (case-sensitive).

resultFields Performance Optimization

By default, fetch returns ALL fields. Use resultFields to return only what you need — significantly reduces payload size and API response time:

<filter id="fetchMinimalFilter" resultFields="sku,label,attribute1,lastUpdateDate">
  <and>
    <criterion fieldName="lastUpdateDate" operator="greaterThan" value="simple:headers.since"/>
  </and>
</filter>

Guidelines:

• Always use resultFields for export filters (you know exactly which fields you need)

• Include lastUpdateDate if doing incremental sync

• Include key fields (sku, customerId, key1) for identification

• Skip for import truncate filters (only need to identify records to delete)

Common Pitfalls

• One filter per file — each file must have the <filters> wrapper element around the filter definition. Do NOT use <beans>, <beans:beans>, or XML declarations.

• Multiple top-level combinators — a <filter> may only contain ONE top-level <and>, <or>, or <not>. Wrap multiple conditions in a single combinator.

• Missing value for binary operators — all operators except isNull and notNull require a value attribute.

• Using inSet with spaces — values in inSet/notInSet are comma-separated with no spaces (e.g., value="A,B,C" not value="A, B, C").

• Filter IDs must match the filter= parameter in route URIs.

• For dynamic values use value="simple:${header.myValue}".

Common Errors

• HTTP 400 on fetch: Invalid operator name, non-existent field, or missing pricingParameterName for LTV/MLTV2

• Empty results for PX/CX: Missing fieldName="name" criterion — PX/CX requires it to identify the extension table

• Dynamic value not resolving: Missing simple: prefix — value="${header.X}" is treated as literal string

• Filter bean not found: Filter ID doesn't match file name, or file is not in filters/ directory