Page History

...

At times, we have to group records to construct a single message to send to an application adapter. This is often seen while 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.

...