How Do I Query Multiple Objects with the SAPB1 Connector? (Magic xpi 3.x)

The SAPB1 Data Interface (DI) API handles a single object at a time. Magic xpi’s Data Mapper service, on the other hand, lets you query multiple objects in a single step. When configuring the step, Magic xpi turns to the DI to query the multiple objects as necessary.

At the end of this topic, you will find a special consideration regarding the SAPB1 main object's compound element.

Note: During add or update operations in User Table systems, fields - like Object, UserSign, Series, Period, Transfered, CreateDate, CreateTime and DataSource - that are maintained by the SAPB1 application are read-only (not updateable) by the user.

Example

Querying multiple SAPB1 objects has three principal stages:

• Stage 1:Configuring Magic xpi’s SAPB1 connector.

• Stage 2: Mapping the multiple query results.

• Stage 3: Viewing the mapped multiple query results.

Stage 1: Configuring the SAP B1 Component

This example describes how to query multiple Business Partners when the list of Business Partners is in a text file. For this example, create a sample text file using the information below.

The Business Partner file template is:

BPName,CardCode,CardType,Tel1,Tel2,Contact,email

The data in the text file is:

Test 00001,M00001,cCustomer,111-00001,222-00001,Contact 00001,1@mail.com

Test 00002,M00002,cCustomer,111-00002,222-00002,Contact 00002,2@mail.com

Test 00003,M00003,cCustomer,111-00003,222-00003,Contact 00003,3@mail.com

Test 00004,M00004,cCustomer,111-00004,222-00004,Contact 00004,4@mail.com

Test 00005,M00005,cCustomer,111-00005,222-00005,Contact 00005,5@mail.com

The steps below show how to query multiple Business Partners when the list of Business Partners is in a text file:

1. Define an SAPB1 resource in the Resource Repository.

2. Drag the SAPB1 connector to your flow.

3. In the Component Properties dialog box, select XML as your interface.

4. In the Settings tab, select the SAPB1 resource that you previously defined in the Resource Repository. If you have not yet defined an SAPB1 resource, you cannot proceed to configure the SAPB1 connector.

5. Click Configuration.

6. Click to open the Data Object List, select Business Partners, and then click OK.

7. The Source/Destination Management window opens. Click New, and set the source type to Flat File.

8. Click Properties, and then copy the text file path you created earlier into the Example field and select a Data Source.

9. Click Refresh to load the example file. The Data Mapper uses the information in the example file to define how to arrange the data for your flat file.

   

10. Click OK.

11. When the Source/Destination Management window opens, click Map.

12. When the Data Mapper screen opens, create the relevant query parameter connections. In this example connect the two CardCode elements. To query multiple objects, connect the Record element to the BO element. To see a sample image click here.

    

Magic xpi creates a single XML file that holds the multiple objects’ data. Magic xpi then uses this file to query SAP B1’s DI. The XML file is then saved in the UserBLOB variable.

Note: You can specify where you want to store the query results. These options are located in the Advanced Query Option section of the SAPB1 2005 configuration dialog box, in the Store result in drop-down list.

Click here to view a sample of the query results in XML code.

Stage 2: Mapping the Multiple Query Results

The multiple query results are stored in a Magic xpi ODS table. The list of the Business Partners names is saved in the ODS Alpha field.

To map the query results do the following:

1. Drag a Data Mapper service onto your SAPB1 connector, and then click Configuration.

2. The Source/Destination Management dialog box opens. In the Source area click New, and then enter a source name. The example below uses the name QueryResult_BP.

3. Set the source type to either:

   • XML - set the source type to XML, and then select the Business partner XSD located in
     <current project>\SAPB1_2004\XSD\oBusinessPartners(2).xsd, or

   • IFC Model - set the source type to IFC model, click to open the Component list and select the SAPB1 connector. Then click to open the Schema List and select Business Partners. From the Data Source list box select UserBLOB.

4. In the Destination area, click New, enter ODS as both your Source Name and Type, and then click Properties.

5. In the Destination Properties dialog box, click the ODS button to open the ODS Repository.

6. Click New and enter a Key Name. This example uses the name BP_Name. Select the check box and click OK.

7. Click Map and then connect the BO element to the BP_Name element, which you created in the previous step. Then connect the CardName element to the Alpha element. To see a sample image click here.

   

Stage 3: Viewing the Mapped Multiple Query Results

1. Run the Debugger. On the Debug menu, in System Tables, click ODS.

2. Click to open the ODS Details window and view your query results. To see a sample image click here.

   

SAPB1 Main Object's Compound Element

In the SAPB1 schema of each object, the compound element of the main object (for example Business Partner \row) is mistakenly defined in the schema as a multiple occurrence, when in fact you only have one occurrence of this compound element in the XML.

This declaration causes a problem when trying to work with multiple Update, Query, and Delete in the SAPB1 connector and the source is an XML of an SAPB1 object as well, since according to the Mapper rules you cannot map multiple occurrence elements (rows) to a single occurrence (QueryParams).

There are two solutions:

1. Define a filter on the row level (which will always be true) to make it a single occurrence.

2. Modify the XSD manually.