08. Mapping Data

Up until now, every time we've wanted to prepare a payload we've done it manually by creating tags (e.g. {somefield}) in an XML or JSON document and then translating that out using either Custom Properties or the Formatter Node.

In most real world scenarios, we want to go beyond simple text substitution and be able to map hierarchical data easily from one schema (shape) to another. We also want to be able to apply functions to individual fields. For example, we might want to trim a field to a specific length so it can be accepted by the target system.

We'll achieve this using the QuickMap Node, our visual data mapper. QuickMap is provided a source and target schema and then allows mappings to be expressed as Flow Connectors. Where needed, inline functions can be used at the field level to convert data too.

Exercise 10. Mapping Data

In this exercise, we'll look at how to map a document containing orders we have received as JSON into a FreshBooks invoice. While we won't actually run the FreshBooks Node, we'll use its Node Samples to see how it requires an invoice document to be structured.

Add QuickMap to a new Workflow, connect Start.RunNow → QuickMap.
Add FreshBooks.

Click the Change/View link on QuickMap.Mapping. In the screen that displays, you'll see QuickMap requires an example of the source and the target document and indicates the different ways that a schema (shape of the data) can be acquired.

Generally, QuickMap will infer a schema for the source data by reading a recent Workflow Log of the preceding step. The schema for the result will be inferred by reading data loaded from a sample of the subsequent step.

For our purposes, we'll hardcode an example JSON document for the source and use a Node sample for the result.

Set QuickMap.Source to the order JSON below:

 {
 	"Orders": {
 		"Order": [
 		{
 			"CustomerID": "ATA005",
 			"EmployeeID": "6",
 			"OrderDate": "2019-05-06T00:00:00",
 			"RequiredDate": "2019-05-20T00:00:00",
 			"Lines": [
 			{
 				"InventoryCode": "INV001",
 				"Quantity": "12",
 				"TotalCostPrice": "100"
 			},
 			{
 				"InventoryCode": "INV002",
 				"Quantity": "6",
 				"TotalCostPrice": "40"
 			},
 			{
 				"InventoryCode": "INV001",
 				"Quantity": "2",
 				"TotalCostPrice": "120"
 			}
 			]
 		},
 		{
 			"CustomerID": "ZXS002",
 			"EmployeeID": "6",
 			"OrderDate": "2019-05-06T00:00:00",
 			"RequiredDate": "2019-05-20T00:00:00",
 			"Lines": [
 			{
 				"InventoryCode": "INV003",
 				"Quantity": "12",
 				"TotalCostPrice": "65"
 			},
 			{
 				"InventoryCode": "INV004",
 				"Quantity": "6",
 				"TotalCostPrice": "13"
 			},
 			{
 				"InventoryCode": "INV002",
 				"Quantity": "2",
 				"TotalCostPrice": "13"
 			}
 			]
 		},
 		{
 			"CustomerID": "CASH",
 			"EmployeeID": "6",
 			"OrderDate": "2019-05-06T00:00:00",
 			"RequiredDate": "2019-05-20T00:00:00",
 			"Lines": [
 			{
 				"InventoryCode": "INV002",
 				"Quantity": "12",
 				"TotalCostPrice": "210"
 			},
 			{
 				"InventoryCode": "INV002",
 				"Quantity": "6",
 				"TotalCostPrice": "30"
 			},
 			{
 				"InventoryCode": "INV006",
 				"Quantity": "2",
 				"TotalCostPrice": "15"
 			}
 			]
 		}
 		]
 	}
 	}

Click the Change/View link again on QuickMap.Mapping. Under Data for Source Property, you should now see The Source Property is set up correctly. We'll now configure the Result Property.

Add FreshBooks and choose the Create Invoice sample (click v → Choose Sample).

Open the FreshBooks.RequestXml Property to see the sample invoice document that has been populated from the sample.
Connect QuickMap.Result → FreshBooks.RequestXml.

Click the Change/View link once more on QuickMap.Mapping. This time, you should see both a source and a target data tree. We are now ready to begin mapping fields.

In QuickMap, connect Order → invoice, CustomerID → client_id and OrderDate → date.

As soon as you've created the mapping, a preview of the resulting document will be shown on the right hand side. At this point, the document should look like this:

 <request>
 	<invoice>
 		<client_id>ATA005</client_id>
 		<date>2019-05-06T00:00:00</date>
 	</invoice>
 	<invoice>
 		<client_id>ZXS002</client_id>
 		<date>2019-05-06T00:00:00</date>
 	</invoice>
 	<invoice>
 		<client_id>CASH</client_id>
 		<date>2019-05-06T00:00:00</date>
 	</invoice>
 </request>

Focus the Map Expression textbox to the right of the date field and type LEFT({OrderDate}, 10).

The LEFT function behaves like the one in Excel - it trims a string to a certain number of characters measured from the left. In this case, we're using it to exclude the time component from the OrderDate field.

Like elsewhere in the platform, field references are enclosed in braces and as you start typing either a function or the name of a connected field, a suggestion list will be displayed for autocomplete. You can either click a suggestion or press TAB to have it applied.

Read the QuickMap Functions article for a complete list of available functions.

We'll now map the invoice detail line fields. You can use the Search textbox to quickly highlight matches and click the funnel icon to toggle between search and filter modes. Filter mode is useful where the tree is large and you don't want to scroll down to find the desired field.

Just like in the Workflow Design, you can connect fields either by dragging from a source Flow Socket to a target Flow Socket or alternatively, click the source Flow socket, then click the target Flow Socket. This is an easier way to connect Flow Sockets that are not close together.
Connect Line → line, InventoryCode → name and Quantity → quantity.

Notice that the same inventory item appears on multiple lines in some of the orders. We will use aggregation to collapse these to individual lines.

To do this, we specify which field or fields to group by and then specify an aggregator function for the remaining fields. In this case, we're going to group by name (the inventory item code) and sum the quantity field.
Click the key icon to the right of the name field.
Set the Map Expression textbox to the right of the quantity field to SUM({Quantity}).

SUM is a function similar to the SUM function from Excel - it returns the sum of a range of numbers, in this case the set of quantities that were collapsed in to a single container due to grouping on the name field.

We'll now look at how we can augment the result data using a separate source. Let's say we wanted to address the order to a specific contact at the company. The order data we have does not contain contact information so we'd need to separately query the list of contact information and then feed that in to QuickMap.

For this exercise, we'll just hardcode an example of that contact information directly into a QuickMap Custom Property. In a real world scenario, we would have queried it as an earlier step in the Workflow.

Click ✓ Confirm to close QuickMap, then add QuickMap.Contacts and set the data type to Xml. Paste this value into the new Custom Property:

<Customers>
	<Customer>
		<CustomerID>ATA005</CustomerID>
		<ContactName>Howard Snyder</ContactName>
	</Customer>
	<Customer>
		<CustomerID>ZXS002</CustomerID>
		<ContactName>Yoshi Latimer</ContactName>
	</Customer>
</Customers>

Go back into the map by clicking Change/View on the Mapping Property.
Connect CustomerID → notes.
Set the Map Expression on notes to LOOKUP({CustomerID}, "Contacts", "CustomerID", "ContactName", "")

LOOKUP works a little like an Excel VLOOKUP - it searches in a Custom Property named Contacts for a field named CustomerID that matches the value of {CustomerID} and when it finds it finds a match, returns the value of the field ContactName. The last parameter tells the LOOKUP function what to return when there is no match - in our case, we just return an empty value.

Take a minute to walk through this step by step, then write the expression by hand rather than copy and pasting it.
To finish off the mapping, prefix it with the text Deliver To: using the CONCAT function - CONCAT("Deliver to: ", LOOKUP(...)).

For our final step, we'll look at how to filter data based on a condition. In our example payload, one of the customers is CASH, we'll create a filter to exclude cash customers.

The filter will be specified at the container level (i.e. the invoice container) so we'll need to have access to the CustomerID field (i.e. the field we're filtering on) from that container.
Under the Map Expressions column, click Switch to Filter Expressions.

We are now in the Filter Expressions view and since these expressions can only be applied to containers, the expression textboxes only display next to containers.
Connect CustomerID → invoice (an error will be shown because we have multiple fields mapped with no expression specified).

Set the Filter Expression for invoice to {CustomerID} <> "CASH".

When the preview refreshes the order for the CASH customer should no longer be shown and should look like this:

<request>
	<invoice>
		<client_id>ATA005</client_id>
		<date>2019-05-06</date>
		<notes>Deliver to: Howard Snyder</notes>
		<lines>
		<line>
			<name>INV001</name>
			<quantity>14</quantity>
		</line>
		<line>
			<name>INV002</name>
			<quantity>6</quantity>
		</line>
		</lines>
	</invoice>
	<invoice>
		<client_id>ZXS002</client_id>
		<date>2019-05-06</date>
		<notes>Deliver to: Yoshi Latimer</notes>
		<lines>
		<line>
			<name>INV003</name>
			<quantity>12</quantity>
		</line>
		<line>
			<name>INV004</name>
			<quantity>6</quantity>
		</line>
		<line>
			<name>INV002</name>
			<quantity>2</quantity>
		</line>
		</lines>
	</invoice>
</request>

You have now completed the mapping. Run the Workflow before submitting the exercise for grading. Only the QuickMap Node should run, don't connect the FreshBooks Node.

Save and run your Workflow, then click Submit Exercise to grade it.