- SAP Managed Tags:
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:
- Set Try-Catch-Finally (or Using) flow
- Open connection to the company
- 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
- Export an XML file using "SaveXML" method
- Return errors if any
- 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 :smile: </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 :smile: </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:
- Set Try-Catch-Finally (or Using) flow
- Open connection to the company
- Set the XML file using "ReadXml" method and actually import the data using Add() function.
- Return errors if any
- 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.
5 Comments
