Version 22.0.8473

• Overview
• EDI Mapping in CData Arc
  • Pulling Database Data as XML
  • Pulling Data Relevant for an 810 Invoice
  • Representing an EDI Document as XML
  • Transforming XML Structures
• Creating the Mapping Flow
  • Step 1: The Database Connector
    • Establishing a Connection
    • Creating the Output Mapping
    • Output Mappings as XML
  • Step 2: The X12 Connector
    • Upload a Test File
    • Configuring the X12 Connector
  • Step 3: The XML Map Connector
    • Configuring the XML Map Connector
    • Understanding the XML Structures
• Creating the Mapping
  • Creating Foreach Loops
  • Mapping Leaf Nodes
    • Simple Leaf Node Mappings
    • Contextual Mappings
    • Simple Calculations
    • Cumulative Calculations
    • Conditional Mappings
    • Index Mappings
    • Generating A Date in the Mapping
    • Counting Line Items and Total Cost
• Flow Summary
• Sample Data
  • Database Output Mapping
  • X12 810

Overview

CData Arc contains powerful tools to retrieve targeted data from back-end databases and map this data into an outbound EDI document (X12, EDIFACT, etc). This guide will walk through the process of creating a database-to-EDI workflow, focusing on generating an X12 810 document (Invoice). The sample Invoice used mirrors the syntax required by Amazon, and is provided at the bottom of the page.

The approach and principles described in this article are also applicable to generating other EDI documents, however there may be differences in the specific data pulled from the database and the relationships between mapped values.

This guide will begin with an overview of how to accomplish the mapping in Arc, then walk through each step of the mapping flow. The sample data used in this article (database structure and output files and the resulting 810 document) are included at the bottom of the page.

EDI Mapping in CData Arc

Pulling Database Data as XML

Arc uses XML as a common format for data transformation and manipulation. All EDI mapping flows involve converting the source data (in this case database data) and destination data (in this case an EDI file) into XML.

All Arc connectors that pull data from databases (or database-like applications) automatically format the output data as XML files. Thus, data that is pulled from a database is immediately ready to be mapped or transformed. This guide will use the Database Connector configured with a MySQL driver to interface with a MySQL database.

Pulling Data Relevant for an 810 Invoice

Invoices are typically sent to trading partners in response to a Purchase Order. When a manufacturing company receives a Purchase Order that requests a set of manufactured goods, the manufacturing company responds with an Invoice to pay for the requested goods.

As a result, the data used to generate an Invoice typically comes from a Purchase Order received earlier. This guide assumes that incoming Purchase Order data is automatically inserted into a database, so the Purchase Order database tables hold the data relevant to the 810 Invoice. When configuring a Database Connector for this mapping flow, the Database Connector will target the tables where the Purchase Order data is held.

Purchase Orders can contain multiple Line Items, and a typical database setup will store Line Item data in a ‘LineItems’ table that is separate from the ‘PurchaseOrders’ table. The ‘LineItems’ table has a foreign key to the ‘PurchaseOrders’ table in order to know which Line Items are associated with which Purchase Order, but data must be pulled from both tables in order to retrieve all of the necessary information to populate an 810 Invoice.

The section in this guide dedicated to configuring a Database Connector will include the steps necessary to pull from both a ‘PurchaseOrders’ table and a ‘LineItems’ table (with the appropriate foreign key relationship) at the same time.

Representing an EDI Document as XML

In order to map the XML data pulled from a database into an EDI document, the EDI document must also be represented as XML. Arc includes many different connectors to convert between EDI and XML formats (X12, EDIFACT, etc). This guide focuses on mapping an X12 document, so the X12 Connector will be used to generate an XML representation of the target 810 document.

It is important to have a sample output document (e.g. a sample 810) prior to creating the mapping flow. This sample document will be used to create the XML model for the mapping step.

Transforming XML Structures

Once both the source and destination formats have been represented as XML, the powerful XML Map Connector is used to transform one XML structure into the other.

The XML Map Connector requires a sample Source File and Destination File, which represent the starting XML structure and ending XML structure respectively. The XML output pulled from the database will serve as the Source File, and the XML representation of an X12 810 document (generated by passing a sample 810 through an X12 Connector) will serve as the Destination File.

Once the Source and Destination structures have been specified, the XML Map Connector’s visual designer is populated with the two structures. Elements from the source structure can be dragged and dropped onto the elements in the destination structure to create a mapping relationship between them. This mapping step requires an understanding of the EDI structure so that the appropriate elements are included in the output file.

The XML Map Connector includes value formatters, conditional logic, and even custom scripting to manipulate and compute data during the mapping process.

Once the mapping relationship has been created, the XML Map Connector can automatically convert any files that match the Source XML structure into a file with the Destination XML structure. In this example, this means that the data pulled from a database will automatically be converted into an XML model of an X12 810 document. The final step is to take this XML model of an 810 CSV file and convert it into an actual 810 document, which is easily accomplished with the X12 Connector.

Creating the Mapping Flow

This section contains each step to create a Flow in Arc that retrieves database output and maps it to an 810 Invoice.

Step 1: The Database Connector

The first step of a mapping flow is to configure a Database Connector to pull the relevant data from a MySQL back-end.

Establishing a Connection

In the Flows page of the application, drag an instance of the Database Connector into the canvas. For production flows, the Database Connector instance name should include the target database (in this case MySQL) and context information about the data being pulled or pushed into the database (e.g. MySQL_Invoice_tradingPartner); in this flow the connector instance name is ‘Database’ for simplicity.

The Database Connector is a general connector that can interface with many different types of databases, so it must be configured with the appropriate database driver to target a specific database. Arc includes a MySQL driver as part of the application, so no additional steps are required to install the MySQL driver where the Database Connector can read it.

After the MySQL driver is selected, the Database Connector settings panel automatically populates with the connection settings required for the target database. Enter the connection properties (or connection string) required to connect to the MySQL database.

The Test Connection button can be used to verify that the connector can successfully establish a connection to the target database.

Creating the Output Mapping

Once a connection is established, an Output Mapping can be created to pull the relevant data from the database. As mentioned in the Pulling Data Relevant for an 810 Invoice section, the Output Mapping must pull from both a parent ‘PurchaseOrders’ table and a child ‘LineItems’ table.

In the Database Connector settings, find the Mappings section and select ‘Output’. Use the + button to create a new Output Mapping, which will bring up a list of tables in the database. The parent table (with Purchase Order data) should be selected first, and the child table (with Line Item data) will be added later. In this example the Purchase Orders table is test_purchase_orders, and selecting this table brings up the Mapping Editor:

This editor allows for selecting the tables within the column that should be retrieved; this guide will include all of the table’s columns in the output XML.

One of the columns in the table is a ‘Processed’ column, which hold the value 0 if a record has not been processed, and 1 if it has. When Purchase Orders are inserted into the table, it is expected that the ‘Processed’ column will be set to 0. The Output Mapping should only pull records if the ‘Processed’ column is 0, and it should update this column with the value 1 after successfully pulling a record.

To accomplish this, first a Filter should be applied to only pull records where ‘Processed’ = 0. In the Filter section of the Mapping Editor, add a new rule:

• The first (left-hand) dropdown is the column to check; in this case ‘Processed’
• The second (middle) dropdown is the logical operator, in this case ‘equal’
• The last (right-hand) input is the value to compare against the column, in this case 0.

In addition to filtering records based on the ‘Processed’ column, this column needs to be updated so that records are not pulled more than once. The ‘Advanced’ section at the bottom of the Mapping Editor allows specifying a column that should be updated when rows are successfully pulled from the database.

In this case, the ‘Processed’ column should be updated with the value 1:

Now that the appropriate records will be pulled from the test_purchase_orders table, the Output Mapping should also be configured to pull the Line Items associated with each Purchase Order. In this example, the Line Item data is held in the test_po_line_items table.

In the top-left of the Mapping Editor, click the + button to add a new table to the mapping. Select the appropriate child table to the mapping:

The Mapping Editor will automatically update with the table tree structure in the left panel. Similar to the test_purchase_orders table, specific columns from the test_po_line_items can be selected for output; this guide will include all of the table columns.

When the Database Connector pulls a Purchase Order record from the test_purchase_orders table, it will also look to pull records from the test_po_line_items table. Only Line Items associated with the current Purchase Order should be pulled, so a ‘WHERE’ constraint must be applied on this child table.

In this example, the ‘PONumber’ column in the test_po_line_items table corresponds to a ‘PONumber’ column in the test_purchase_orders table. Only when the ‘PONumber’ from a Line Item matches the ‘PONumber’ from a Purchase Order should that Line Item record be included in the output for the Purchase Order record.

In order to accomplish this, add a new rule to the Filter section of the test_po_line_items mapping:

• The first (left-hand) dropdown is the column in the child table to check; in this case it should be ‘PONumber’
• The second (middle) dropdown should be ‘equal’
• The last (right-hand) input can be set to reference the parent table by clicking the dropdown arrow and selecting ‘REF’ (the ‘$’ symbol appears to indicate a reference)
• Finally the last (right-hand) input should be set to the column in the parent table to compare; in this case it should also be ‘PONumber’

The Output Mapping is now fully configured, and can be saved in the Database Connector.

Once this Output Mapping has been saved, data can be pulled from the database in two ways. If Receive Automation is enabled under the ‘Automation’ tab of the Database Connector, then the connector will automatically attempt to pull un-processed Purchase Orders (and the associated Line Items) according to the Receive Interval. Records can be manually pulled by navigating to the ‘Output’ tab and clicking Receive.

Output Mappings as XML

After an Output Mapping is created, the Database Connector represents this Output Mapping as XML. To view the XML model of the Output Mapping, click the </> button to the right of the configured Output Mapping:

When the Database Connector pulls data from the database, it will use the retrieved values to populate an XML document with this structure. Note that sub-structures within the XML file may repeat; for example if multiple Line Items are pulled for a single Purchase Order, the XML sub-section for the test_po_line_items table will repeat for each Line Item.

This XML structure will eventually need to be mapped into an XML structure representing an X12 810 Invoice; then, the resulting mapped XML can be converted directly into an EDI document.

Note: the Output Mapping used for this guide is available in the Sample Data section at the bottom of the page.

Step 2: The X12 Connector

The X12 Connector will perform two roles in this mapping flow. First, it will be used to generate an XML representation of a sample 810 Invoice; this XML sample will be used later, in the XML Map Connector, to map the database output into an EDI structure. Second, the X12 Connector will perform the last step in the flow: converting the XML generated by the XML Map Connector into an X12 document.

In the Flows page, drag an instance of the X12 Connector into the canvas. X12 Connector instance names should typically include the name of the trading partner sending X12 documents; in this Flow the instance name is ‘X12’ for simplicity:

Upload a Test File

The Upload Test File feature in the X12 Connector simplifies the process of generating an XML model for the target EDI document. Open the X12 Connector’s settings and navigate to the ‘Input’ tab; the More dropdown menu includes the Upload Test File option:

Browse on disk for a sample 810 Invoice to upload to the connector. The X12 Connector will generate an XML model of this document internally, which the XML Map Connector can automatically detect. Since the model is stored internally, the external appearance and configuration of the connector does not change.

Note: the sample 810 document used for this guide is available in the Sample Data section at the bottom of the page.

Configuring the X12 Connector

In addition to creating a sample XML model, the X12 Connector will be used to convert XML mapped by the XML Map Connector into the output EDI file. In addition to simply converting the XML format into X12 format, the X12 Connector also adds Interchange and Functional Group data to the document according to the settings configured in the connector. Thus, it is important to configure the X12 Connector with the settings specific to the partner who will be receiving the outbound X12 810 document.

Within the Settings Tab of the X12 Connector, the Translation Type property should be set to ‘XML-to-X12’. When operating in this mode, input to the X12 Connector should be XML that is structured as an EDI document. Using the Create Test File option from the previous step in the XML Map Connector ensures that the input XML matches this specific structure.

The X12 Connector also fills in Interchange and Functional Group values according to the settings configured in the connector. At a minimum, these values should be set:

• Sender Identifier - For outgoing EDI documents this is your identifier (if this value requires qualification, then Sender Id Qualifier should be set to the qualifying value)
• Receiver Identifier - For outgoing EDI documents this is your partner’s identifier (if this value requires qualification, then Receiver Id Qualifier should be set to the qualifying value)
• Test Indicator - Indicates whether an outgoing EDI document should be treated as Production Data or Test Data.

Your trading partner may require further Interchange (ISA) and Functional Group (GS) values to successfully receive the 810; communicate clearly with your trading partner what header elements are required. All of the X12 Connector’s configurable properties are documented here.

Step 3: The XML Map Connector

Now that the Database Connector and X12 Connector have generated an XML model of the input and output data respectively, the XML Map Connector is required to transform one XML structure into the other.

Drag an instance of the XML Map Connector onto the flow, then connect the Database Connector to the XML Map Connector and the XML Map Connector to the X12 Connector.

Note: Remember to save the changes to the Flow in the bottom-right corner of the Flows canvas (close any open connector configuration panels to see the blue save icon).

Configuring the XML Map Connector

Inside the XML Map Connector configuration panel, find the dropdown for Source File and Destination File. The XML Map Connector should detect the XML model from the Database Connector’s Output Mapping as an available Source File, and the XML model from the X12 Connector’s test file as an available Destination File. If either option is not available, verify that the test file and Output Mapping were appropriately saved, that the connectors are connected in the Flow canvas, and that the Flow changes were saved via the blue save icon in the bottom-right of the Flows page.

Once the Source File and Destination File are specified, the XML Map visual designer will automatically populate with the XML structures of the Output Mapping and the sample X12 810 file.

If the Interchange and FunctionalGroup elements in the Destination structure have Meta elements contained in them, these can safely be deleted; the X12 Connector will apply the relevant metadata to the ISA and GS segments when converting XML into EDI.

Understanding the XML Structures

Creating the right mapping relationships in the XML Map Connector requires understanding how the source and destination structures are represented in XML.

First, there are two types of XML elements (nodes) in the structures. Parent nodes are nodes with children but no value. Leaf nodes are nodes with values but no children. A Parent node can be expanded (to show its children) or contracted (to hide its children) with the + or - signs next to it.

In the Database XML, each Parent node represents a table from which data is being pulled, and each Leaf node represents a column in that table. In the 810 XML, each EDI loop and segment is a Parent node, and each individual EDI element is a Leaf node.

Creating the Mapping

Once the XML Map Connector has been configured with the Source and Destination structures, the mapping can be created by dragging and dropping nodes in the visual designer.

Mappings are created in two primary steps:

1. Creating the Foreach looping relationships between Parent nodes
2. Mapping values between Leaf nodes within the established Foreach loops.

Creating Foreach Loops

Foreach relationships mean that each time an element appears in the source, a new XML structure should be created in the destination. If ElementA in the source is mapped to ElementB in the destination, each occurrence of ElementA in an input file will result in an instance of ElementB (and all of ElementB’s children) in the output file.

In this case, each record pulled from the Purchase Orders table (PurchaseOrders) should result in a new Transaction (TX) in the resulting EDI, and each record pulled from the Line Items table (test_po_line_items) should result in a new IT1Loop. Drag the test_purchase_orders element onto the TX-00401-810 element, and the test_po_line_items onto the IT1Loop1 element:

Note: The sample X12 810 document used to create the Destination structure may have multiple IT1Loop1 elements that show up in the mapping designer. All but one of these elements can safely be removed (by right-clicking the node and selecting ‘Delete Node’), since the Foreach relationship will ensure that the correct number of IT1Loop1 elements are generated.

Mapping Leaf Nodes

Once the Foreach relationships have been established, the values from Leaf nodes can be mapped within the loops. Mapping Leaf nodes determine the values that populate the XML structures created by Foreach loops.

Some Leaf node mappings are simple, and others require use of the Expression Editor or Script Editor. This section will cover each of the common types of Leaf node mappings in an EDI document. More general information about various mapping features can be found in the documentation for the XML Map Connector.

Simple Leaf Node Mappings

When a value from the source should be directly inserted in the resulting output, simply drag a Leaf node from the source onto a Leaf node in the destination to map the values. This process requires an understanding of the EDI destination format and which values need to be included in the resulting X12 document.

For example, the BIG04 element in the X12 810 holds the Purchase Order Number for which the Invoice is created. Naturally, this corresponds to the PONumber element from the database output. Simply drag the PONumber node onto the BIG/BIG04 node to establish this relationship:

To the right of a mapped Destination node is a green xpath that indicates the source of the mapped value (e.g. ‘PONumber’). Note that Leaf node xpaths are relative to the Foreach xpaths in Parent nodes (e.g. ‘/Items/test_purchase_orders’). The xpath from all Parent nodes can be concatenated with the xpath in a Leaf node to find the full xpath in the Source XML for the mapped value: ‘/Items/test_purchase_orders/PONumber’.

In this example, other direct Leaf node mapping values include the following:

• PODate -> BIG/BIG03
• CurrencyCode -> CUR/CUR02
• ItemID -> IT1Loop1/IT1/IT107
• Quantity -> IT1Loop1/IT1/IT102
• PricePerUnit -> IT1Loop1/IT1/IT104
• PONumber (again) -> IT1Loop1/IT1/IT111

Each of these values can simply be dragged from the Source data and onto the Destination EDI document without modification or conditional logic.

Contextual Mappings

Some Leaf node mappings depend on other elements in the Destination. For example, an N1Loop1 loop in the Destination XML is a collection of elements that represent a party/entity in the EDI transaction (billing party, shipping party, purchasing party, etc). The N1Loop1/N1/N101 element indicates the specific role of the party, and the role of the party determines which values should be mapped from the Source XML.

In this guide, the source data includes party details for the Ship-To party and the Remit-To party. To include this data in the EDI output, there should be two separate N1Loop1 structures, one with N1Loop1/N1/N101 set to ‘ST’ (the value for Ship-To) and one with N1Loop1/N1/N101 set to ‘RI’ (the value for Remit-To). Then, the N1Loop1/N1/N102 value for each respective loop can be mapped by dragging and dropping ShipToID and RemitToID from the Source:

The other Ship-To data (ShipToAddr, ShipToZip, etc) can be mapped into the N2, N3, and N4 elements in the N1Loop1 structure where N101 is set to ‘ST’. Similarly, the other Remit-To data (RemitToAddr, RemitToZip, etc) can be mapped to the appropriate elements in the N1Loop1 structure where N101 is set to ‘RI’.

Note: Some EDI loops require multiple distinct nodes in the Destination mapping (like the N1Loop1 loop in this section), and others require only a single node in the Destination mapping (like the IT1Loop1 loop from the previous section). The difference depends on whether a Foreach relationship will automatically generate the correct number of output nodes: since the N1Loop1 node does not have a Foreach relationship associated with it, the Destination mapping needs to explicitly include the correct number of N1Loop1 nodes.

Simple Calculations

Some values are not pulled directly from the Source XML, but are calculated using one or more of the values in the Source. For example, each IT1Loop1 loop (i.e. each Line Item) has a TXI/TXI02 element that represents the amount of tax applicable for that Line Item. The Source data includes all of the information necessary to calculate the tax applied to the purchase:

• Price per unit
• Quantity
• Tax percent

However the calculated tax value is not itself present in the Source, and so must be calculated within the XML Map Connector.

The Expression Editor has Formatters that can be used to manipulate data during a mapping. In this case, the ‘multiply’ formatter is all that is required to convert the Source data into the Destination value.

Begin by mapping the PricePerUnit Source node onto the IT1Loop1/TXI/TXI02 Destination node. This serves as a good starting point for generating the appropriate mathematical expression, since the price per-unit is one of the input valuables in the expression. Then, open the Expression Editor via the ‘tablet and pencil’ symbol to the right of the Destination node:

Note that the ‘xpath’ value for Quantity must be surrounded by square brackets to be interpreted as a dynamic value instead of a string literal.

Now, this total cost value needs to be multiplied by the TaxPercent, and also multiplied by .01 to convert from percent to a dollar value:

Save the expression and the mapping will automatically calculate the appropriate value from the dynamic inputs specified.

Cumulative Calculations

The output 810 document has a TDS segment the represents the total monetary value of the Invoice. Summing the cost of each Line Item requires a persistent variable that can be updated in each Line Item loop.

Scripting is required to store values in variables and retrieve these values later in the mapping. Within ArcScript, values are stored as ‘attributes’, and ‘items’ are objects that can have multiple attributes. Attributes of an item are referenced with the following syntax: item_name.attribute_name

The \_map item is a special item that retains attribute values throughout the entire document mapping. In other words, most attributes (variables) are only preserved for a single node mapping, but attributes of the \_map item persist throughout every node in the mapping.

In order to sum the cost of each Line Item, therefore, an attribute of the \_map item should be set within the Line Items Foreach loop (i.e. within the IT1Loop1 loop). Since this scripting is not associated with any specific elements within the IT1Loop1 loop, a dedicated Script element should be added to the Destination mapping. This element does not appear in the resulting EDI document, but indicates that scripting logic is being performed for some other use within the mapping.

First, right-click the IT1Loop1 element and select ‘New Code Script’ to add a new Script element (this element can be repositioned by clicking and dragging it):

This script needs to calculate the full cost of the Line Item, which can be found with the following formula:

(PricePerUnit * Quantity) + (PricePerUnit * Quantity * TaxPercent * .01)

Edit the Script element to represent this formula in ArcScript:

<arc:set attr="lineItemCost" value="[xpath(PricePerUnit) | multiply([xpath(Quantity)])]" />
<arc:set attr="lineItemTax" value="[lineItemCost | multiply([xpath(TaxPercent)]) | multiply(.01) | round(2)]" />
<arc:set attr="_map.costSum" value="[_map.costSum | def(0) | add([lineItemCost]) | add([lineItemTax])]" />

After this script executes during each Line Item loop, the _map.costSum attribute will hold the total cost (including tax) for all Line Items. To reference this value in the TDS/TDS01 element, click the </> icon to the right of the TDS01 element and set the result.text value to the attribute from earlier:

Conditional Mappings

Some nodes require conditional logic to determine whether they should appear in the output, and what value should be set within them. For example, the ITD segment includes data about the Terms of the Invoice, and this only needs to be included in the output 810 if Terms are specified.

The ‘Filter’ icon next to the ITD element in the Destination mapping can be used to apply a condition that determines whether the element (and all of its children) will appear in the output document. Click this icon and add a rule in the Condition Editor; in this case the Terms of the Purchase Order will have a prefix that allows us to know whether terms must be specified in the Invoice:

Now that the ITD element will only appear if special Terms are specified, the ITD01 element needs to hold the appropriate code for the Terms. In this example, the following values should be used for the associated terms:

• 02 - Expedited Payment
• 03 - Delayed Payment
• 11 - Alternative Payment Profile

The values in the Terms element of the input data cannot be converted into the ITD01 value via Formatters, so a custom script is required to determine the ITD01 mapping. The ‘tablet and pencil’ icon to the right of the ITD01 element opens the Script Editor, and the following script accomplishes the pattern above:

<arc:set attr="termsCode" value="" />
<arc:set attr="termsInput" value="[xpath(Terms)]" />

<arc:select value="[termsInput]">
  <arc:case value="Expedited Payment">
    <arc:set attr="termsCode" value="02" />
  </arc:case>
  <arc:case value="Delayed Payment">
    <arc:set attr="termsCode" value="03" />
  </arc:case>
  <arc:case value="Alternative Payment Profile">
    <arc:set attr="termsCode" value="11" />
  </arc:case>
</arc:select>

<arc:set attr='result.text'>[termsCode]</arc:set>

The above example uses the arc:select keyword, but the same result can be accomplished with a series of arc:if and arc:else statements, as documented here.

Index Mappings

Some elements in the Destination XML need to count the number of loops in a Foreach relationship. For example, the IT1Loop1/IT1/IT101 element holds the Line Item number, so it needs to increment for each Line Item in the Purchase Order. Similarly, the Meta/ST02 element needs to increment for each Purchase Order in the document.

The \_index attribute is a special variable that holds the index of the most local Foreach loop relationship (i.e. for nested Foreach loops, _index will refer to the innermost loop). As long as the Foreach relationships are established correctly, both Meta/ST02 and IT1Loop1/IT1/IT101 can be set to the following value to correctly “count” the number of Purchase Orders and Line Items respectively:

[_index]

This special attribute is also available in any custom scripting environment, if the current index in a Foreach loop is relevant for more advanced mapping logic.

Generating A Date in the Mapping

Some elements in the Destination XML do not reference the Source data at all. The BIG/BIG01 element represents the date of the Invoice, which is not available until the current mapping step.

The special ‘now’ Date Formatter can be used to generate a new date value for the current datetime. Like all formatters, this is available in the Expression Editor for any node mapping.

All formatters require an input attribute to format. Often, during an XML Mapping, the input attribute will be a value retrieved with the ‘xpath’ formatter (i.e. a value parsed from the Source XML). In this case, it does not matter what attribute is passed to the ‘now’ formatter, since it always returns the current datetime. As a result, the default ‘_’ item can be passed as a placeholder:

Note that the parameter passed to the ‘now’ formatter defines the output format of the date.

Counting Line Items and Total Cost

The CTT segment includes elements for counting the total number of Line Items and a hash value that represents the total cost of the items. The approach to calculating these values is similar to that which is described in the Cumulative Calculations section.

The relevant values must be calculated within the section of the mapping where Line Item data is available (i.e. the IT1Loop1 section), and then referenced later in the CTT mapping. This requires a custom script that stores the values in the special \_map item.

Within the IT1Loop1 loop (which is mapped with a Foreach relationship to the test_po_line_items records), add a new Script node by right clicking and selecting ‘New Code Script’. This Script will be executed each time the mapping loops, or in other words, it will execute once for each Line Item. To calculate the values, the number of lines and total cost need to be summed and then stored in an attribute of the \_map item.

The Line Item count is calculated with a simple counter, and the cost can be calculated with the same formula used in the Cumulative Calculations section.

<arc:set attr="_map.lineItemCount" value= "[_map.lineItemCount | def(0) | add(1)]">


<arc:set attr="currentCost" value="[xpath(PricePerUnit) | multiply([xpath(Quantity)])]" />
<arc:set attr="currentTax" value="[currentCost | multiply([xpath(TaxPercent)]) | multiply(.01) | round(2)]" />

<arc:set attr="_map.totalCost" value="[_map.totalCost | def(0) | add([lineItemCost]) | add([lineItemTax])]" />

The CTT01 element can map directly to the _map.lineItemCount, so open the Script Editor and simply reference the attribute from earlier:

<arc:set attr="result.text">[_map.lineItemCount]</arc:set>

The CTT02 element can map to the _map.totalCost, however the CTT hash requires ignoring the decimal point, which is equivalent here to multiplying the cost by 100:

<arc:set attr="result.text">[_map.lineItemCount | multiply(100)]</arc:set>

Flow Summary

The Database Connector generates XML from Purchase Order data in a back-end system, and this XML is mapped into an EDI format by the XML Map Connector. The EDI XML is then passed to the X12 Connector to add Interchange headers and convert the XML into X12 format.

The most involved step of this flow is the creation of the XML mapping. Mapping requires establishing the Foreach relationships between Parent nodes, then mapping individual Leaf nodes with a combination of dragging-and-dropping, using the Expression Editor, adding Conditionals, and writing custom scripts.

Sample Data

Database Output Mapping

<Items>
    <test_purchase_orders selectQuery="SELECT * FROM `test_purchase_orders` WHERE `Processed` = 0">
        <PONumber key="true" />
        <CurrencyCode />
        <CustomerID />
        <PODate type="datetime" />
        <Processed type="int" updateValue="1" />
        <RemitToAddr />
        <RemitToCountry />
        <RemitToID />
        <RemitToState />
        <RemitToZip />
        <ShipToAddr />
        <ShipToCountry />
        <ShipToID />
        <ShipToState />
        <ShipToZip />
        <Terms />
        <test_po_line_items selectQuery="SELECT * FROM `test_po_line_items` WHERE `PONumber` = ${PONumber}">
            <ItemID key="true" />
            <PONumber />
            <PricePerUnit type="double" />
            <Quantity type="int" />
            <TaxPercent type="double" />
        </test_po_line_items>
    </test_purchase_orders>
</Items>

X12 810

ISA*00*          *00*          *ZZ*VENDOR         *ZZ*AMAZON         *090322*1001*U*00400*000001631*0*T*>~
GS*IN*VENDOR*AMAZON*20090322*1001*480*X*004010~
ST*810*0001~
BIG*20090320*5002841638*20090311*Q6515853~
CUR*BT*CAD~
N1*RI*VENDORNAME~
N3*PO BOX 1234~
N4*TORONTO*ON*M5W 5M5*CA~
N1*ST*AMAZON CANADA~
N3*500 MCCARTHY DR*FAIRVIEW BUSINESS PARK~
N4*MISSISSAUGA*ON*L5R 3W5*CA~
ITD*01*3*****60~
IT1*1*4*EA*12.6*NT*UP*786932788990*VN*1012380100000*PO*Q2515852~
TXI*GS*2.52*5~
IT1*2*30*EA*29.5*NT*UP*786936725951*VN*1000570100000*PO*Q2515852~
TXI*GS*44.25*5~
IT1*3*90*EA*22.5*NT*UP*782936735321*VN*0543900100000*PO*Q2515852~
TXI*GS*101.25*5~
IT1*4*5*EA*29.5*NT*UP*786936782268*VN*1000570200000*PO*Q2515852~
TXI*GS*7.38*5~
TDS*326330~
TXI*GS*155.4*5*****3108*954090650~
CTT*4*129~
SE*22*0001~
GE*1*480~
IEA*1*000001631~