Search…
Example 1 - Sales Order
Building an example sales order transformation file from scratch.
We have created a simple example transformation file below. This file shows the basic concepts of a transformation. The data has two states, payload in (the data that we receive from a source system) and payload out (the converted output data we send to a destination system). We are able to convert the data between the two formats based on a single transformation file. All three are shown below:
Payload In
Transformation
Payload Out
{
"order": "#GB123042",
"sub_total": 48.5,
"shipping": 4.95,
"grand_total": 53.45,
"shipping_type": "Next Day",
"created_at": "2020-01-01 13:43:23",
"customer_email": "[email protected]",
"customer_name": "Joe Bloggs"
}
{
"order_number": {
"*ppk*": "order"
},
"order_email": {
"*ppk*": "customer_email"
},
"full_name": {
"*ppk*": "customer_name"
}
}
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"full_name": "Joe Bloggs"
}
β€ŒSo now we know how the most basic of transformations can be built in the HighCohesion format. In the next section we build out more examples to help build your understanding.
Throughout the following examples the payload_in.json will remain the same, however the payload_out.json will change depending on the changes to the transformation file.β€Œ

Scenario

β€ŒIn the example above, we had just mapped three basic fields. For the following sections, please imagine that aim is to get the data in to a format so that a warehouse system can read the a CSV file (generated after payload out is sent to the destination) and contain enough information to ship the customers order.β€Œ

Start with the basics

β€ŒLets start with just the basic order information, pretty simple we're just going to map three fields to start with:
Payload In
Transformation
Payload Out
{
"order": "#GB123042",
"sub_total": 48.5,
"shipping": 4.95,
"grand_total": 53.45,
"shipping_type": "Next Day",
"created_at": "2020-01-01 13:43:23",
"customer": {
"email": "[email protected]",
"firstname": "Joe",
"lastname": "Bloggs",
"marketing": {
"allow": true,
"last_updated": "2020-01-01 13:43:23"
}
},
"address": {
"street": "43 Test Street,\nTest town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439"
}
"order_items": [
{
"sku": "ABF3439",
"price_each": 9.5,
"quantity": 2
},
{
"sku": "GHNT0001",
"price_each": 29.5,
"quantity": 1
}
]
}
{
"order_number": {
"*ppk*": "order"
},
"order_email": {
"*ppk*": "customer_email"
},
"ship_service": {
"*ppk*": "shipping_type"
}
}
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day""
}

β€ŒAdding in the address

Next we will add in the address fields:
Payload In
Transformation
Payload Out
{
"order": "#GB123042",
"sub_total": 48.5,
"shipping": 4.95,
"grand_total": 53.45,
"shipping_type": "Next Day",
"created_at": "2020-01-01 13:43:23",
"customer": {
"email": "[email protected]",
"firstname": "Joe",
"lastname": "Bloggs",
"marketing": {
"allow": true,
"last_updated": "2020-01-01 13:43:23"
}
},
"address": {
"street": "43 Test Street,\nTest town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439"
}
"order_items": [
{
"sku": "ABF3439",
"price_each": 9.5,
"quantity": 2
},
{
"sku": "GHNT0001",
"price_each": 29.5,
"quantity": 1
}
]
}
{
"order_number": {
"*ppk*": "order"
},
"order_email": {
"*ppk*": "customer_email"
},
"ship_service": {
"*ppk*": "shipping_type"
},
"address_line_1": {
"*ppk*": "address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 0
}
}
]
},
"address_line_2": {
"*ppk*": "address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 1
}
}
]
},
"city": {
"*ppk*": "address.city"
},
"post_code": {
"*ppk*": "address.post_code"
},
"country": {
"*ppk*": "address.country"
},
"phone": {
"*ppk*": "address.phone"
}
}
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day",
"address_line_1": "43 Test Street,",
"address_line_2": "Test town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439"
}
Note that we've added in a new concept here of post formatting functions. These are instructions to the core system to apply a function to the field before returning it to the payload_out.json.
In the example above, our payload_in.json contained the users street fields combined in one field. Therefore we added in a *post_format* block to instruct the core system to format the output value, in this case we used the split_and_take function.β€Œ

Add in the customer

β€ŒNext we will add in some of the basic customer information:
Payload In
Transformation
Payload Out
{
"order": "#GB123042",
"sub_total": 48.5,
"shipping": 4.95,
"grand_total": 53.45,
"shipping_type": "Next Day",
"created_at": "2020-01-01 13:43:23",
"customer": {
"email": "[email protected]",
"firstname": "Joe",
"lastname": "Bloggs",
"marketing": {
"allow": true,
"last_updated": "2020-01-01 13:43:23"
}
},
"address": {
"street"
"43 Test Street,\nTest town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439"
}
"order_items": [
{
"sku": "ABF3439",
"price_each": 9.5,
"quantity": 2
},
{
"sku": "GHNT0001",
"price_each": 29.5,
"quantity": 1
}
]
}
{
"order_number": {
"*ppk*": "order"
},
"order_email": {
"*ppk*": "customer_email"
},
"ship_service": {
"*ppk*": "shipping_type"
},
"address_line_1": {
"*ppk*": "address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 0
}
}
]
},
"address_line_2": {
"*ppk*": "address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 1
}
}
]
},
"city": {
"*ppk*": "address.city"
},
"post_code": {
"*ppk*": "address.post_code"
},
"country": {
"*ppk*": "address.country"
},
"phone": {
"*ppk*": "address.phone"
},
"customer_name": {
"*ppk*": [
"customer.firstname",
"customer.lastname"
]
},
"allows_marketing": {
"*ppk*": "customer.allows_marketing"
}
}
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day",
"address_line_1": "43 Test Street,",
"address_line_2": "Test town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439",
"customer_name": "Joe Bloggs",
"allows_marketing": true
}
Note that we've added in a new concept here using multiple values in the *ppk* field. This can be seen on the customer_name field in the transformation, which is a join for firstname and lastname from the payload in. The core system grab each of these separate values and join the together with a space.β€Œ

Add in the items

β€ŒGetting slightly harder now. We need to add in the items, however in our payload_in.json there are multiple items. In order to get all of the data, we will use the List functionality:
Payload In
Transformation
Payload Out
{
"order": "#GB123042",
"sub_total": 48.5,
"shipping": 4.95,
"grand_total": 53.45,
"shipping_type": "Next Day",
"created_at": "2020-01-01 13:43:23",
"customer": {
"email": "[email protected]",
"firstname": "Joe",
"lastname": "Bloggs",
"marketing": {
"allow": true,
"last_updated": "2020-01-01 13:43:23"
}
},
"address": {
"street"
"43 Test Street,\nTest town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439"
}
"order_items": [
{
"sku": "ABF3439",
"price_each": 9.5,
"quantity": 2
},
{
"sku": "GHNT0001",
"price_each": 29.5,
"quantity": 1
}
]
}
{
"*list*": "order_items",
"*list_fields*": {
"order_number": {
"*ppk*": ".order"
},
"order_email": {
"*ppk*": ".customer_email"
},
"ship_service": {
"*ppk*": ".shipping_type"
},
"address_line_1": {
"*ppk*": ".address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 0
}
}
]
},
"address_line_2": {
"*ppk*": ".address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 1
}
}
]
},
"city": {
"*ppk*": ".address.city"
},
"post_code": {
"*ppk*": ".address.post_code"
},
"country": {
"*ppk*": ".address.country"
},
"phone": {
"*ppk*": ".address.phone"
},
"customer_name": {
"*ppk*": [
".customer.firstname",
".customer.lastname"
]
},
"allows_marketing": {
"*ppk*": ".customer.allows_marketing"
}
}
}
{
[
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day",
"address_line_1": "43 Test Street,",
"address_line_2": "Test town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439",
"customer_name": "Joe Bloggs",
"allows_marketing": true,
"sku": "ABF3439",
"price": 9.5,
"quantity": 2
},
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day",
"address_line_1": "43 Test Street,",
"address_line_2": "Test town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439",
"customer_name": "Joe Bloggs",
"allows_marketing": true,
"sku": "GHNT0001",
"price": 29.5,
"quantity": 1
}
]
}
Here we introduced the concept of lists. Lists occur in payload where the are multiples of the same object block. In our example of an order, the order has two different line items in it. The *list* and *list_fields* keys appear at the start of the list.
Please also note the . that has appeared at the start of the existing *ppk* values. This denotes that the key path is absolute. Remember that when you are inside a list the keys will be relative to the data list that we're looping through. To access data outside of the list item we use the absolute path.
Further detail on lists can be found here.β€Œ

Add in tracking

β€ŒIn this part we will add in some tracking fields so that we can use HighCohesions inbuilt de-duplication options to help protect against any duplicate orders being shipped.
Payload In
Transformation
Payload Out
{
"order": "#GB123042",
"sub_total": 48.5,
"shipping": 4.95,
"grand_total": 53.45,
"shipping_type": "Next Day",
"created_at": "2020-01-01 13:43:23",
"customer": {
"email": "[email protected]",
"firstname": "Joe",
"lastname": "Bloggs",
"marketing": {
"allow": true,
"last_updated": "2020-01-01 13:43:23"
}
},
"address": {
"street"
"43 Test Street,\nTest town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439"
}
"order_items": [
{
"sku": "ABF3439",
"price_each": 9.5,
"quantity": 2
},
{
"sku": "GHNT0001",
"price_each": 29.5,
"quantity": 1
}
]
}
{
"--source_id": {
"*ppk_tracked*": {
"*ppk*": "order",
"*tracked_field*": [
"s_id",
"name"
]
}
},
"*list*": "order_items",
"*list_fields*": {
"order_number": {
"*ppk*": ".order"
},
"order_email": {
"*ppk*": ".customer_email"
},
"ship_service": {
"*ppk*": ".shipping_type"
},
"address_line_1": {
"*ppk*": ".address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 0
}
}
]
},
"address_line_2": {
"*ppk*": ".address.street"
"*post_format*": [
{
"split_and_take": {
"on": "\n",
"take": 1
}
}
]
},
"city": {
"*ppk*": ".address.city"
},
"post_code": {
"*ppk*": ".address.post_code"
},
"country": {
"*ppk*": ".address.country"
},
"phone": {
"*ppk*": ".address.phone"
},
"customer_name": {
"*ppk*": [
".customer.firstname",
".customer.lastname"
]
},
"allows_marketing": {
"*ppk*": ".customer.allows_marketing"
}
}
}
[
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day",
"address_line_1": "43 Test Street,",
"address_line_2": "Test town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439",
"customer_name": "Joe Bloggs",
"allows_marketing": true,
"sku": "ABF3439",
"price": 9.5,
"quantity": 2
},
{
"order_number": "#GB123042",
"order_email": "[email protected]",
"ship_service": "Next Day",
"address_line_1": "43 Test Street,",
"address_line_2": "Test town",
"city": "Testercity",
"post_code": "TE5 7ER",
"country": "United Kingdom",
"phone": "+442043939439",
"customer_name": "Joe Bloggs",
"allows_marketing": true,
"sku": "GHNT0001",
"price": 29.5,
"quantity": 1
}
]
Here we can see that we have modified the order_number field as this contains a unique reference for the order. The modifications include two parts:
  1. 1.
    Wrapping in *ppk_tracked*, this denotes to the core system that this is a tracked field. 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:
    1. 1.
      s_id - Source ID
    2. 2.
      s_pid - Source Parent ID
    3. 3.
      d_id - Destination ID
    4. 4.
      d_pid - Destination Parent ID
    5. 5.
      name - A field that can contain a reference that should be the same in both the source and destination systems (e.g order number)
    ​
  2. 2.
    Addition of the key_lookup post formatting function. This function essentially checks in the 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.
  3. 3.
    By using a double negative at the start of the tracking field --source_id, indicates that it is not to be included in the payload_out.json.

The end result

The above payload out with CSV settings in the destination function would result in the following CSV file being created:
"order_number","order_email","ship_service","address_line_1","address_line_2","city","post_code","country","phone","customer_name","allows_marketing","sku","price","quantity"
"#GB123042","[email protected]","Next Day","43 Test Street,","Test town","Testercity","TE5 7ER","United Kingdom","+442043939439","Joe Bloggs","True","ABF3439","9.5","2"
"#GB123042","[email protected]","Next Day","43 Test Street,","Test town","Testercity","TE5 7ER","United Kingdom","+442043939439","Joe Bloggs","True","GHNT0001","29.5","1"
​