The recommended SQL structure to generate multiple child records

The SAP Business One Integration tool has the ability to produce a structure from a flat database recordset known as ‘Recordset Shaping’.

Provided below is an example of the recommended SQL structure to produce a recordset of the correct format. This is necessary to enable mapping of multiple child records to a single header simultaneously.

Requirements

1. Free hand SQL.
2. Unions to allow multiple SELECT statements to be joined together one after the other in a single recordset. The following unions will be required:
   • A SELECT statement for producing records for each child type with NULLS in the fields for the other children.
   • A union to combine each child type.
   • The last SELECT and last union to return records where no children exist for the header.
3. A key column that acts a differentiator for the header and is populated within each SELECT statement.
4. An ORDER BY statement on the header fields.

Example SQL Structure

The SQL below uses an example for creating multiple address and contact entries for a single business partner.

It can be broken down as follows:

• The first select returns the addresses. Notice that the contact fields are null.
• The second select returns the contacts. Notice that the address fields are null.
• The third select returns the records where no address or contacts exist for a header.

In order to ensure that the records for a single header are returned together in the recordset, it would be advisable to sort the results of the union on the header fields. In this example, the statement “ORDER BY CardCode” has been placed after the final WHERE clause.

SELECT
BusinessPartner.CardCode AS CardCode,
BPAddresses.adresType AS adresType,
BPAddresses.Address AS Address,
BPAddresses.StreetNo AS StreetNo,
BPAddresses.Street AS Street,
BPAddresses.City AS City,
BPAddresses.Country AS Country,
Null AS Active,
Null AS FirstName,
Null AS LastName
Null AS Name

FROM
BusinessPartner INNER JOIN BPAddresses ON BusinessPartner.CardCode = 
BPAddresses.BPCardCode

Union

SELECT
BusinessPartner.CardCode AS CardCode,
NULL AS adresType,
NULL AS Address,
NULL AS StreetNo,
NULL AS Street,
NULL AS City,
NULL AS Country,
BPContacts.Active AS Active,
BPContacts.Firstname AS FirstName,
BPContacts.LastName AS LastName,
BPContacts.Name AS Name

FROM
BusinessPartner INNER JOIN BPContacts ON BusinessPartner.CardCode = 
BPContacts.BPCardCode

Union

SELECT
BusinessPartner.CardCode AS CardCode,
Null AS adresType,
Null AS Address,
Null AS StreetNo,
Null AS Street,
Null AS City,
Null AS Country,
Null AS Active,
Null AS FirstName,
Null AS LastName,
Null AS Name

FROM
BusinessPartner LEFT JOIN BPContacts ON BusinessPartner.CardCode = 
BPContacts.BPCardCode LEFT JOIN BPAddresses ON 
BusinessPartner.CardCode = BPAddresses.BPCardCode

WHERE
adresType is null and Active is null

ORDER BY
CardCode

A table can be used to show the recordset rows that this SQL query will generate:

For the header KG001, two contacts and two addresses will be created.

For the header KG002, one contact and one address will be created.

For the header KG003, one address will be created.

For the header KG004, no address or contact entries exist at present but a new header record will be created which will enable the new account to be updated at a later time.

Recordset Shaping within the SAP Integration Tool

Within the Recordset tab the initially flat recordset can now be shaped into a structure, as displayed in figure 1 below.

The nodes on the right that reflect the child element names are created by you within the tab and then the relevant columns of the recordset are added to each child element to form the structure.

In this case ‘Addresses’ and ‘ContactEmployees’ are the child elements. ‘CardCode’ is set as the key and refers to the header record. It is possible to have more columns from the recordset in the header. The ‘key’ column(s) are used to determine when a new header has been found.

Figure 1. SAP Integration Tool – Recordset Tab.

Mapping the New Structure to SAP Business One

Within the Mapping tab, links can then be created between your recordset structure displayed on the left and the XML structure of the SAP Business One object on the right.

Figure 2. SAP Integration Tool – Mapping Tab – Showing mapped ‘Addresses’ child record.

Figure 3. Showing mapped ‘ContactEmployees’ child record.

For further details on how to use the SAP Integration Tool, specifically the Recordset and Mapping tabs, refer to the relevant sections within our white paper.