Restructure

Learn how to use the restructure action item in DBSync Cloud Workflow

At times, records must be grouped to construct a single message for an application adapter. This is often required when processing records that contain multiple sub-records.

A common use case is invoice processing. Some applications, such as QuickBooks, MS Dynamics (GP), or Intacct, require invoice requests to be submitted as a single request. However, users may have invoices stored as separate records in a database table or view.

Database queries and CSV file readers often present grouped records as flat responses, making it difficult to convert them into a structured XML format.

Example Data Representation:

Invoice Number	Date	PO	ID	Product Code	Quantity	Amount	Total
1	1/1/2011	123	1	ABC	10	100	200
1	1/1/2011	123	1	DEF	20	200	200
1	1/1/2011	123	1	GHI	30	300	200
2	2/1/2011	123	2	ABC	10	100	300
2	2/1/2011	123	2	DEF	10	100	300

In this example, there are two invoices. The first invoice (invoice_num = 1) has three line items, while the second invoice (invoice_num = 2) has two line items. The target application expects two records instead of five.

To achieve proper grouping, the Restructure property can be used. It processes the response from the reader and organizes it into the correct XML structure.

To configure the reader, set the restructure property as follows:

invoice_num, date, po, id, [product_code, prod_qty, prod_amt], total

Any values enclosed in square brackets ([and]) are treated as sub-items. The column preceding the brackets serves as the grouping key, meaning a change in this value triggers the creation of a new XML structure. In this case, the grouping column is "id."

The split operation creates a node on the element before the brackets, labelled as "list." The XML output will be structured as follows

<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>

Map the target loop structure to id/list along with the field mappings.

Batch Size Customization:

To enhance flexibility, the Restructure feature now supports batch size customization. Users can specify a batch size that determines how many grouped records are processed together. This feature is particularly useful when handling large datasets, as it improves efficiency and system performance.

To configure the batch size, set the batch_size parameter in the Restructure property:

Example:

batch_size=100

This configuration ensures that records are grouped and processed in batches of 100 before being sent to the target application.

Limitations:

• Only one level of grouping is allowed (i.e., there can only be one [ and one ]).

• The grouping column (e.g., id) should not be mapped to any field except for the loop field in the mapping.