Introduction

Goals

• Export a journal entry record in XML format thru DI-API

• Format and simplify the XML file to make it valid format for DI-API

• Import back the formatted XML file thru DI-API

Please note the codes here are simplified for better understanding especially for beginners.

Versions

• SAP Business One 8.82 (8.82.072) PL: 09
• SAPbobsCOM88.dll (8.82.072)
• VB.NET 2013

Entire Source Code

For those who just wants the code and go, here is all you need.  For your convenience, I commented almost every single lines.

Public Class Form1

    ‘=======================

    ‘ Export XML

    ‘=======================

    Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

        ‘// Company object is the primary DI API object

        ‘// that represents a single SAP B1 company database.

        ‘// This object enables you to connect to the company database

        ‘// and to create business objects to use with the company database.

        Dim vCmp As New SAPbobsCOM.Company

        ‘// Set Company object properties

        vCmp.UseTrusted = True

        vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

        vCmp.Server = “xxxxxx”

        vCmp.LicenseServer = “xxxxxx:30000″

        vCmp.CompanyDB = “xxxxxx“

        vCmp.UserName = “xxxxxx“

        vCmp.Password = “xxxxxx“

        ‘// Sets a valid value of BoXmlExportTypes that specifies the types

        ‘// for exporting data from the database to XML format.

        vCmp.XmlExportType = SAPbobsCOM.BoXmlExportTypes.xet_ExportImportMode

        Try

            ‘// Connects to the SAP Business One company database.

            vCmp.Connect()

            ‘// Call JournalEntries obj

            Dim oJE As SAPbobsCOM.JournalEntries

            ‘// BoObjectTypes Enumeration specifies the object types.

            oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

            ‘// GetByKey method retrieves and sets the values

            ‘// of the object’s properties by the object’s absolute key

            ‘// from the Company database.

            If oJE.GetByKey(116541) Then

                oJE.SaveXML(“C:\temp.xml”)

                MsgBox(“OK!”)

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            vCmp.Disconnect()

            vCmp = Nothing

        End Try

    End Sub

    ‘=======================

    ‘ Import XML

    ‘=======================

   Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click

        ‘// Company object is the primary DI API object

        ‘// that represents a single SAP B1 company database.

        ‘// This object enables you to connect to the company database

        ‘// and to create business objects to use with the company database.

        Dim vCmp As New SAPbobsCOM.Company

        ‘// Set Company object properties

        vCmp.UseTrusted = True

        vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

        vCmp.Server = “xxxxxx“

        vCmp.LicenseServer = “xxxxxx:30000″

        vCmp.CompanyDB = “xxxxxx“

        vCmp.UserName = “xxxxxx“

        vCmp.Password = “xxxxxx“

        Try

            ‘// Connects to the SAP Business One company database.

            vCmp.Connect()

            ‘// Call JournalEntries obj

            Dim oJE As SAPbobsCOM.JournalEntries

            ‘// BoObjectTypes Enumeration specifies the object types.

            oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

            ‘// Browses XML formatted data and enables to update the data.

            ‘

            ‘// Parameters

            ‘// XmlFileStr: Specifies the XML file name or the XML content string

            ‘//            depending on the value of the XMLAsString property.

            ‘// Index :  Specifies the number of the object that you want

            ‘//          to read from the XML data (starts from 0, default 0).

            oJE.Browser.ReadXml(“C:\temp.xml”, 0)

            ‘// The Add function adds a record to the object table

            ‘// in SAP B1 company database.

            ‘// If there is no errors while importing, the Add function returns 0

            If oJE.Add() = 0 Then MsgBox(“OK!”)

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            ‘// Disconnects an active connection with the company database.

            vCmp.Disconnect()

            vCmp = Nothing

        End Try

    End Sub

End Class

Step-By-Step Guide

Add Reference

First of all, add SAP Business One DI API from the Reference Manager.

Set a Form

From the Form1, add two buttons named btnExport and btnImport.  Double click the each button, so you can create events when btnExport and btnImport are pressed.

The code would looks like this:

Public Class Form1

    Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

    End Sub

    Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click

    End Sub

End Class

Coding to Export XML

Company Object

The code below is the standard way to connect SAP B1 server.  The example code below is for SQL Server using Windows Authentication. If your SQL Server is not using Windows Authentication, you need to “UseTrusted” property to “False”, and add “DbPassword” and “DbUserName” property.

Write the codes in “btnExport_Click” method:

        ‘// Company object is the primary DI API object

        ‘// that represents a single SAP B1 company database.

        ‘// This object enables you to connect to the company database

        ‘// and to create business objects to use with the company database.

        Dim vCmp As New SAPbobsCOM.Company

        ‘// Set Company object properties

        vCmp.UseTrusted = True

        vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

        vCmp.Server = “xxxxxx”

        vCmp.LicenseServer = “xxxxxx:30000″

        vCmp.CompanyDB = “xxxxxx“

        vCmp.UserName = “xxxxxx“

        vCmp.Password = “xxxxxx“

XML Export Type

Within the property “XmlExportType“, you can select the XML exporting type.  Choose “xet_ExportImportMode” in this example.

Write the code below after the previous codes:

        ‘// Sets a valid value of BoXmlExportTypes that specifies the types

        ‘// for exporting data from the database to XML format.

    vCmp.XmlExportType = SAPbobsCOM.BoXmlExportTypes.xet_ExportImportMode

FYI: There are other types according to the SAP Business One SDK help file…

Member	Description
xet_AllNodes	Export to XML all fields (both read only and read/write fields) from the database. (XML files exported using this type cannot be read by the ReadXml method.)
xet_ValidNodesOnly	Export to XML only valid fields that support XML import (read/write fields only) from the database. (XML files exported using this type cannot be read by the ReadXml method.)
xet_NodesAsProperties	Export to XML all fields as properties from the database. (XML files exported using this type cannot be read by the ReadXml method.)
xet_ExportImportMode	Export to XML only valid fields that support XML import and export (read/write fields only that do not contain null values) from the database. (XML files exported using this type can be read by the ReadXml method.)

Connecting to the Company and Export XML

Next step will be that opening connection to the company database, export XML, return if there is errors, and close the connection. The steps would be:

1. Set Try-Catch-Finally (or Using) flow
2. Open connection to the company
3. Choose the journal entry number (found at “TransId” field at the OJDT table) that you want to export, using GetByKey method. In this example, I chose the number 116541
4. Export an XML file using “SaveXML” method
5. Return errors if any
6. Close the connection

Write the code below after the previous codes:

        Try

            ‘// Connects to the SAP Business One company database.

            vCmp.Connect()

            ‘// Call JournalEntries obj

            Dim oJE As SAPbobsCOM.JournalEntries

            ‘// BoObjectTypes Enumeration specifies the object types.

            oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

            ‘// GetByKey method retrieves and sets the values

            ‘// of the object’s properties by the object’s absolute key

            ‘// from the Company database.

            If oJE.GetByKey(116541) Then

                oJE.SaveXML(“C:\temp.xml”)

                MsgBox(“OK!”)

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            vCmp.Disconnect()

            vCmp = Nothing

End Try

Test the Code

Run the code and see if XML file is generated.  In this example, you should see the file under the C drive (C:\temp.xml)

Formatting XML File

Unfortunately, you cannot just importing the same XML file that you just exported.  You have to eliminate unnecessary data.  In this example, I will leave only mandatory data.

Make it for human

The exported XML looks so ugly as you see the screenshot. To format the XML file readable for human, you can do so from Visual Studio:  Go to Edit –> Advanced –> Format Document.

If for some reasons, you cannot use Visual Studio, there are unbelievably nice people are providing you free online services, such as:

• http://www.freeformatter.com/xml-formatter.html
• http://www.webtoolkitonline.com/xml-formatter.html
• http://xmltoolbox.appspot.com

Before: This does not look like for human…

After: Now the XML looks tidy!

Simplify the XML file

First of all, DI-API does not like the first part of XML, so you can delete this line.

<?xml version=“1.0“ encoding=“UTF-16“?>

The XML file is consist of two parts– header and lines.  The header starts from <JournalEntries> tag and lines start from <JournalEntries_Lines> tag.

In header part, leave only date tags (ReferenceDate, TaxDate, and DueDate) and delete everything else. In this example, I willleave just <Memo> tag to identify what was imported to check later.  So the header would look like this.

    <JournalEntries>

      <row>

        <ReferenceDate>20140717</ReferenceDate>

        <Memo>Imporrrrrt Test 🙂 </Memo>

        <TaxDate>20140717</TaxDate>

        <DueDate>20140717</DueDate>

      </row>

</JournalEntries>

In line part, leave only AccountCode, Debit, and Credit, and delete everything else. So the line would look like this.

    <JournalEntries_Lines>

      <row>

        <AccountCode>1005000</AccountCode>

        <Debit>777.000000</Debit>

        <Credit>0.000000</Credit>

      </row>

      <row>

        <AccountCode>1006999</AccountCode>

        <Debit>0.000000</Debit>

        <Credit>777.000000</Credit>

      </row>

    </JournalEntries_Lines>

Save the XML files

After all, the entire XML would look like below. Overwrite the XML file to the original.

<BOM>

  <BO>

    <AdmInfo>

      <Object>30</Object>

      <Version>2</Version>

    </AdmInfo>

    <JournalEntries>

      <row>

        <ReferenceDate>20140717</ReferenceDate>

        <Memo>Imporrrrrt Test 🙂 </Memo>

        <TaxDate>20140717</TaxDate>

        <DueDate>20140717</DueDate>

      </row>

    </JournalEntries>

    <JournalEntries_Lines>

      <row>

        <AccountCode>1005000</AccountCode>

        <Debit>777.000000</Debit>

        <Credit>0.000000</Credit>

      </row>

      <row>

        <AccountCode>1006999</AccountCode>

        <Debit>0.000000</Debit>

        <Credit>777.000000</Credit>

      </row>

    </JournalEntries_Lines>

  </BO>

</BOM>

Coding to Import XML

Company Object

This would be the same as exporting, but below is the entire code after the “btnExport_Click” method.

        ‘// Company object is the primary DI API object

        ‘// that represents a single SAP B1 company database.

        ‘// This object enables you to connect to the company database

        ‘// and to create business objects to use with the company database.

        Dim vCmp As New SAPbobsCOM.Company

        ‘// Set Company object properties

        vCmp.UseTrusted = True

        vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

        vCmp.Server = “xxxxxx“

        vCmp.LicenseServer = “xxxxxx:30000″

        vCmp.CompanyDB = “xxxxxx“

        vCmp.UserName = “xxxxxx“

        vCmp.Password = “xxxxxx“

Connecting to the Company and Import XML

Importing step will be pretty much the same as exporting, but less coding– opening connection to the company database, import the XML file, return if there is errors, and close the connection. The steps would be:

1. Set Try-Catch-Finally (or Using) flow
2. Open connection to the company
3. Set the XML file using “ReadXml” method and actually import the data using Add() function.
4. Return errors if any
5. Close the connection

Write the code below after the previous codes:

        Try

            ‘// Connects to the SAP Business One company database.

            vCmp.Connect()

            ‘// Call JournalEntries obj

            Dim oJE As SAPbobsCOM.JournalEntries

            ‘// BoObjectTypes Enumeration specifies the object types.

            oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

            ‘// Browses XML formatted data and enables to update the data.

            ‘

            ‘// Parameters

            ‘// XmlFileStr: Specifies the XML file name or the XML content string

            ‘//            depending on the value of the XMLAsString property.

            ‘// Index :  Specifies the number of the object that you want

            ‘//          to read from the XML data (starts from 0, default 0).

            oJE.Browser.ReadXml(“C:\temp.xml”, 0)

            ‘// The Add function adds a record to the object table

            ‘// in SAP B1 company database.

            ‘// If there is no errors while importing, the Add function returns 0

            If oJE.Add() = 0 Then MsgBox(“OK!”)

        Catch ex As Exception

            MsgBox(ex.Message)

        Finally

            ‘// Disconnects an active connection with the company database.

            vCmp.Disconnect()

            vCmp = Nothing

        End Try

Test the Code

Run the code and see if XML file is imported into Journal Entry.