Using expressions in your file import setup allows you to set up different rules like comparing or switching values in different fields.
Operators in On-premises import expressions:
| Description | Operator |
| Equals | = |
| Less than | < |
| More than | > |
| Less than or equal to | <= |
| Greater than or equal to | >= |
| Not equal to | <> |
| Not | !=(Alternative not) |
Rules for syntax
- All field references must be surrounded with <>. F1 will be ignored.
- The syntax is not space-sensitive but the best practice is to use no spaces between logical operators, e.g. <F5> instead of < F5 >.
- Values should not be separated by "". The separator is a comma, operator, or field reference <>.
Expressions using IF:
The syntax for an IF expression:
IF(<Fn> op comparevalue, TRUE value, FALSE value)
<Fn> refers to a field. op means one of the operators in the table above. comparevalue is the value to be compared to the value of the field <Fn>. This can be a string or a number. The expression is evaluated to be true or false. TRUE value is the value to return if the expression is true. FALSE value is the value to return if the expression is false. Both TRUE value and FALSE value can be a literal value (e.g. USA), a field name (e.g. <F10> or an expression name (e.g. <E2>).
Example 1:
If weight is less than 1 kg, then choose a weight of 1 kg. (If weight is less than 1 kg, choose 1 kg, or else use the imported weight value).
IF(<Fnweightfield><1,1,<Fnweightfield>)
In an import setup where the weight field is F10, the expression would be:
IF(<F10><1,1,<F10>)
Example 2:
The value of the Contents field depends on goodstype, and contents should always be specified with the same value when a specific goods type is imported.
IF(<Fngoodtype>,goodstypecode,goodstype description)
In a real-life import, this expression could look like this:
IF(<F15>,PLL,Pallet with bricks)
Expressions using SWITCH
The syntax for a SWITCH expression:
SWITCH(<Fn>,”key1|value1|key2|value2”)
<Fn> is the field whose value will be compared. The second parameter is a quoted string consisting of pairs, each pair consisting of one key and one value. Pipes (|) are used as separators between keys and values. The value of <Fn> is compared to each key from left to right until there is a match, and then the value for the found key is returned. If no key matches, the result will be the value of <Fn> if the default value has been configured with the <Fn> field else it will be blank. The SWITCH statement can change some values and leave other values unchanged.
Example:
SWITCH(<F4>,"UK|United Kingdom|US|United States|AU|Australia")
Nested expressions
Nested expressions can be used but be aware of the complexity if you have many nested expressions.
Example:
If field 17 is less than 20, use value DHL, otherwise run the expression in <E5>
IF(<F17><20,PDK,<E5>)