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".

"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:

"location_id": {
	"*static_value*": 1432526
}
"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. 

"date": {
  "*ppk*": "created_at"
}
"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": "joe.blogs@highcohesion.com"
    "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. 

"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:

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:

"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

...

"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:

"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"
        }
    ]
}

If you want to check a multi level array, then you can check the array key and move the result to the root level. If the array is multilevel, you need to move the result to the root level, the function won't update multilevel arrays

PAYLOAD_IN.JSON
{   "shippingAddress": {
        "node": {
            "addresses" : {
                "phone": "12345678780"
            }
        }
    } 
}

TRANSFORMATION.JSON
{
  "*pre_format*": [
      {
          "array_check": {
              "check_array_element": "shippingAddress.node.addresses.0",
              "take_array_element": "shippingAddress.node.addresses",
              "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. You can use different expressions, for example, ==, <>, >=, <=, <, >, contains, does not contain, starts with, ends with. In the example below, we want to remove the items that contains an email with the value test@test.com.

{
    "*pre_format*": 
        [        
            {
                "unset_key": { 
                    "key": "product.line_items",
                    "field": "email",
                    "expression": "==",
                    "value": "test@test.com"
                    }
            }    
        ]
}

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. 

"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

"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.

"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. 

  "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.

  "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.

"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. 

"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:

"country": {
  "*ppk*": "billing_address.country"
  "*post_format*": [
    {
      "iso2_to_iso3": true
    } 
  ]
}

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

"country": {
  "*ppk*": "billing_address.country"
  "*post_format*": [
    {
      "name_to_iso2": true
    } 
  ]
}

Example converting a ISO2 code to a ISO3 code

"country": {
  "*ppk*": "billing_address.country"
  "*post_format*": [
    {
      "iso2_to_iso3": true
    } 
  ]
}

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. 

"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": {
  "*static_value*": ""
  "*post_format*": [
    {
      "current_timestamp": true
    } 
  ]
}

additional example with formatting:

"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.

"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:

"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.

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.

"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.

"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. 

"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. 

"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. 

"first_name": {
  "*ppk*": "billing_address.firstname",
  "*post_format*": [
    {
      "lowercase": true
    }
  ]
}

length_value

Returns the length value of a given field.

"description_length": {
  "*ppk*": "description",
  "*post_format*": [
    {
      "length_value": true
    }
  ]
}

// input
{
  "description": "test"
}

// output
{
  "description_length": 4
}

ltrim

Trim the given character from the left side of a value.

"description": {
  "*ppk*": "desc",
  "*post_format*": [
    {
      "ltrim": " "
    }
  ]
}

negative

Ability to convert a int or float value in to a negative. 

"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. 

"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.

Current list of expression operations:

  • in

  • contains

  • does not contain

  • starts with

  • ends with

  • >

  • <

  • =>

  • <=

  • <>

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",
        "abort_message": "this name is not valid"
      }
    }
  ]
}

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.

"first_name": {
  "*ppk*": "billing_address.first_name",
  "*post_format*": [
    {
      "if": {
        "expression": "==",
        "value": "Andy",
        "then": "error"
        "error_message": "This name is not valid"
      }
    }
  ]
}

In the example above, we are going to fail the event when the logic is true

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": ""
          }
        ]
      }
    }
  ]
}

remove_first_characters

Removes the first X characters from a string. The below would return String

{
  "test": {
    "*static_value*": "Test String"
    "*post_format*": [{
      "remove_first_characters": 5
    }]
}

remove_last_characters

Removes the last X characters from a string. The below would return Test

{
  "test": {
    "*static_value*": "Test String"
    "*post_format*": [{
      "remove_last_characters": 6
    }]
}

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.

"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
      }
    }
  ]
}

sum_list_key

This function allows to calculate the sum of the items in an array.

"total_price": {
  "*static_value*": 0,
          "*post_format*": [
            {
                "sum_list_key": {
                    "list": "LineItem",
                    "key": "OrderLine.PurchasePrice"
                }
            }
        ]
}

sum_list_key1_by_key2

This function allows to calculate the sum of the items in an array multiplying them by a second item in the array.

"total_price": {
  "*static_value*": 0,
        "*post_format*": [
            {
                "sum_list_key1_by_key2": {
                    "list": "LineItem",
                    "key_1": "OrderLine.PurchasePrice",
                    "key_2": "OrderLine.OrderQty"
                }
            }
        ]
}

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"
      }
    }
  ]
}

title_case

This will make each new word in a sentence be upper case. For example "A great sentence for example" would be converted to "A Great Sentence For Example"

"ShippingAddressCountry": {
            "*ppk*": "shippingAddress.country",
            "*post_format*": [
                {           
                    "title_case": true
                }
            ]
        },

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. 

"order_number": {
  "*ppk*": "name",
  "*post_format*": [
    {
      "prefix": "UKWEB"
    }
  ]
}

random_value

This function returns a random value. There are three options: uuid, integer or string.

"order_number": {
  "*static_value*": "",
  "*post_format*": [
    {
      "random_value": {
        "type": "uuid"
      }
    }
  ]
}

// Result
{
  "order_number": "49c7d4fb-7000-4a8d-bfe4-10b83362e895"
}

"order_number": {
  "*static_value*": "",
  "*post_format*": [
    {
      "random_value": {
        "type": "string",
        "length": 10
      }
    }
  ]
}

// Result
{
  "order_number": "dekalstlsa"
}
"order_number": {
  "*static_value*": "",
  "*post_format*": [
    {
      "random_value": {
        "type": "integer",
        "length": 20
      }
    }
  ]
}

// Result
{
  "order_number": 2134567845623579764
}

replace

This function allows for text based find and replace to to done with any field in the transformation process. The example shown below will replace any occurrence of the string #UK with the new string of ONLINE. 

"order_number": {
  "*ppk*": "name",
  "*post_format*": [
    {
      "replace": {
        "find": "#UK",
        "replace": "ONLINE"
      }
    }
  ]
}

regex

This function allows for regex-based validation to be applied to any field during the transformation process. The example shown ensures that any value for commodityCode must match the pattern of one or more digits [0-9]+

Note: Avoid using \ in your regex patterns to prevent issues with escaping.

"commodityCode": {
        "*ppk*": "commodity_code",
        "*post_format*": [
            {
                "regex": {
                    "pattern": "[0-9]+"
                }
            }
        ]
    }

rtrim

Trim the given character from the right side of a value.

"description": {
  "*ppk*": "desc",
  "*post_format*": [
    {
      "rtrim": " "
    }
  ]
}

squash

Squash is used to merge an array of data down in to a single string. It uses the Python function of .join() to join the elements of the array and is similar to the implode() function in PHP. The example shown below gives an example of how an input array (being product.options.0.values) is converted to a comma separated list of the values.

"option_values": {
  "*ppk*": "product.options.0.values",
  "*post_format*": [
    {
      "squash": ","
    }
  ]
}

The value shown in speech marks after the squash key is the join character. For example if this was changed to a "|" pipe, the product options would be merged together with the pipe symbol instead of a comma.

suffix

This function allows for simple suffixing to to done with any field in the transformation process. The example shown below will add a prefix of PAYPAL to the end of the order_number field. 

"order_number": {
  "*ppk*": "name",
  "*post_format*": [
    {
      "suffix": "PAYPAL"
    }
  ]
}

trim

Trim the given character from the start or end of a value.

"description": {
  "*ppk*": "desc",
  "*post_format*": [
    {
      "trim": " "
    }
  ]
}

uppercase

This function allows will change all of the characters in the given field to be uppercase. 

"first_name": {
  "*ppk*": "billing_address.firstname",
  "*post_format*": [
    {
      "uppercase": true
    }
  ]
}

List Post Formatting

List post formatting allows list modifications to be made after the data has been formatted. This is common when building flat data for CSV or XML files where source data has been multi-dimensional.

flat_array

Transforms a multidimensional array into a single dimensional array

For example, if your list result is like
{
    "list": [[1,2,3], [4,5,6]]
}

After applying this post format list function, the result will look
{
    "list": [1,2,3,4,5,6]
}

This is useful when you need to extract sub items from an array.

For example:

{
 "Lines": {
        "*list*": "Order.TransformLines",
        "*list_fields*": {
            "*list*": "BOMs",
            "*list_fields*": {
                "productcode": {
                    "*ppk*": "SKU"
                }
            }
        },
        "*list_post_format*": [
            {
                "flat_array": true
            }
        ]
    }
}

merge_down

Message Data

message_data

You can grab information from the event message and replace it in the given field

"stream_name": {
  "*message_data*": "stream.spec.title"
}
PreviousCustomising a Public TransformationNextTransformation Library

Last updated