Rules engine – Advanced mappings

This page describes advanced mapping scenarios where the source and target field types are different or where the mapping depends on multiple fields.

Map a Text column to a Number column

To be filterable and sortable, numbers must be stored as numbers. If they are stored as text, sorting and range filters will not behave reliably.
When numbers are stored as text, formats can vary (decimal separators, thousands separators, units, placeholders), making conversion ambiguous and error‑prone.

Examples:

1,000.90 (US) is equivalent to 1 000,90 (many European formats).
1,000 (US) is not equivalent to 1,000 (Europe, where , can be a decimal separator).
Some legacy systems use placeholders like NA or - to mean “no value”.

Data sources that store numbers as text are typically legacy systems that limit data quality and can lead to business errors.

Template approach (parse common number formats)

Important

Parsing text into numbers is inherently ambiguous and error‑prone. If possible, fix the source data type. If not, keep inputs as consistent as possible (same locale/format) and add validation checks.

If you cannot change your data source and the values are “numbers as text”, you can use a template (Twig) to normalize common formats before writing to a NUMBER field.

A basic example to handle spaces between numbers would be

Replace PRICE_TEXT with your source field key.

twig

{% set v = "1 1 1" | default('') | trim %}
{% set v = v | replace({' ':''}) %}
{{v}}

Below, a more complete example that handles:

thousands separators (spaces, apostrophes, ,, .)
decimal separators (, or .)
placeholders like NA, N/A, - (outputs empty)

Replace PRICE_TEXT with your source field key.

twig

{# Read and normalize common number-as-text formats #}
{% set raw = source('PRICE_TEXT') | default('') | trim %}
{% set v = raw | lower %}

{# Common "no value" placeholders #}
{% if v in ['', 'na', 'n/a', '-', '—'] %}
  {{ '' }}
{% else %}
  {# Remove spaces (including non-breaking), apostrophes, and some currency symbols #}
  {% set v = v | replace({' ':'', ' ':'', "'":'', '€':'', '$':'', '£':''}) %}

  {% set hasComma = (v | split(',') | length) > 1 %}
  {% set hasDot = (v | split('.') | length) > 1 %}

  {% if hasComma and hasDot %}
    {% set afterComma = v | split(',') | last %}
    {% set afterDot = v | split('.') | last %}

    {# Heuristic: 1–2 digits → decimal separator #}
    {% if afterDot|length in [1,2] %}
      {% set v = v | replace({',':''}) %}
    {% elseif afterComma|length in [1,2] %}
      {% set v = (v | replace({'.':''})) | replace({',':'.'}) %}
    {% else %}
      {% set v = (v | replace({',':''})) | replace({'.':''}) %}
    {% endif %}

  {% elseif hasComma %}
    {% set parts = v | split(',') %}
    {% if parts|length == 2 and (parts[1]|length in [1,2]) %}
      {% set v = v | replace({',':'.'}) %}
    {% else %}
      {% set v = v | replace({',':''}) %}
    {% endif %}

  {% elseif hasDot %}
    {% set parts = v | split('.') %}
    {% if parts|length != 2 or (parts[1]|length not in [1,2]) %}
      {# Most likely a thousands separator #}
      {% set v = v | replace({'.':''}) %}
    {% endif %}
  {% endif %}

  {{ v }}
{% endif %}

Map a Text or Number source column to a Select or Classification column

Text or Number values cannot always be predicted or normalized in a reliable way.

For example, the color "green" might appear as:

Green
green
GREEN
104 (a color code)

Approach

you can use conditions to create one rule per text input value.

Steps:

In Action, select Use a default value and set the numeric value to apply.
In Only if, set the conditions describing when this numeric value must be used (for example when source text equals "Green").
Before saving, click on Add new rule.
Give a meaningful rule name, for example: Green for "Green".
Click on Save and add a new rule and repeat the process for each predictable text input.

You will end up with multiple rules, each one handling a specific input value.

Best practice

If you have only a few predictable text inputs, this approach can work.
If the values are not predictable, there is no safe solution other than transforming your data source to use a Number field.

Map a Classification column depending on multiple source columns

In this example:

The retailer target table contains:
- EAN – the product EAN 13
- Classification – the website classification, with possible values:
  - Vacuum cleaner wireless
  - Vacuum cleaner with wire
The supplier source table contains:
- EAN 13 – the product EAN 13
- Typology – product typology, with values:
  - Vacuum cleaner
  - Coffee machine
- Type – specific to Vacuum cleaner, with values:
  - Wireless
  - With wire

The business rule is:

If Typology is Vacuum cleaner and Type is Wireless, then Classification must be Vacuum cleaner wireless on the retailer side.
Else, if Typology is Vacuum cleaner and Type is With wire, then Classification must be Vacuum cleaner with wire on the retailer side.

Implementation with multiple rules

You will create multiple rules on the same target field (Classification), one per case.

First rule – Vacuum cleaner wireless:
- In Action, select Use a default value and choose the classification Vacuum cleaner wireless.
- In Only if, add:
  - A condition on Typology = Vacuum cleaner
  - A condition on Type = Wireless
- Click on + Add new rule and give a name such as Classification – vacuum cleaner wireless.
Second rule – Vacuum cleaner with wire:
- In Action, select Use a default value and choose the classification Vacuum cleaner with wire.
- In Only if, add:
  - A condition on Typology = Vacuum cleaner
  - A condition on Type = With wire
Order the rules as needed (usually the more specific rules first).

You now have multiple rules on the same target field, each one expressing a specific business case.

Cascading select (limit options based on another field)

Sometimes you want to limit the selectable options of a list field based on another field (supplier, typology, etc.). This is typically done with a Formula using the SET_SELECTABLE_OPTIONS action.

Example – Limit options by supplier code

Goal: on field DEEE_SCALE, allow only some options depending on the SUPPLIER_CODE.

xml

<Formulas>
    <Field key="DEEE_SCALE">
        <Rule priority="1">
            <Conditions>
                <Condition-Group>
                    <Condition source="SUPPLIER_CODE" operator="IN">
                        <Value>0646</Value>
                        <Value>0647</Value>
                    </Condition>
                </Condition-Group>
            </Conditions>
            <Action type="SET_SELECTABLE_OPTIONS">
                <Value>5240654</Value>
                <Value>5240655</Value>
                <Value>5240656</Value>
            </Action>
        </Rule>
        <Rule priority="2">
            <Conditions>
                <Condition-Group>
                    <Condition source="SUPPLIER_CODE" operator="IN">
                        <Value>0648</Value>
                    </Condition>
                </Condition-Group>
            </Conditions>
            <Action type="SET_SELECTABLE_OPTIONS">
                <Value>5240656</Value>
            </Action>
        </Rule>
    </Field>
</Formulas>

The first rule restricts options when SUPPLIER_CODE is 0646 or 0647.
The second rule handles the specific case 0648.
At edition time, the grid only offers the allowed options; invalid existing values are still visible but marked as inconsistent and can be surfaced via conditional formatting.

Composite unique identifier (formula on an identifier)

You may need to enforce uniqueness on a combination of fields (for example supplier reference + supplier). This can be modelled with an identifier that is computed by a Formula, and then marked as unique in the table schema.

Example – Unique pair "SUPPLIER_REF / SUPPLIER"

xml

<Table key="PRODUCTS">
    <Schema>
        <Identifiers>
            <Identifier key="EAN" index="1" level="PRODUCT">
                <Title>EAN</Title>
            </Identifier>
            <Identifier key="SUPPLIER_REF-SUPPLIER_CODE" index="2" level="PRODUCT">
                <Title>Supplier ref / supplier</Title>
            </Identifier>
        </Identifiers>
        <Formulas>
            <Identifier key="SUPPLIER_REF-SUPPLIER_CODE">
                <!-- Remove the composed key if one of the components is empty -->
                <Rule priority="1">
                    <Conditions>
                        <Condition-Group>
                            <Condition source="SUPPLIER_REF" operator="EMPTY"/>
                        </Condition-Group>
                        <Condition-Group>
                            <Condition source="SUPPLIER" operator="EMPTY"/>
                        </Condition-Group>
                    </Conditions>
                    <Action type="REMOVE_VALUE"/>
                </Rule>
                <!-- Build the composed key when both fields are filled -->
                <Rule priority="2">
                    <Conditions>
                        <Condition-Group>
                            <Condition source="SUPPLIER_REF" operator="NOT_EMPTY"/>
                            <Condition source="SUPPLIER" operator="NOT_EMPTY"/>
                        </Condition-Group>
                    </Conditions>
                    <Action type="SET_TEXT">
                        <Template trim-spaces="true"><![CDATA[{{source("SUPPLIER_REF")}}-{{source("SUPPLIER","key")}}]]></Template>
                    </Action>
                </Rule>
            </Identifier>
        </Formulas>
    </Schema>
</Table>

The first rule clears the composed identifier if one of the components is empty (so that uniqueness is not enforced in that case).
The second rule concatenates the values when both are present.
Uniqueness is then enforced at identifier level: two items cannot share the same composed value.

Rules engine – Advanced mappings ​

Map a Text column to a Number column ​

Recommended solution ​

Template approach (parse common number formats) ​

Map a Text or Number source column to a Select or Classification column ​

Approach ​

Map a Classification column depending on multiple source columns ​

Implementation with multiple rules ​

Cascading select (limit options based on another field) ​

Example – Limit options by supplier code ​

Composite unique identifier (formula on an identifier) ​

Example – Unique pair "SUPPLIER_REF / SUPPLIER" ​