Advanced Field Functions
Advanced Field Functions are especially suitable for custom formatting, calculations and queries of individual column contents in imports and exports. Please pay attention to the correct use of the pipe |, the single quotes' and the curved {} and square [] brackets.
Please, use these functions only as specified in the examples herein. We do not provide support for multiple or incorrectly nested (faulty) functions that are not as shown in the following examples.
Contents
- 1 spec* - Usage of additional Data Fields
- 2 Format, calculate and filter cell content
- 2.1 replace - Replacing Cell Content
- 2.2 find - Find Values / Content
- 2.3 truncate - Truncating Cell Content
- 2.4 lowercase - Cell Content (all letters lowercase)
- 2.5 uppercase - Cell Content (all letters uppercase)
- 2.6 capitalize - Cell Content (only first letters uppercase)
- 2.7 capitalize-f - Cell Content (Only the first letter of the first word uppercase)
- 2.8 len - Number of Characters
- 2.9 net - Net Price
- 2.10 html - Text with HTML
- 2.11 text - Text w/o HTML but, with Linebreaks
- 2.12 clear - Text w/o HTML and w/o Linebreaks
- 2.13 split - Extracting Data from a Data Field with Separators
- 2.14 math - Calculations
- 2.15 date() - Date Values
- 2.16 SEO - Optimizing character strings for search engines
- 2.17 match - Finding text segments in character strings
- 3 Nesting of Functions
- 4 Conditional Statements and Branches (Queries)
- 5 Interfaces specific Placeholders
- 6 Sources of Errors in Functions
spec* - Usage of additional Data Fields
Up to 20 additional, individual fields, e.g. for columns of the import file that do not match any of the existing field labels, can be defined under My Account ->Base Settings ->Actions. The assignment of these free fields is done in the Import and Export settings via the placeholder [spec1], [spec2], and so on ... up to [spec20].
- spec1, spec2, spec3, spec4 ... - Assigning free fields
Example: You have given your first free field the name 'Mobile Link' since your import file contains such a column with corresponding values. All values of this column can be read or assigned via the following placeholder: [spec1]
Format, calculate and filter cell content
replace - Replacing Cell Content
The replace function substitutes one value through another. As a separator, any character can be specified. However, this can not be the Pipe (|) or the value that is to be replaced. Within the 'replace' function no quotes are used.
Example 1... The comma in the price cell shall be replaced by a dot. Separator is the colon (:).
[price|replace:,:.]
Example 2... Double colons in the attribute cell shall be replaced by a single colon. Separator is the hash mark (#). In this case do not use the colon as separator, because the values to be replaced are colons!
[attributes|replace#::#:]
Example 3 ... In the values of the "title" column, the words "pendant" shall be changed into "jewelry pendant" and "stud" into "ear stud". Separator here is the double dot (:).
[Title|replace:'pendant':'jewelry pendant'{replace:'plug':'ear plug'}]
find - Find Values / Content
Example... The content of cell "NAME" is searched for the value "bl" and, if it is present there (return values: 1 = found, 0 = not found), the value of cell "TITLE" changed from "One Product" to "[ID]-black". The cell "ID" with the value "52368" serves here only as an example. Do not use the pipe (|) sign and not the value to be replaced as separator for the function (find:bl). Otherwise, any character can be defined as the separator:
{IF '[NAME|find:bl]' == '1'}[ID]-black{ENDIF}
truncate - Truncating Cell Content
Note: If the cut happens to be in a word, this word will not be passed.
Example ... From a cell 'ImageName1' in your import file only the first 4 characters ahall be outputed or imported:
[pic1|truncate:4]
Example ... From your SKU number you want to import 6 characters starting at position 3:
[itemno|truncate:6:3]
lowercase - Cell Content (all letters lowercase)
Example ... ImageName1 "GTN765.jpg" shall be converted to lowercase (gtn 756.jpg):
[pic1|lowercase]
uppercase - Cell Content (all letters uppercase)
Example... ImageName1 "gtne75.jpg" shall be converted to uppercase "GTNE75.JPG":
[pic1|uppercase]
capitalize - Cell Content (only first letters uppercase)
Example... The first letter(s) in the cell color: "black, red, blue" shall be converted to uppercase (Black, Red, Blue):
[color|capitalize]
capitalize-f - Cell Content (Only the first letter of the first word uppercase)
Example... Only the first letter of the cell title: "my great product" shall be converted to uppercase (My great product):
[color|capitalize-f]
len - Number of Characters
Example 1 ... You need to know the number of characters in the cell title:
[title|len]
Example 2 ... The number of characters in the field title is queried and combined with an IF condition. If there are less than 50 characters in the "title" cell, then write "Begin --> " in front of this title:
{IF '[title|len]' < '50'}Begin -->{ENDIF}[title]
net - Net Price
Example ... Your import file contains gross prices. However, you need the net price, without the contained VAT:
[price|net]
html - Text with HTML
Example ... All text descriptions shall be imported "with" HTML tags (if any):
[description|html]
text - Text w/o HTML but, with Linebreaks
Existing HTML tags (if any), from the product descriptions of your import file, are removed and only the actual text is imported. HTML line breaks in the text are re-formatted as text line breaks:
[description|text]
clear - Text w/o HTML and w/o Linebreaks
Example ... All descriptions are outputed as unformatted, continuous text without line breaks:
[description|clear]
split - Extracting Data from a Data Field with Separators
Example 1 ... cell content is as follows: "ImagePath1|ImagePath2|ImagePath3". As default data separator in the system is the pipe character ("|") preset. Only the value of 'Image Path 2' shall be taken:
[FeldNameBild|split:2]
Example 2 ... cell content: "ImagePath1->ImagePath2->ImagePath3". Data separator is a hyphen in conjunction with the right chevron ("->") (as an example of a different Separator). From the cell content with 3 values only the value of "ImagePath3" shall be taken:
[FeldNameBild|split:3:->]
Example 3 ... cell contents of a field 'Category': "Furniture|Living Room|Chair". Data separator is the pipe again. If you use a individual separator, please see example 2 for guidance. The acquisition of the values "Furniture", "Living Room" and "Chair" in 3 separate cells is prepared as follows:
[Category1|split:1] [Category2|split:2] [Category3|split:3]
math - Calculations
Please note: Inside calculations with the math function do IF/ELSE commands not work!
Example 1 ... Add 100 to the current price:
[math|price+100]
Example 2 ... Multiply the current price by 1.5; use a dot as the decimal separator:
[math|price*1.5|.]
Example 3 ... Add the values of the cells 'price' and 'shipping cost':
[math|price+shippingCost]
Example 4 ... Add/compute 4 values in one field: Import Price [price_list] + VAT [vat] + 20% surcharge + 5 (EUR) flat
[math|price_list*1.vat*1.2+5]
Example 5 ... value in the cell 'Import Price' [price_list] is e.g. 'VK25.25'. This however can not be used for calculations, so 'VK' must be deleted to change the value into a number. 'VK' is therefore replaced by "nothing", hence deleted. A simple replace within math does not work though, so a special one must be used !:
- Schema: [math|column(+ calculation(optional))|decimal separator|search value(| substitution value (optional))]
[math|price_list|.|VK]
Example 6 ... the value in the cell 'Import Price' [price_list] is e.g. '30.50 USD '. The numeric $ value is to be converted, the decimal separator is to be replaced by the comma and 'USD' by 'EUR':
- Schema: [math|column+calculation|decimal separator|search value(|substitute value optional)]
[math|price_list*0.9|,|USD|EUR]
date() - Date Values
Example 1 ... Start date (for example, for auctions) specifying hour and minute:
date(d.m.Y H:i)
Beispiel 2... Startdatum mit zusätzlicher Angabe der Sekunden:
date(d.m.Y H:i:s)
Example 3 ... start date with hour and minute but set 2 days back:
date(d.m.Y H:i|-2)
SEO - Optimizing character strings for search engines
Example ... Output of the title text will be optimized for search engines (SEO):
[title|seo]
match - Finding text segments in character strings
Example ... Your description contains lots of text. However, you only need a certain part of it. Enter a specific start and end text as a search pattern, e.g. from your product description. Everything between TEXTVON and TEXTBIS is then selected and returned as a value. TEXTVON and TEXTBIS must of course be replaced by your own values:
[description|match:'TEXTVON':'TEXTBIS']
remove found text segment including TEXTVON (Text from) and TEXTBIS (Text to): [Description | match: 'TEXTVON': 'TEXTBIS', 1]
Nesting of Functions
[func1 {func2}] - Joining two Functions
Example 1 ... Replaces in a stringhttps with http and a-domain.com with b-domain.com:
[pic1|replace:'https':'http'{replace:'a-domain.com':'b-domain.com'}]
Example 2 ... you want change date values; for example, "09.10.2015 17:55:49" in "09.10.2016". This requires that the first part of the date is selected and in there '2015' is replaced by '2016'. The separator is a blank:
[Datum|split:1: {replace:'2015':'2016'}]
[func1 {func2}{func3}] - Joining three Functions
Example ... In the title "Chain, black, red Metallik, 80cm" "Metallik" is converted to "metallic". This requires that the third part of the title is found, all the letters are written in lower case and a letter is replaced. As separator of the values within the title the blank is defined (before the first curly brace):
[title|split:3: {lowercase}{replace:'k':'c'}]
Conditional Statements and Branches (Queries)
IF - Statement
Example ... If the value in the column 'Inventory' is greater than 5, write 5 - otherwise do not write anything in this cell:
{IF 'Inventory' > '5'}5{ENDIF}
IF-ELSE - Statement
Example 1 ... If column 'Available' value is not 'yes', write 0 - otherwise write 1
{IF 'Available' != 'ja'}0{ELSE}1{ENDIF}
Example 2 ... If 'Stock' value is empty, write 'no' - otherwise write 'yes'.
{IF 'Stock' == }no{ELSE}ja{ENDIF}
Beispiel 3... Wenn Zelle 'A' nicht leer, schreibe den Wert aus Zelle 'A' - ansonsten schreibe 'No Name' in diese Zelle.
{IF 'A' != }[A]{ELSE}No Name{ENDIF}
IF-ELSEIF-ELSE - Statement
Example 1 ... If value of column 'Available' = no, write 0 - if value of column 'Available' = yes, write 1 - if both are FALSE, write nothing in the cell. {IF 'Available' == 'no'}0{ELSEIF 'Available' == 'yes'}1{ELSE}{ENDIF} Example 2 ... is a bit more complex. There are, for example, variant cells for clothing sizes. The following code replaces the size designations 36-54 from the import file (column name: 'Variation_Size') with the desired letter named sizes, for example, S, M, L, XL, etc.:
{IF 'Variation_Size' == '36'}XXS {ELSEIF 'Variation_Size' == '38'}XS {ELSEIF 'Variation_Size' == '40'}S {ELSEIF 'Variation_Size' == '42'}M {ELSEIF 'Variation_Size' == '44'}L {ELSEIF 'Variation_Size' == '46'}XL {ELSEIF 'Variation_Size' == '48'}XXL {ELSEIF 'Variation_Size' == '50'}3XL {ELSEIF 'Variation_Size' == '52'}4XL {ELSEIF 'Variation_Size' == '54'}5XL {ELSE}6XL {ENDIF}
IF-ELSE-ELSE - Statement
Example ... If column 'Active' value = 'yes', accept value from column 'Inventory' as is - otherwise if value 'inventory' is greater 10, write '10' - otherwise write '0' in the cell. {IF 'Active' == 'Ja'}[Inventory]{ELSE}{IF 'Inventory' > '10'}10{ELSE}0{ENDIF}{ENDIF}
Interfaces specific Placeholders
WooCommerce
ASIN-placeholder
[ASIN-{COUNTRY}]
Sources of Errors in Functions
If functions with numbers are used inside an IF-branch, then the initial VALUE must be either an integer or have the dot as the decimal separator. Example: {IF 'VALUE' < '5'} and so on... 'VALUE' can be i.E. 10, 20.50 or 23.32 sein. Values with comma as the decimal separator will not work in IF-branchings.
More functions will be added as required and on request.