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"
        }
      }
    ]
  }
}
"id": {
  "*ppk_tracked*": {
    "*ppk*": "sku",
    "*tracked_field*": [
      "s_id",
      "name"
    ],
    "*post_format*": [
      {
        "key_lookup": {
          "*match*": "s_id",
          "*pluck*": "d_id",
          "*on_fail*": "abort",
          "*abort_messsage*": "Destination product id not found. Make sure that this product is created."
        }
      }
    ]
  }
}
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"
        }]
      }
    }
  ]
},
inline_if_example_abort.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": "abort",
        "abort_message": "The shipphing method is not found!"
      }
    }
  ]
}
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.*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.*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
check_array
Check the element array structure, if this does not exist, it will create a new structure and update the payload in. If the array exists, it will return the original payload.
PAYLOAD_IN.JSON
{   "shippingAddress": {
        "firstName": "Jonas",
        "lastName": "Munk",
    } 
}
​
TRANSFORMATION.JSON
{
  "*pre_format*": [
      {
          "array_check": {
              "check_array_element": "shippingAddress.0",
              "take_array_element": "shippingAddress",
              "new_array_key": "shippingAddress"
          }
      }
  ],
  "addresses": {
    "*list*": "shippingAddress",
      "*list_fields*": {
        "first_name": {
          "*ppk*": "firstName"
        }
      }
  }
}
​
PAYLOAD_OUT.JSON
{
    "addresses": [
        {
            "first_name": "Jonas"
        }
    ]
}
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. 
For example, the following array will be transformed as
PAYLOAD_IN.JSON
[
{ "barcode":"1234", "qty":1 },
{ "barcode":"1234", "qty":1 }
]
​
TRANSFORMATION.JSON
{
    "*pre_format*": [
        {
            "group_on": {
                "key": "barcode",
                "index": "products"
            }
        }
    ],
    "products": {
        "*list*": "priceList",
        "*list_fields*": {
            "barcode": {
                "*ppk*": "group.0.barcode"
            },
            "qty": {
                "*ppk*": "group.0.qty"
            }
        }
    }
}
​
PAYLOAD_OUT.JSON
 { 
 "products":  [
     { "barcode":"123", "qty":1 },
     { "barcode":"1234", "qty":5 }
 ] 
 }
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"
            }
          ]
        }
      }
    ]
  }
​
stream_setting_lookup
Allows the retrieval of a setting value. For now, there is only one option, which is to pass the output as the dictionary key. In the future, we can add more options to get the values.
stream_setting_example.json
  "ShippingCode": {
    "*ppk*": ".shippingLine.code",
     "*post_format*": [
         {
             "stream_setting_lookup": {
                 "get_key_from": "output"
             }
         },
         {
             "get_value_from_dic": {
                 "key": "code"
             }
         }
     ]
}
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
    } 
  ]
}
camel_case
This functions allows a string to be converted into a camel case string. For example it would change United Kingdom to unitedKingdom.
"country": {
    "*ppk*": "country",
    "*post_format*": [{
        "camel_case": true
    }]
}
camel_case_to_sentence
This allows for a camel case string to be converted to sentence case. For example it would change camelCaseString to Camel case string
"ShippingAddressCountry": {
            "*ppk*": "country",
            "*post_format*": [
                {
                    "camel_case_to_sentence": 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"
      }
    } 
  ]
}
col_lookup_example_abort.json
"variant_id": {
  "*ppk*": "sku"
  "*post_format*": [
    {
      "col_lookup": {
        "*match*": "name",
        "*pluck*": "d_id",
	"*data_type*": "product",
        "*key_type*": "source",
        "*on_match*": "abort",
        "*abort_message*": "Product has already been sent"
      }
    } 
  ]
}
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.
timedelta option allows you to increase or decrease the date field.
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",
			  "timedelta": 300 // OPTIONAL
		  }
    } 
  ]
}
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​
You can also convert the timezone of a datetime string by adding the input and output timezone. For example:
First Tab
Second Tab
"order_date": {
    "*ppk*": "createdAt",
    "*post_format*": [
      {
        "date_format": {
          "input": "%Y-%m-%dT%H:%M:%SZ",
          "input_timezone": "utc",
          "output": "%Y-%m-%dT%H:%M:%S",
          "output_timezone": "Europe/London"
        }
      }
    ]
  }
​
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"
}
​
​
get_increment
This function returns an incremented value.
"order_number": {
    "*static_value*": "0",
     "*post_format*": [
         {
             "get_increment": {
                 "universal_key": "random_uuid" // this could be the stream id
             }
         }
     ]
},
​
get_lead_zero
Fill the output with zeros at the left of the string.
"order_number": {
    "*ppk*": "id", // 52645
     "*post_format*": [
         {
             "get_lead_zero": 15
         }
     ]
},
​
The output will be a string like 000000000052645
get_value_from_dic
Similar to the default get_value function. This allows you to apply the same logic in the post_formatting array. For example, you can get a dict from the stream settings then get a specific value by key.
"ShippingCode": {
    "*ppk*": ".shippingLine.code",
     "*post_format*": [
         {
             "stream_setting_lookup": {
                 "get_key_from": "output"
             }
         },
         {
             "get_value_from_dic": {
                 "key": "code"
             }
         }
     ]
},
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. 
Write a custom transformation

Field Types

Static Value

Producer Payload Key

Producer Payload Key Tracked

PPK Math

Stream Setting

If Statement

Value Lookup

Counter

List & List Fields

Lists

Pre-Formatting

check_array

group_on

unset_key

Post Formatting

absolute

​

add_check_digit

add_key_to_list

stream_setting

stream_setting_lookup

calculate

camel_case

camel_case_to_sentence

cast

country_code_convert (to be released in Jan 2020)

col_lookup

current_timestamp

date_format

do_nothing

expand

fallback

format_numeric_str

get_increment

get_lead_zero

get_value_from_dic

list_fallback

max_length

lowercase
