Mapping Data

Abstract

Constructing and deconstructing the data in the various EDI formats available is a task that requires both analysis and development skills. Most of the document types within the manufacturing and shipping space are fairly straightforward to handle, but you will run across node structures and mapping requirements that will pose challenges.

These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Constructing and deconstructing the data in the various EDI formats available is a task that requires both analysis and development skills. Most of the document types within the manufacturing and shipping space are fairly straightforward to handle, but you will run across node structures and mapping requirements that will pose challenges.

It is possible to look at an implementation guide and make intelligent decisions about how data should be mapped. To improve your chances for a compliant EDI document early in the development process, make sure to take the following steps:

• Whenever possible, get access to an actual EDI document that is being used for the specific trading partner you are working with. Implementation guides are good for reference, but as a developer, having access to the actual file is of immense value. It allows for side-by-side comparisons between what you are creating in BizTalk and what you know is a valid format.

• There are many alternative ways to populate data in the various formats, and it is common to have mapping requirements that are unique to a trading partner. Be prepared to have maps that are very different between partners, whether inbound or outbound.

FormalPara Note

Don’t try to create a single map that will work for all of your trading partners—this requires too much logic in a map. One map per partner per document type is the general rule for mapping.

• In some cases, mapping is best done in multiple stages. It is always nice to be able to map a source document to the target EDI document (or vice versa) in a single map, but it does not always allow for the easiest or most maintainable solution. When developing your maps, think about the next person who may have to take these maps over from you at a later time. Will they be able to interpret what you have done and make modifications to it, if needed? When planning your mapping, think about ways to simplify your logic, and determine if creating several maps that take the data through several phases of transformation could ease your development.

The topics in this chapter will cover how to approach mapping various EDI formats with several development techniques. It won't be possible to demonstrate how to map a document in its entirety, but with some key foundational information available and some patience, you will be able to create maps that are well architected and use the most appropriate mapping technologies.

FormalPara Note

When building outbound maps where you have control over the source data you are mapping from, try to pre-format as much data as possible so that once it gets to the BizTalk map, there is as little additional mapping needed as possible. The less complex your map is, the easier your solution will be to develop, test, and maintain.

BizTalk Mapping Technologies

There are several technologies that you will need to be very comfortable with in order to have the tools available in BizTalk to handle the mapping that is associated with working the various document implementations. The BizTalk Visual Studio map is the canvas for development, but there are numerous options for mapping the data. These mapping technologies are as follows:

• Functoids: There are many functoids available to you, and it is possible to implement almost all mapping requirements without the use of externals scripts. However, trying to implement all of your maps without the use of external scripts is a mistake, and will most likely lead to complex and unmaintainable maps.

• Inline .NET Code: The Scripting functoid in the mapper allows for a variety of script languages. You can accomplish quite a bit within these scripts, but you have limited access to libraries. Only those libraries available to XLANG can be used within a .NET Scripting functoid.

• External .NET Assemblies: When you need the power of the full .NET engine and associated libraries, you'll want to develop an actual assembly with methods that can be called from the map. Some tasks, such as interactions with a database, are often best done in external assemblies.

• Inline XSLT: This is a very important skill to develop, and should not be overlooked or avoided. Tasks that could take many functoids or complex external .NET scripts can be done quickly using XSLT. Some mapping issues in more complex documents can't even be solved without the use of XSLT. While there will be some ramp-up in learning this scripting language, it will be worth your investment.

• External XSLT: In most cases, you will use the functoids and mapping options available in the map to complete your solution. In other cases, you may want to simply shell out to an external XSLT file to handle the mapping.

• SQL Stored Procedures: When dealing with business rules, data level transformations, or lookups, you may want to create stored procedures and deal with logic there. Calling a stored procedure through an external .NET assembly from a map is an easy architecture to build, and can be of great benefit. Several examples of calling a DLL from an orchestration were given in earlier chapters, and this approach can be applied to calls within maps.

When determining how to map and what technology or technologies to use, take the following steps to ensure you are making the right decision:

• Plan your architecture; wait to start development. It is easy and tempting to just start mapping. In fact, 80% of the mapping you'll do is straightforward, and you'll find that you make quick progress with much of the implementation. Then, as you begin on the final 20%, you'll start to run into hurdles that are very difficult to overcome, especially if you haven't built the rest of your map to support this 20%. You'll find that loops you've built and nodes you've mapped have to be rewritten to support your requirements. So, in short, plan your mapping before you start development.

• Start with the most complex nodes first. Assess what is the most complicated aspect of mapping, and deal with that first. It will ensure that you are developing with the right approach, and if you find that you have to rethink your approach, you haven't wasted a lot of time on other mappings.

• Use XSLT. There are so many complexities in mapping that are reduced and simplified by using XSLT that it is a mistake not to plan on using it extensively in your maps.

Looking at actual examples of mapping technologies is the topic of the next section. With these specific examples in front of you, you should be able to jump into the development of your maps with a solid direction as to how to implement.

Using Inline C# to Map ITD04 and Other Dates

One item that you will likely have to deal with in the creation of the 810 is the format of certain date fields, such as the ITD04. Your inbound data format will likely differ from the requirements of the format for these fields, and some conversion will likely need to take place. Converting dates can be done in a variety of ways, including a pattern of multiple standard functoids (string concatenates, rights, lefts, finds, etc.). In this case, you will look at some inline C#, as shown in Listing 4-1. This assumes that the date format is YYYYMMDD and that there is a parameter (period) indicating how many days need to be added to this date.

Listing 4-1. Date Conversion Using Inline C#

public string getDateInfo(string date, int period)

{

 // convert to valid date format

 string newDate = date.Substring(4,2) + "/" + date.Substring(6,2) + "/" + date.Substring(0,4);

 DateTime dt = DateTime.Parse(newDate);

 // add the period and convert it to new date format

 newDate = dt.AddDays(period).ToString("yyyyMMdd");

 return newDate;

}

Note

Inline functions can be used multiple times in a map by creating a script functoid that has no inputs or outputs and placing within it the function (or functions) that you want accessible globally to the map.

Date Conversions Using an External .NET Assembly

In some cases, you may have requirements that are either very involved, or need to be used across multiple maps and/or orchestrations. Building on the date conversion from the inline sample, imagine a scenario where the source data was generated by SQL Server and all dates are in the format similar to 2012-10-21T04:00:20.043. The target date fields (such as ITD04) need to have this format converted to 20121021. This formatting must be applied to many fields, and the code used to convert from the SQL format to the EDI format needs to be used each time.

There are many options for implementation, including using standard functoids, but the most appropriate solution that uses the least amount of components is an external .NET assembly. If standard functoids were used, you would have to use several string functoids in a pattern, and this pattern would have to be applied over and over in the map. With the inline C# option, you would have to paste the same code into multiple maps. With the external .NET assembly, the code is written once, and a Scripting functoid is dropped wherever the conversion needs to take place.

If the conversion logic needs to be changed for whatever reason (such as the source data format changes), then it only has to be changed in one place (the referenced DLL) and not everywhere the conversion is taking place (which would be the case with the functoid pattern).

In order to build this using an external .NET assembly, take the steps in the following exercise.

MAPPING WITH AN EXTERNAL .NET ASSEMBLY

This exercise will walk through calling an external assembly to format dates in EDI-compliant formats.

• 1. Create a .NET class library. Give it a namespace of Maps.Helper, and a class name of Helper.

• 2. Create a method called FormatDate, which has one input parameter in string format. Write the code to convert from the source format to the target EDI format. An example of this code is shown in Listing 4-2.

• 3. Compile the assembly and reference it in your Visual Studio map project.

• 4. In the map, drop a Scripting functoid on the map surface. Drag the input from the source document's date field, and drop it on the target document's date field.

• 5. Open the Scripting functoid and click the Script Functoid Configuration tab. Set the script type to External Assembly, then select your Script assembly, class, and method from the drop-downs. If you assembly is not shown, try closing Visual Studio and reopening. In some cases, you may have to install the DLL to the Global Assembly Cache (GAC; see next step) in order to be able to see it.

• 6. Test the map. This requires that the assembly be deployed to the GAC. Chapter 1 discusses how to deploy assemblies via the BizTalk Administration Console, which places them in the GAC.

An example of this functoid being used on the ITD04 field is shown in Figure 4-1.

Figure 4-1.

Calling an external assembly from a map

Listing 4-2. Formatting a Date

public string FormatDate(string dateString)

{

 if (string.IsNullOrEmpty(dateString) == false)

 {

  try

  {

   DateTime date = XmlConvert.ToDateTime(dateString,

    XmlDateTimeSerializationMode.Local);

   return date.ToString("yyyyMMdd");

  }

  catch

  {

   return string.Empty;

  }

 }

 else

 {

  return string.Empty;

 }

}

Mapping the N1Loop1 with Inline XSLT Call Template

Another item you will have to deal with in mapping the 810 is the creation of the N1Loop1, a repeating node that likely won’t map directly to your source data. This is a case where using inline XSLT will be of use and will eliminate the complexity of trying to deal with this through the use of standard functoids. Figure 4-2 shows the map pattern for this. On the left is the source data, and there is one field, EDIReferenceCode, being used as an input parameter to the inline XSLT Call Template script (the functoid on the right). This first parameter represents the N103 field. The script functoid on the left is another input to the inline XSLT, and is a database lookup for the N104 field.

Figure 4-2.

Using inline XSLT call template to map the N1Loop1

As you know, the N1Loop1 can consist of many addresses. Some are static (like the source RI company data) while others are dynamic (based on the source data). The example XSLT script in Listing 4-3 shows how to deal with both static and dynamic data, along with handling two input parameters to the script.

Listing 4-3. Inline XSLT Call Template for the N1Loop1

<xsl:template name="N1Loop">

<xsl:param name="N103" />

<xsl:param name="N104" />

<xsl:element name="N1Loop1">

 <!-- hard coded Source Company values -->

 <xsl:element name="N1">

  <xsl:element name="N101">RI</xsl:element>

  <xsl:element name="N102">Demo Company</xsl:element>

  <xsl:element name="N103">91</xsl:element>

  <xsl:element name="N104">123456789</xsl:element>

 </xsl:element>

 <xsl:element name="N3">

  <xsl:element name="N301">Department 123</xsl:element>

 </xsl:element>

 <xsl:element name="N4">

  <xsl:element name="N401">Grand Junction</xsl:element>

  <xsl:element name="N402">CO</xsl:element>

  <xsl:element name="N403">81502</xsl:element>

 </xsl:element>

</xsl:element>

<!-- loop through dynamic number of source segments for remaining -->

<xsl:for-each select="//HAD_Lines">

 <xsl:element name="N1Loop1">

  <xsl:element name="N1">

   <xsl:if test="CodeInMessage = 'INV'">

    <xsl:element name="N101">BT</xsl:element>

    <xsl:element name="N102">

     <xsl:value-of select="Name" />

    </xsl:element>

   </xsl:if>

   <xsl:if test="CodeInMessage = 'DEL'">

    <xsl:element name="N101">ST</xsl:element>

    <xsl:element name="N102">

     <xsl:value-of select="Name" />

    </xsl:element>

    <xsl:element name="N103"><xsl:value-of select ="$N103"/></xsl:element>

    <xsl:element name="N104"><xsl:value-of select ="$N104"/></xsl:element>

   </xsl:if>

  </xsl:element>

  <xsl:if test="Name02 != ''">

  <xsl:element name="N2">

   <xsl:element name="N201">

    <xsl:value-of select="Name02" />

   </xsl:element>

  </xsl:element>

  </xsl:if>

  <xsl:element name="N3">

   <xsl:element name="N301">

    <xsl:value-of select="Address" />

   </xsl:element>

  </xsl:element>

  <xsl:element name="N4">

   <xsl:element name="N401">

    <xsl:value-of select="translate(substring-before(City,','),' ','')"/>

   </xsl:element>

   <xsl:element name="N402">

    <xsl:value-of select="translate(substring-after(City,','),' ','')"/>

   </xsl:element>

   <xsl:element name="N403">

    <xsl:value-of select="ZipCode"/>

   </xsl:element>

  </xsl:element>

 </xsl:element>

</xsl:for-each>

</xsl:template>

Inline XSLT is amazingly useful with BizTalk mapping. It gives the level of control over parsing and manipulating data that is essential. The ability to look throughout the source data, regardless of where data lies in the overall hierarchy and node structure, allows for building the logic that may be required without having to build complex functoid pattern solutions.

Using Standard Functoids to Map the CTT Segment

On occasion, using standard functoids can be unpredictably simple. In the case of mapping the CTT node (Transaction Totals) on the 810, for example, getting the total number of line items and total quantity delivered can be done using a Record Count functoid and a Cumulative Sum functoid, as show in Figure 4-3.

Figure 4-3.

Mapping the CTT01 and CTT02 totals

For situations where you are mapping all of the line items to the output, these two functoids are extremely helpful and the map is simple. However, if you were required to filter out some of the outbound line items, and provide a total in CTT01 and CTT02 only for those lines that were mapped, you’ll run into a situation where once again you’ll want to use inline XSLT, as the options for mapping via standard functoids are not good.

Case Statements and the PO1Loop1 Node

When mapping the inbound 850, you’ll likely run into some interesting conditions when dealing with the PO1Loop1 data. Trading Partners can use any combination of the PO106 – PO125 data to represent their item data. You may find that in order to map to your inbound schema, you’ll need to determine what data is stored in which PO segment, and will have to map this in some particular order. One way of doing this is through inline C# that does the filtering and provides an output for other functoids in the map (such as a Value Mapping functoid). Figure 4-4 shows looping on the PO1Loop1 node, and each of the PO106 – PO125 nodes going to a single Scripting functoid. The C# for this functoid is shown in Listing 4-4.

Figure 4-4.

Mapping the PO1Loop1 data

Listing 4-4. Filtering the PO Data in Inline C#

// must find VC element if present. If not, return PO107 (the first)

public string FindCorrectItem(string PO106, string PO107, string PO108, string PO109, string PO110, string PO111, string PO112, string PO113, string PO114, string PO115, string PO116, string PO117, string PO118, string PO119, string PO120, string PO121, string PO122, string PO123, string PO124, string PO125)

{

 if(PO108 == "VC")

  return PO109;

 if(PO110 == "VC")

  return PO111;

 if(PO112 == "VC")

  return PO113;

 if(PO114 == "VC")

  return PO115;

 if(PO116 == "VC")

  return PO117;

 if(PO118 == "VC")

  return PO119;

 if(PO120 == "VC")

  return PO121;

 if(PO122 == "VC")

  return PO123;

 if(PO124 == "VC")

  return PO125;

 else

  return PO107;

}

Mapping the PADLoop1 Nodes

The final example we’ll look at is the detailed mapping of the outbound 845’s PADLoop1 nodes. There are a number of nodes that are challenging to construct, especially depending on the structure of the source document. The map in Figure 4-5 shows all of the functoids that are used for mapping the various nodes. There are straight mappings: one loop, one iteration, and several XSLT functoids. Each of the elements mapped are described in the following sections.

Figure 4-5.

The PADLoop1 mapping

The PADLoop1

This is the containing element and has an inbound Looping functoid attached to it. This will force a new loop instance of the PAD segments for each line item in the source data.

The PAD Elements

There is simple mapping in place for the PAD01 and PAD03 elements. PAD01 is the output of the Iteration functoid, which gives a simple line count. PAD03 shows a straight mapping from the reason code in the source data.

The PID Segment

More simple mappings here, but what is not shown in the map from Figure 4-5 is that the value of PID01 and others are given default values in the properties on the target elements. For example, PID01 is defaulted to “F”, as shown in Figure 4-6. This can be done by setting the Value property of the PID01 element in the properties dialogue box.

Note

While setting default values in the Value property is an option, you won’t have visibility on the map that the field has been mapped. If you want to keep visibility, place the default value in a simple Concatenate functoid and use this as the input to your target field.

Figure 4-6.

Setting a default value on PID01

The UIT Segment

The UIT data contains pricing and other information. For this mapping, a Scripting functoid with an inline XSLT call template is used. Four parameters are passed as inputs. The code is shown in Listing 4-5.

Listing 4-5. XSLT for the UIT Segment

<xsl:template name="UIT">

<xsl:param name="ContractPriceQualifier" />

<xsl:param name="ContractPrice" />

<xsl:param name="WholesalePriceQualifier" />

<xsl:param name="WholesalePrice" />

<xsl:element name="ns0:UIT">

<xsl:element name="ns0:C001_2">

 <xsl:element name="C00101">CA</xsl:element>

</xsl:element>

<xsl:element name="UIT02">

 <xsl:value-of select="$ContractPrice"/>

</xsl:element>

<xsl:element name="UIT03">

 <xsl:value-of select="$ContractPriceQualifier"/>

</xsl:element>

</xsl:element>

<xsl:element name="ns0:UIT">

<xsl:element name="ns0:C001_2">

 <xsl:element name="C00101">CA</xsl:element>

</xsl:element>

<xsl:element name="UIT02">

 <xsl:value-of select="$WholesalePrice"/>

</xsl:element>

<xsl:element name="UIT03">

 <xsl:value-of select="$WholesalePriceQualifier"/>

</xsl:element>

</xsl:element>

</xsl:template>

The DTM_5 Segment

The date and time information stored in this segment shows another instance of mapping using XSLT. The XSLT call template code is shown in Listing 4-6.

Listing 4-6. XSLT for the DTM Segment

<xsl:template name="DTM_5">

<xsl:param name="EffectiveQualifier" />

<xsl:param name="EffectiveDate" />

<xsl:param name="ExpirationQualifier" />

<xsl:param name="ExpirationDate" />

<xsl:element name="ns0:DTM_5">

 <xsl:element name="DTM01">

  <xsl:value-of select="$EffectiveQualifier"/>

 </xsl:element>

 <xsl:element name="DTM02">

  <xsl:value-of select="$EffectiveDate"/>

 </xsl:element>

</xsl:element>

<xsl:element name="ns0:DTM_5">

 <xsl:element name="DTM01">

  <xsl:value-of select="$ExpirationQualifier"/>

 </xsl:element>

 <xsl:element name="DTM02">

  <xsl:value-of select="$ExpirationDate"/>

 </xsl:element>

</xsl:element>

</xsl:template>

The LIN Segment

All of the line item information is handled in the XSLT call template code shown in Listing 4-7. It introduces some if statements into the logic.

Listing 4-7. XSLT for the LIN Segment

<xsl:template name="LIN">

<xsl:param name="LineNumber" />

<xsl:param name="UPNQualifier" />

<xsl:param name="UPN" />

<xsl:param name="NDCQualifier" />

<xsl:param name="NDC" />

<xsl:param name="VendorQualifier" />

<xsl:param name="ItemNumber" />

<xsl:element name="ns0:LIN">

 <xsl:element name="LIN01">

  <xsl:value-of select="$LineNumber"/>

 </xsl:element>

 <xsl:if test="$UPNQualifier != ''">

  <xsl:element name="LIN02">

   <xsl:value-of select="$UPNQualifier"/>

  </xsl:element>

  <xsl:element name="LIN03">

   <xsl:value-of select="$UPN"/>

  </xsl:element>

 </xsl:if>

 <xsl:if test="$UPNQualifier != '' and $NDCQualifier = ''">

  <xsl:element name="LIN04">

   <xsl:value-of select="$VendorQualifier"/>

  </xsl:element>

  <xsl:element name="LIN05">

   <xsl:value-of select="$ItemNumber"/>

  </xsl:element>

 </xsl:if>

 <xsl:if test="$UPNQualifier = '' and $NDCQualifier != ''">

  <xsl:element name="LIN02">

   <xsl:value-of select="$NDCQualifier"/>

  </xsl:element>

  <xsl:element name="LIN03">

   <xsl:value-of select="$NDC"/>

  </xsl:element>

  <xsl:element name="LIN04">

   <xsl:value-of select="$VendorQualifier"/>

  </xsl:element>

  <xsl:element name="LIN05">

   <xsl:value-of select="$ItemNumber"/>

  </xsl:element>

 </xsl:if>

 <xsl:if test="$UPNQualifier = '' and $NDCQualifier = ''">

  <xsl:element name="LIN02">

   <xsl:value-of select="$VendorQualifier"/>

  </xsl:element>

  <xsl:element name="LIN03">

   <xsl:value-of select="$ItemNumber"/>

  </xsl:element>

 </xsl:if>

 <xsl:if test="$UPNQualifier != '' and $NDCQualifier != ''">

  <xsl:element name="LIN04">

   <xsl:value-of select="$NDCQualifier"/>

  </xsl:element>

  <xsl:element name="LIN05">

   <xsl:value-of select="$NDC"/>

  </xsl:element>

  <xsl:element name="LIN06">

   <xsl:value-of select="$VendorQualifier"/>

  </xsl:element>

  <xsl:element name="LIN07">

   <xsl:value-of select="$ItemNumber"/>

  </xsl:element>

 </xsl:if>

</xsl:element>

</xsl:template>

Conclusion

This chapter gave an overview of how to be successful with your mapping solutions. It also gave specific examples around mapping several elements, and provided direction on how to implement inline scripts for complex maps. With the proper use of the techniques outlined in this chapter, and especially through the use of XSLT, you will be able to solve any mapping problem that may arise.