Search…
Write a custom transformation

Field Types

Field types in the transformation files are defined in JSON key names that start and end with a * character. For example using "*static_value*" in the transformation document will indicate which type of field to use for the output data. The examples below will show each of the different field definitions and an example of how they can be used.

Static Value

Used for specifying a static value in the transformation file. Using the example below, the output data will contain a field called "status" and that field will contain the value of "Released".
static_string_example.json
"status": {
"*static_value*": "Released"
}
Static values can be any string type required, for example above, the output will be a string. The two examples below show how a integer or boolean value could be used:
static_integer_example.json
"location_id": {
"*static_value*": 1432526
}
static_boolean_example.json
"active": {
"*static_value*": true
}

Producer Payload Key

Using the *ppk* acronym in the transformation document will allow the lookup of a value based on the key provided. We defined the phrase "Producer Payload Key" as being the path to the data in the payload produced by the source system. The path is relative to the list that the transformation is currently looping, and hierarchy can be indicated by used a full stop. Two examples below show a path on the current level, and one that is nested.
basic_ppk_example.json
"date": {
"*ppk*": "created_at"
}
nested_ppk_example.json
"email": {
"*ppk*": "customer.email"
}
To help explain the hierarchy difference in the two example above, let's look at the example payload in from the source system below (dummy_payload_in.json). In the two examples above in order to map the correct data we reference two fields from the dummy payload below. The first example references a key on the top level created_at and maps it to a field called date in the output. The second example references a nested key inside of the customer block, therefore to get the nested data we have to give the full path: customer.email, to see a working example of this check out Simple Example 1.
dummy_payload_in.json
{
"channel": "website",
"created_at": "2020-01-01 13:43:23"
"customer": {
"email": "[email protected]"
"firstname": "Joe",
"lastname": "Blogs",
"marketing": {
"allow": true,
"last_updated": "2020-01-01 13:43:23"
}
}
}
In this third example shown below, we can see how how to join two PPK's together where it is necessary e.g to join first name and last name to get the full name.
ppk_join_example.json
"full_name": {
"*ppk*": [
"first_name",
"last_name",
]
}
For more example on how the mappings files work, check out Simple Example 1 and Simple Example 2.

Producer Payload Key Tracked

Using the *ppk_tracked* option in the transformation file allows the tracking of data between two systems. This normally takes the shape of tracking ID's from the source and the destination system, tying them together with a name field. The name field normally contains a value that is the same in both systems.
Inside of the *ppk_tracked* field, there is the a standard *ppk* block with an additional *tracked_fields* option. This option details how the core system should track the data. The options here are:
  • s_id - Source ID
  • s_pid - Source Parent ID
  • d_id - Destination ID
  • d_pid - Destination Parent ID
  • name - A field that can contain a reference that should be the same in both the source and destination systems (e.g order number)
ppk_tracked_example.json
"order_number": {
"*ppk_tracked*": {
"*ppk*": ".order",
"*tracked_field*": [
"s_id",
"name"
]
}
}
With the addition of the key_lookup post formatting function the core system will be able to check in our database to see if we have seen the order (in the example) before. If the system has seen it before it then aborts the transformation. For more details on key_lookup, click here.
ppk_tracked_with_deduplication_example.json
"order_number": {
"*ppk_tracked*": {
"*ppk*": ".order",
"*tracked_field*": [
"s_id",
"name"
],
"*post_format*": [
{
"key_lookup": {
"*match*": "s_id",
"*pluck*": "d_id",
"*on_match*": "abort",
"*on_fail*": "ppk"
}
}
]
}
}
Alternatively, if for example this was a product record, the key_lookup function can return the destination ID. The destination can then use a Update method rather than a create.
ppk_tracked_with_existing_id_lookup_example.json
"id": {
"*ppk_tracked*": {
"*ppk*": "sku",
"*tracked_field*": [
"s_id",
"name"
],
"*post_format*": [
{
"key_lookup": {
"*match*": "s_id",
"*pluck*": "d_id",
"*on_fail*": "empty"
}
}
]
}
}

PPK Math

The ability to add, subtract, multiply or divide two ppk values.
ppk_math_example.json
"unit_tax": {
"*ppk_math*": [
"tax_lines.0.price",
"/",
"quantity"
]
},

Stream Setting

When creating a stream in the control panel optional setting parameters can easily be added. This allows for single transformation files to be used across multiple streams when they only require small changes in certain fields. For example (shown below) where orders could have a different "warehouse_id" setting that is defined in the control panel via settings:
Could not load image
The *stream_setting* option in the mapping file example shown below will attempt a lookup based on the defined JSON value. Seen below is an JSON example of how the setting from the setting from the screenshot above could be pulled in to the transformation:
"warehouse": {
"*stream_setting*": "warehouse_id"
}
The result of using the above code/settings would be that the output data would contain then valueROTH001 with a key of warehouse.

If Statement

Used to execute a simple if statement on one or multiple input data variables. The example below shows changing the output of the line1 line based on if the input data has a company name set:
inline_if_example.json
"Haulier code":{
"*ppk*": "shipping_lines.0.title",
"*post_format*":[
{
"if":{
"value": "DPD Express Next Working Day Delivery",
"expression": "==",
"then": [{
"static_value": "DPD ND"
}],
"else": [{
"static_value": "DPD 2DAY"
}]
}
}
]
},

Value Lookup

...
vlookup_example.json
"id": {
"*vlookup*": "line_items in orders"
}

Counter

Used as an iterative counter from within the data. For example a line counter on sales order item lines. Shown below is an example of a line counter:
iterive_counter_example.json
"PositionNo": {
"*counter*": "line_item"
}

List & List Fields

See lists definition below.

Lists

Lists will occur in most occurrences of data transformation. When it comes to the transformation process a list is used as a definition when to loop through a repeating list of input data. For example when receiving a batch of 50 orders, the transformation process will need loop through each of the orders individually in order to create the output data.
Using a list definition in the transformation file is simple. The example below shows how in an input file for move order data between Shopify and FTP we define an input list in order to create an output list.
//...
"*list*": "orders",
"*list_fields*": {
"order_number": {
"*ppk*": "name"
}
//...
From the example above you can see that lists have two requirements:
  1. 1.
    *list* - The list definition first defines the path to the input data where the list is available. In the example above, where we are receiving data from Shopify, the Order data comes in the format of an array, which is keyed by the word "orders".
  2. 2.
    *list_fields* - The next definition indicates the start of the output field list. This is the point at which the input data (in example from Shopify) will start to be looped over. In the list_fields, each field will then be created in an output array with the relative values to the input data.
It should be noted that while inside a list, the input parameter key are relative to the list in which it is looping. For example in the raw input data, the path to the order name from Shopify would have been orders.0.name, but in the example above you can see that inside the list, it can simply be referenced as name.
Additional list formatting options:
*list_field* - Instead of *list_fields*, the singular *list_field* can be used to create a list of items in the output that does not have a key. for example:
//...
"tracking_numbers": {
"*list*": "line_items.line_item",
"*list_field*": {
"*ppk*": "courier_tracking_code"
}
},
//...
*list_count* - Can be used to get the total count of items in a list. Example:
//...
"total_lines": {
"*list_count*": "line_items"
},
//...

Pre-Formatting

Pre-formatting functions can be used in the transformation file to alter data before it is retrieved from the transformation process.
Pre-formatting needs to be added at the beginning of the transformation file to take the effect of the function before start processing the rest of the payload_in

group_on

The group on function can be used to group together blocks of data based on certain key within the input data. This is especially useful if data originates from flat files such as CSV. Defining a key to group the data on, such as a parent_id can result in output data that is multi-dimensional and more fitting to the destination system requirements.

unset_key

We can use this function to find an element inside an array, compare it with a value and remove the full item where the field was found. In the example below, we want to remove the items that contains an email with the value [email protected]
​
{
"*pre_format*":
[
{
"unset_key": {
"key": "product.line_items",
"field": "email",
"value": "[email protected]"
}
}
]
}

Post Formatting

Post formatter functions can be used in the transformation file to alter data after it has been retrieved from the input data.
Post formatting functions can be added on to fields as shown in the following example code:
"option_values": {
"*ppk*": "product.options.0.values",
"*post_format*": [
{
"squash": ","
}
]
}
In the example above we can see that the output value (the key on the left side) of option_values has been mapped to the input variable (the key on the right side) of product.options.0.values. In the case above, we are expecting the input variable to be in the format of an array, therefore we apply the post format function of squash, then pass in the option ",".
The net result of this post formatting will be that the input array will be squashed down to a single string, using a "," to separate the different options (if found).
Post formatting options can be daisy chained together, the functions will be executed in the order in which they are entered in to the transformation document. (example below in the add_key_to_list definition).
When daisy chaining functions together, if the functions are incompatible with each other, it may result in a transformation level error.

absolute

Ability to convert a negative int or float value in to a positive.
absolute_example.json
"discount": {
"*ppk*": "total_discount",
"*post_format*": [
{
"absolute": true
}
]
}

​

add_check_digit

This function converts an EAN 13 digit code from 12 digits code. For example, you can convert this code 111456749112 into 1114567491122
add_check_digit.json
"sku": {
"*ppk*": "product.sku",
"*post_format*": [
{
"add_check_digit": true
}
]
}

add_key_to_list

The add key to list function will normally be used in conjunction with the expand function above. This function will loop a list and prefix each element with a defined key. The example below builds on the expand example below, where by it adds in a key of src to each of the image elements in the array.
add_key_to_list_example.json
"images": {
"*ppk*": "group.0.images",
"*post_format*": [
{
"expand": ","
},
{
"add_key_to_list": "src"
}
]
}

stream_setting

Allows the retrieval of a setting for from stream config file. This includes source and destination settings. This option is typically only used in conjunction with the if statement.
stream_setting_example.json
"language_iso3": {
"*ppk*": "note_attributes.0.value",
"*post_format*": [
{
"if": {
"expression": "in",
"value": [
"de",
"fr"
],
"then": [
{
"ppk": "note_attributes.0.value"
}
],
"else": [
{
"stream_setting": "language"
}
]
}
}
]
}

calculate

This function allows for the mathematical calculation of a given string, normally used in conjunction with ppk_replace when a calculated figure is required from the input data.
calculate_example.json
"tax": {
"*static_value*": "total - shipping - subtotal"
"*post_format*": [
{
"ppk_replace": {
"total": "grand_total",
"shipping": "shipping_amount",
"subtotal": "subtotal"
},
"calculate": true
}
]
}

cast

This function allows for field values to be cast to a specific string type. The example below will cast the quantity output to an integer.
cast_example.json
"quantity": {
"*ppk*": "qty_ordered"
"*post_format*": [
{
"cast": "int"
}
]
}

country_code_convert (to be released in Jan 2020)

Different systems can often hand country codes in different formats. This function allows the simple translation of one format to another. Shown in the example below is a conversion from a source system giving the country code in ISO2 format and a destination system requirement of ISO3 formatting:
contry_code_convert_example.json
"country": {
"*ppk*": "billing_address.country"
"*post_format*": [
{
"country_code_convert": "ISO2 to ISO3"
}
]
}
Our system allows for the following conversion options:
  • ISO2 (ISO 3166-1 alpha-2)
  • ISO3 (ISO 3166-1 alpha-3)
  • ISO# (ISO 3166-1 numeric)
  • NAME (Official state name)
Example converting a country name to a ISO2 code
name_to_iso2_example.json
"country": {
"*ppk*": "billing_address.country"
"*post_format*": [
{
"country_code_convert": "NAME to ISO2"
}
]
}
Example converting a ISO2 code to a ISO3 code
iso2_to_iso3_example.json
"country": {
"*ppk*": "billing_address.country"
"*post_format*": [
{
"country_code_convert": "ISO2 to ISO3"
}
]
}
More information on country code formatting can be found at: https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes

col_lookup

Ability to pluck data from the data storage table.
col_lookup_example.json
"variant_id": {
"*ppk*": "sku"
"*post_format*": [
{
"col_lookup": {
"*match*": "name",
"*pluck*": "d_id",
"*data_type*": "product",
"*key_type*": "source",
"*on_fail*": "empty"
}
}
]
}

current_timestamp

Gets the current Unix timestamp. This can then be formatted in by date_format.
current_timestamp.json
"current_timestamp": {
"*static_value*": ""
"*post_format*": [
{
"current_timestamp": true
}
]
}
additional example with formatting:
current_timestamp_formatted.json
"current_date": {
"*ppk*": "na",
"*post_format*": [
{
"current_timestamp": true,
"date_format": {
"input": "%s",
"output": "%Y%m%d"
}
}
]
}

date_format

The date post formatter function can be used to transform date variables between different systems where the destination date time format may be different from the source. You must specify the input and the output formats.
date_example.json
"order_date": {
"*ppk*": "created_at",
"*post_format*": [
{
"date_format": {
"input": "%Y-%m-%dT%H:%M:%S%z",
"output": "%Y%m%d%H%M%S%w"
}
}
]
}
For guidance on the formatting string options please refer to the Python documentation for datetime at: https://docs.python.org/2/library/datetime.html#strftime-and-strptime-behavior​

do_nothing

Commonly only used in conjunction with the if function. This will no just return the value as is.
do_nothing_example.json
TBC

expand

Expand is the opposite function to the squash function shown above. It will allow a given string to be expanded in to an array based on a separator. The example below shows expanding an input string based on a comma to create an array.
expand_example.json
"images": {
"*ppk*": "group.0.images",
"*post_format*": [
{
"expand": ","
}
]
}
The code above is expecting to receive a comma separated list of image URLs (from the input key of group.0.images), which it will then split based on a comma.

fallback

The ability to fallback to another value if the value of the ppk is empty.
fallback_example.json
"first_name": {
"*ppk*": "firstname",
"*post_format*": [
{
"fallback": "addresses.billing_address.firstname"
}
]
}

format_numeric_str

This function formats a string or numeric value into a formatted string value.
To find more information about the format options see https://docs.python.org/3/library/string.html#format-examples​
"price": {
"*ppk*": "price",
"*post_format*": [
{
"format_numeric_str": "This is the new price {0:.2f}"
}
]
}
The result will be
Input:
{
"price": 12.0222222
}
Output:
{
"price": "This is the new price 12.02"
}

list_fallback

This function is used to fallback to a value relative to the list that is currently being iterated over.
list_fallback_example.json
"price": {
"*ppk*": "parent_item.price",
"*post_format*": [
{
"list_fallback": "price"
}
]
}

max_length

This function allows you to define the maximum length of a given field in the transformation document. This is helpful if the destination system has a limit on the number of characters allowed in a field. The example below shows how a destination system that has a hard limit of 36 characters on the address_line_one field can be accommodated.
uppercase_example.json
"address_line_one": {
"*ppk*": "billing_address.address1",
"*post_format*": [
{
"max_length": 36
}
]
}
Consideration should be given when limiting the length of fields in the transformation document. Building on the example above, if your source system has allowed user input of more than 36 characters in the address line one field, the input will simply be truncated when reaching the destination system. This could cause issues if data is truncated.

lowercase

This function allows will change all of the characters in the given field to be lowercase.
lowercase_example.json
"first_name": {
"*ppk*": "billing_address.firstname",
"*post_format*": [
{
"lowercase": true
}
]
}

ltrim

Trim the given character from the left side of a value.
suffix_example.json
"description": {
"*ppk*": "desc",
"*post_format*": [
{
"ltrim": " "
}
]
}

negative

Ability to convert a int or float value in to a negative.
absolute_example.json
"discount": {
"*ppk*": "total_discount",
"*post_format*": [
{
"negative": true
}

pad

This function allows create padding to the right or left. Where "side", "length" and "char" definable. "side" - is the field which defines padding side, "length" - is the field which defines padding length and "char" - is the field which defines padding symbol.
pad_example.json
"test_order_number_padded": {
"*ppk*": "name",
"*post_format*": [
{
"pad": {
"side": "right",
"length": 40,
"char": "0"
}
}
]
},

phone_numbers_only

The same as numbers_only, but allows for the + symbol in addition to the numbers 0 to 9.
phone_numbers_only.json
"phone": {
"*ppk*": "telephone",
"*post_format*": [
{
"phone_numbers_only": true
}
]
}

ppk_replace

This function allows replacing of words in a string (this could be a static_value or a ppk value) with value from another ppk.
ppk_replace_example.json
"tax": {
"*static_value*": "total - shipping - subtotal"
"*post_format*": [
{
"ppk_replace": {
"total": "grand_total",
"shipping": "shipping_amount",
"subtotal": "subtotal"
}
}
]
}

math

This function allows using simple math functionality inline in mapping file done. Where operator" and "value" definable. "operator" - is the mathematical operator, "value" - is the value for mathematical operations.
math_example.json
"first_name": {
"*ppk*": "billing.price",
"*post_format*": [
{
"math": {
"operator": "*"
"value": 1.25
}
}
]
}
In the example above, output_value will be multiplied on .

numbers_only

Ability to strip out all other characters from a string that are not 1 2 3 4 5 6 7 8 or 9.
numbers_only_example.json
"phone": {
"*ppk*": "telephone",
"*post_format*": [
{
"numbers_only": true
}
]
}

if

The "If" function can cause 3 type of result. Where "expression", "output" and "then" - definable. "expression" - is the comparison operator, "output" - comparison value and "then" - result action.
The first is running of the indicated functions
if_value_equals_string_format_example.json
"first_name": {
"*ppk*": "billing_address.first_name",
"*post_format*": [
{
"if": {
"expression": "==",
"value": "Andy",
"then": [
{
"uppercase": true
}
]
}
}
]
}
In the example above, if current value is equal to "output" then all functions from "then" block will be run.
The second result enable you to delete a block with the given type of if. For example:
if_value_equals_string_remove_block_example.json
"first_name": {
"*ppk*": "billing_address.first_name",
"*post_format*": [
{
"if": {
"expression": "==",
"value": "Andy",
"then": "drop_block"
}
}
]
}
In the example above, if current value is equal to "output" then "first_name" field will be deleted form result object.
The third result enables to to halt or abort an event on certain parameters being.
if_value_equals_string_abort_example.json
"first_name": {
"*ppk*": "billing_address.first_name",
"*post_format*": [
{
"if": {
"expression": "==",
"value": "Andy",
"then": "abort"
}
}
]
}
In the example above, if current value is equal to "output" then the event will be aborted in the transformation process based on the output_value.
Another example of how if can be used is with an else condition as shown below:
if_else_example.json
"published_at": {
"*ppk*": "status",
"*post_format*": [
{
"if": {
"expression": "==",
"value": "1",
"then": [
{
"ppk": "created_at"
}
],
"else": [
{
"static_value": ""
}
]
}
}
]
}

static_fallback

Ability to fallback to a static value if the ppk is empty, the value of static_value will be used in the output data instead of blank.
static_fallback_example.json
"ship_by": {
"*ppk*": "shipping_title",
"*post_format*": [
{
"static_fallback": "Standard Shipping"
}
]
}

split_and_take

This function allow a string to be broken and partially returned.
In the example below, it is splitting the street value from the source system (Magento in this case) and splitting on a new line. In the example, address1 takes the first element, and address2 takes the second element. *Note: If the input string doesn't contain the on value, it will only return the input value in part 0.
split_and_take_example.json
"address1": {
"*ppk*": "street",
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 0
}
}
]
},
"address2": {
"*ppk*": "street",
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 1
}
}
]
}

table_lookup

This function allows to lookup a value based on an input. Possibly a JSON representation of a table etc. Where "match" - is the comparison operator, "table" - is the simple key x value table, "fallback" - is the optional field, which will be assigned to "output_value" if "match" statement is false.
This will give the event transformer a basic ability of a lookup table, stored in the JSON, see the example below:
table_lookup_example.json
"currency_test": {
"*ppk*": "currency",
"*post_format*": [
{
"table_lookup": {
"*match*": "==",
"*table*": {
"GBP": "GB Pounds",
"USD": "US Dollar",
"EUR": "Euro",
"JPY": "Japanese Yen"
}
}
}
]
}
In the example above, the output_value is mapped to currency, which in the case of our example will give GBP. The table_lookup is a simple key x value table which should use the output value to == exact match in this case to convert the output_value to β€˜GB Pounds’.
table_lookup_with_fallback_example.json
"currency_test": {
"*ppk*": "currency",
"*post_format*": [
{
"table_lookup": {
"*match*": "==",
"*fallback*": "GB Pounds",
"*table*": {
"USD": "US Dollar",
"EUR": "Euro",
"JPY": "Japanese Yen"
}
}
}
]
}
To use the dynamic table lookup as defined in the control panel area: Lookup Tables you can pass in the UUID of the lookup table (created, viewed and editable in the control panel) in the transformation file, like in the example shown below:
table_lookup_with_fallback_example.json
"currency_test": {
"*ppk*": "currency",
"*post_format*": [
{
"table_lookup": {
"*match*": "==",
"*fallback*": "GB Pounds",
"*table*": "f670f2c0-4521-11eb-4c83-r98fbd0c4463"
}
}
]
}

key_lookup

This is a powerful function that allows the lookup of data stored in the HighCohesion entity table.
This function allows to give the user the ability to switch the mapping file all together, e.g. Stop and Start again! with a new transformation ID.
key_lookup_example.json
"id": {
"*ppk*": "ref",
"*post_format*": [
{
"key_lookup": {
"*match*": "s_id",
"*pluck*": "d_id",
"*on_fail*": "empty",
"*on_match*": "transformation_switch",
"*transformation_id*": "ef755f7a-bdcc-11e9-8692-366c3ae2703e"
}
}
]
}
​

list_find

This function allows to lookup a value by the key in nested JSON data, e.g. if the output_value is a JSON string, decode it and lookup the desired value. Where "match", "field" and "return" - definable. "match" - is the name of key, "field"- is the value of the key and "return" - is the name of getting value.
list_find_example.json
"order_lang_from_tags_test": {
"*ppk*": "order_options_tags",
"*post_format*": [
{
"list_find": {
"match": "name",
"field": "lang_tag",
"return": "value"
}
}
]
}

json

This function allows to lookup a value by the key in nested JSON data, e.g. if the output_value is a JSON string, decode it and lookup the desired value.
json_example.json
"order_lang_from_json_test": {
"*ppk*": "order_options_json",
"*post_format*": [
{
"json": {
"key": "value"
}
}
]
}

serial

This function allows ability to lookup a value in serialized data, e.g. if the output_value is serialized data, function deserialize it and lookup the desired value.
serial_example.json
"order_lang_from_serial_test": {
"*ppk*": "order_options_serial",
"*post_format*": [
{
"serial": {
"key": "value"
}
}
]
}

prefix

This function allows for simple prefixing to to done with any field in the transformation process. The example shown below will add a prefix of UKWEB to the start of the order_number field.
prefix_example.json
"order_number": {
"*ppk*": "name",