Formula > Data Factory Edition
WARNING
-The formulas are calculated after each update of a table on all of its items.
Introduction
This document describes the creation and edition of formulas using the Data Factory task table-import-schema. A complete description of the table-import-schema task can be found here.
Key elements
Only the following can be the target of formulas
- identify them
- type fields
- SINGLE-LINE-TEXT
- LONG TEXT
- HTML-TEXT
- SINGLE-SELECT
Formulas can only use elements belonging to the same level as the one associated with the target.
Limits related to formulas are available here, error codes related to exceeding these limits are available [here](# logs-related-to-the-creation-or-update-of-formulas)
The possibilities here are restricted with respect to the rule engine tool, only the elements below can be used within the conditions:
- The
SINGLE-LINE-TEXT,LONG-TEXT,NUMBERandSINGLE-SELECTtype fields - Identifiers
- Classifications
- The
Within the templates (
SET_TEXTaction), thesourceexpression must be surrounded by {{ and }} to be interpretedHere the term
sourcepoints to the current table.As in the rule engine tool, within a template:
- The first parameter is the key of the field, the identifier or the classification. Example:
source("SUPPLIER_REF")- If the key contains a quote
", it must be doubled, example:source("la""composition""")
- If the key contains a quote
- The second parameter is to be used if the source attribute is a list of options. Example:
source("color", "key")- Possible values are
title,title-localandkey
- Possible values are
- The third parameter is to be used if the source attribute is a
SINGLE-SELECTtype field and the second attribute istitle-local- The possible values correspond to the list of standard languages in the application
- The first parameter is the key of the field, the identifier or the classification. Example:
Any incorrect (or incomplete) formula will not be imported, and a log will be added to the generated error report (see [below](#logs-related-to-the-creation-or-la-update- day-of-formulas))
Updating the structure of the table leads to the recalculation of all the formulas for this table, in all the contexts (shared or not).
XML representation
The XML representation of a formula is as below:
| XPath | Description | Occurrence |
|---|---|---|
| Formulas | Group all the formulas on a table | 0..1 |
| Formulas/[Identifier | Field] | Characterizes a formula to be applied to an identifier or a field | 0..* |
| Formulas/[Identifier | Field]@key | The key of the target - The property on which the formula is applied | 1 |
| Formulas/[Identifier | Field]/Rule | The rule associated with the formula on the target identifier. | 1..* |
| Formulas/[Identifier | Field]/Rule@priority | The priority order of execution of the rule associated with the formula. If absent, then only the first rule is imported. | 1 |
| ./Rule/Conditions | The conditions for which the formula applies. Conditions can only be performed on properties belonging to the current table. More details here | 0..1 |
| ./Rule/Action | Allows you to describe the action to be performed within the target rule. More details here | 0..* |
Lifecycle of a formula
Considering the example below:
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
We name the block below, formula, or formula on an identifier:
xml
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
We name the block below, rule, or rule within a formula:
xml
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
General remarks
- If a
ruleis proposed on aformula, the information concerning it is considered to be exhaustive- If an element of a
ruleis not presented (a condition, a group of conditions...), it is deleted.
- If an element of a
- If one of the
rulesof aformulais not present during the import, it is not updated, nor archived, nor deleted. - If one of the
rulesof theformulacannot be imported (for technical or logical reasons), it is not imported and therulealready present is kept - To archive a
formulaon an identifier, proceed as presented below
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="SUPPLIER_REF-SUPPLIER" status="ARCHIVED" />
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
2
3
4
5
6
7
The example above archives a formula on an identifier without modifying its content.
- To archive a
rulewithin a formula, proceed as presented below.
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1" status="ARCHIVED"/>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
The example above archives the priority 1 rule and leaves unchanged other rules present on this formula.
- The examples below are associated with an import of the table in incremental mode. In exhaustive mode, any element that is not communicated is archived.
Examples
Not mentioning a formula does not impact it
Starting from the state below:
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
The import of the XML below:
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}_____{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Produces the following result:
- Only the
formulaon the identifierREF-FIELD_2is updated
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}_____{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Not mentioning a rule does not impact it
Starting from the state below
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
The import of the XML below:
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}__________{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Produces the following result:
- Only the
ruleof priority 2 associated with theformulaon the identifierREF-FIELD_1is updated - The
ruleof priority 1 associated with theformulaon the identifierREF-FIELD_1remains unchanged (in this example, we consider the case of importing the table in incremental mode)
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}__________{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
If a rule is invalid, it is not imported
Starting from the state below
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
The import of the XML below:
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="2">
<Conditions-Invalid>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions-Invalid>
<Action-Invalid type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}_{{source("FIELD_1")}}]]></Template>
</Action-Invalid>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Produces the following result:
- The proposed rule is not valid, so no attempt is made to update the priority 2 rule
- Priority 1 rule remains unchanged
xml
<Table key="products">
<Schema>
<Formulas>
<Identifier key="REF-FIELD_1">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}]]></Template>
</Action>
</Rule>
<Rule priority="2">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_1" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_1")}}]]></Template>
</Action>
</Rule>
</Identifier>
<Identifier key="REF-FIELD_2">
<Rule priority="1">
<Conditions>
<Condition-Group>
<Condition source="REF" operator="NOT_EMPTY" />
<Condition source="FIELD_2" operator="NOT_EMPTY" />
</Condition-Group>
</Conditions>
<Action type="SET_TEXT">
<Template trim-spaces="true"><![CDATA[{{source("REF")}}-{{source("FIELD_2","key")}}]]></Template>
</Action>
</Rule>
</Identifier>
</Formulas>
</Schema>
</Table>1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42