Page History

...

At times, we have to group records to construct a single message to send to an application adapter. This is often the case when processing records that have multiple sub-records attached to it.

One area where we see this the most is while processing Invoices. Some applications - like QuickBooks, MS Dynamics (GP) or Intacct - need Invoice request requests as a single request. However, you a user might have this as a set of records in a single database table or view.

Lets look at an example -

Database queries, or CSV File  File readers, often have records that are grouped together as flat responses. The responses are not easy to convert , or map, into an XML structure.

For example:-

or in a CSV file as:

In the above example, we have a set of records that represents two invoices. The first (invoice_num=1) has 3 line items. And, the second (invoice_num=2) has 2 line items. When we send a request is sent to the target application, it expects to see 2 records request instead of 5 as described above.

To get the right grouping, we will make use of our xmlformatter XML-Formatter property capability. XMLFormatter property takes the response from the Reader and, groups it to form the right XML structure. To do so, the Reader will have a property "xmlformatter" and will be set as follows:

...

Any value within "[" and "]" will be treated like a sub-item. The column before "[" will be the driving column - i.e. a change in that item will create a new XML. In this example "id" becomes the grouping column.

...

<row>
 <invoice_num>value</invoice_num>
 <date>value</date>
 <po>value</po>
 <id id="">
 <list>
 <product_code>value</product_code>
 <prod_qty>value</prod_qty>
 <prod_amt>value</prod_amt>
 </list>
 <list>
 <product_code>value</product_code>
 <prod_qty>value</prod_qty>
 <prod_amt>value</prod_amt>
 </list>
 </id>
 <total>value</total>
 </row>

Now, all you have to do is map Map the target loop structure to "id/list" along with the field maps.

...