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.
{
"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.
"full_name": {
"*ppk*": [
"first_name",
"last_name",
]
}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)
"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.
"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.
"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.
"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"
}]
}
}
]
},"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:
*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".*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.
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 asPAYLOAD_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 [email protected].
{
"*pre_format*":
[
{
"unset_key": {
"key": "product.line_items",
"field": "email",
"expression": "==",
"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).
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
}
]
}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"
}
}
]
}"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
}
}
]
}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.
TBCexpand
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 000000000052645get_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
}
]
}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.
"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": {
"*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.
"first_name": {
"*ppk*": "billing.price",
"*post_format*": [
{
"math": {
"operator": "*"
"value": 1.25
}
}
]
}In the example above, output_value will be multiplied on .
math_round
This function allows to round a value to X decimal values.
"price": {
"*ppk*": "billing.price",
"*post_format*": [
{
"math_round": 2
}
]
}{
"price": 10.33
}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.
"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
"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:
"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.
"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:
"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.
"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:
"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’.
"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:
"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.
"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.
"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.
"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.
"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"
}Last updated
Was this helpful?