Things to do with the Map Variables Activity

Posted by jack on July 12, 2015

The map Variables activity superficially looks like the simplest of all Cast Iron activities – there are no end-points to connect or templates to select, just inputs and outputs. Yet it is one of the most versatile of Cast Iron activities. Here are a few things I use this activity for

  • Filtering using XPath
  • Counting records in an XML variable
  • Accumulating rows in an output variable when they meet specified criteria
  • Converting a ragged hierarchy to a flat record (for example from a cross-object salesforce query)
  • Merging values from one variable into matched values of another (payload) variable.

Filtering Using XPath like other Cast Iron features, is not immediately obvious in the Studio user interface – furthermore there are important limitations in how it works.

To use XPath filtering, right-click on the root of the recurring element to filter into and select Filter Recurring Nodes from the pop-up menu. The XPath Predicate is a statement like Status = ‘Cast Iron to Convert’  – only records meeting this criteria will be mapped. In this case, Status is a string value – XPath also works fine for Booleans  – eg boolEntry = true()  , You can also use for numbers eg numberValue = 0.5  – however, take care if you have nulls when using XPath filtering, particularly of dates and numbers.

Cast Iron XPath filtering does NOT work with data as formatted out of a Salesforce Query – the structure of the output variable with multiple levels including object, is not catered for in the Map Variable XPath filter, which basically only handles simple XML variables with a flat single level data structure. To filter data extracted from Salesforce therefore you must map the data to such a variable first – I create my XSD using XML Spy Pro but you can use any tool that suits – just make sure that the resulting variable has one level and does not include the object level. Here is an example of such an .xsd:

<?xml version=”1.0″ encoding=”UTF-8″?>
<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<xsd:element name=”users” type=”user”/>
<xsd:complexType name=”user”>
<xsd:sequence>
<xsd:element name=”rows” type=”row” minOccurs=”0″ maxOccurs=”unbounded”/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name=”row”>
<xsd:sequence>
<xsd:element name=”userName” type=”xsd:string”/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>

You can filter from one input variable to multiple output variables in one Map Variables activity, using a different XPath extression for each – but if you do this do not filter from the variable to itself but use entirely new variables on the output.

Accumulating rows in an output variable when they meet specified criteria Sometimes I have to go through this process

  1. Pick up some records
  2. Check & classify each one
  3. Accumulate into groups of similar records for further processing

Accumulating records uses the map variables activity. Both recordToAdd and similarRecords are input variables in this case, with just similarRecords as the output. To add in recordToAdd, before mapping  SimilarRecords, right-click on the root of the recurring element in the output, and select Expand Occurrences – them map the input similarRecords  to the many records element of the output, and recordToAdd to the single additional occurrence.

You must ensure that  similarRecords is initialised when used in this way to accumulate records.

Merging values from one variable into matched values of another (payload) variable – an approach I worked out to avoid having to write XSLT  templates all the time – it is much quicker to code, easier for others to understand and does not seem to impose much of a performance hit.

Here is a typical problem. I have updated versions of records coming into the data warehouse and I want to update the existing values in Salesforce to match, but have not set the record identifier as an external key in Salesforce. Of course, it would be much easier if I had set that key, but there are circumstances where that is not possible. So – what I want to do is

  1. Query the data warehouse to extract the up-to-date records
  2. Query Salesforce to get existing matched values
  3. Match the 2 sets together, putting the salesforce Id into the matched up-to-date records so the7y can be used to update Salesforce
  4. Identify records not already in Salesforce, and insert those.

One approach is to use 2 for each loops for each up-to-date record (loop1), find the matching  salesforce record (loop2) – fine if you have 10 records (max 100 loops), not so good when you have 25,000 (max 625 million loops) – you might wait a long time….

My solution uses one loop, one custom javascript function and one map variables activity to do the same  job. The variable with the values to merge in (in our example the Salesforce records) is looped, and the payload variable amended to merge in the required values (in our example the Salesforce Ids)

The javascript custom function mergeMatchedValue has these input variables – payloadKey, payloadValue, matchKey, matchValue. The javascript code in the function is

var returnValue;

returnValue=payloadValue;

if(payloadKey == matchKey) returnValue = matchValue;

return returnValue;

The for each loop variable salesforceRecord and the payload variable payload form the input to a map variables activity, and payload forms the output. Drag to link the root of payload, then un-map the mapping for the value for (salesforce) Id.

Now pull the mergeMatchedValue function into the activity, and link the business key and (salesforce) Id from the payload variable to function inputs payloadKey and payloadValue. Then map the business key and (salesforce) Id from the loop variable salesforceRecord to the function inputs matchKey and matchValue. Map the output from the function to the output field (salesforce) Id in Payload.

Voila! when run the salesforce Ids get merged into the payload variable. You can then use map Variables activity with XPATH filtering downstream of the loop to filter the Payload into two new variables payloadWithExistingSalesforceRecords and payloadNoExistingSalesforceRecords

The equivalent XSLT template runs to about 80 lines of not easy to understand code.

Categories: Cast Iron

Comments are closed.