1 2 Previous Next

lisa.mulchinock

18 Posts

The SAP Business One Integration component (B1ic) is available to download and use with SAP Business One 8.8 PL12.

 

The Installation Guide and Administrator Guide are availble in the Documenation folder of the B1ic package.  Janos has also created a great blog with some installation tips so you can check this out also: Tips for Installation of Cockpit in SAP B1 8.8 PL 12

 

Previously I published a troubleshooting article however for ease of update we have move the content to a 3 seperate Notes. It now means we can now update these notes regular with new information/scenarios.

Please review the following Notes:  

The notes cover issues that may occur during Installation and Using the Cockpit and hopefully it helps you out :-) 

 

https://websmp230.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1295117

In this blog i want to highlight some ways you can track who the current user logged into SAP Business One is. I will show you how to do this via FMS, the UI API (2 ways) and finally by using the SP_TransactionNotification stored procedure.

 

1. Via Formatted Search

If your business process requires you to display the current user, for example in a field on a SAP Business One form, then a FMS is a good way to achieve this.

 

image

 

You can write a simple query and assign it to a particular field and it will return the current logged in user for you:

     SELECT T0.U_NAME FROM OUSR T0 WHERE INTERNAL_K = $[USER]    

 

2. Reading the value from Main Module Screen via UI API

Via the UI you can read the value from the top of the Modules Main Menu.

 

image

 

You will need to read the caption value from the static text which has a UID of 8 on Form 169.

The code you need to use is and you can include it in the part of your code that most fits your business requirement:

     Dim oForm As SAPbouiCOM.Form
     Dim oStatic As SAPbouiCOM.StaticText
     Dim sLoggedInUser As String
     oForm = SBO_Application.Forms.GetForm(169, 0)
     oStatic = oForm.Items.Item("8").Specific
     sLoggedInUser = oStatic.Caption
     MsgBox("The current logged in User is " & sLoggedInUser)

 

image

 

3. Track user logins/logouts via UI API Events

To do this we first need to create a user defined table in SAP Business One - in my example it's called USER_DATA. We also define 3 user defined fields (UDFs).

  • UserCode records the current SAP Business One User
  • LoginTimeDetails records the date and time the user logged in
  • Direction records if the user logged in or logged out

 

image

 

In the code we will catch the application Company Changed event and then write the usercode, time and direction into the user defined table.

 

If EventType = SAPbouiCOM.BoAppEventTypes.aet_CompanyChanged Then

  Try

     oRS = oCompany.GetBusinessObject

     (SAPbobsCOM.BoObjectTypes.BoRecordset)

     oUserName = SBO_Application.Company.UserName

     oDate = DateTime.Now

     oRS.DoQuery("Select Max(Code) from [@USER_DATA]")

     oCurrentLogCode = oRS.Fields.Item(0).Value

     oCode = oCurrentLogCode

     oName = oCurrentLogCode

       If oCurrentLogCode = "" Then

         oCode = 1

         oName = 1

       Else

         oCode = oCode + 1

         oName = oName + 1

       End If

     oRS.DoQuery("INSERT INTO [@USER_DATA] (Code, Name,

     U_UserCode, U_LoginTimes, U_Direction) VALUES (" & oCode & "," &

     oName & ",'" & oCurrentUser & "','" & oDate & "','Logged Out')")

     oRS.DoQuery("INSERT INTO [@USER_DATA] (Code, Name,

     U_UserCode, U_LoginTimes, U_Direction) VALUES (" & oCode + 1 &  

     "," & oName + 1 & ",'" & oUserName & "','" & oDate & "','Logged In')")  Catch ex As Exception

     MsgBox(ex.Message.ToString())

  End Try

  oCurrentUser = oUserName

End If

 

image

 

4. Via the SP_TransactionNotification

If not already done create the same User Defined Table as described above in 3 or simply add the additonal user defined fields to the existing table. The UDFs are:

  • UserCode records the current SAP Business One User
  • LoginDate records the date the user logged in
  • LoginTime records the time the user logged in

 

image

 

We use the same principle in this SP_TransactionNotification as we used via the UI code above but we will record Date and Time in two different UDFs and we will also only log a new entry if Login Time is null or its more that 60 seconds since the same user logged in.

 

DECLARE @usercode NVARCHAR(8)

DECLARE @CurrentLoginDate DATETIME

DECLARE @CurrentLoginTime NVARCHAR(8)

DECLARE @LastLoginTime NVARCHAR (8)

DECLARE @Code NVARCHAR (8)

DECLARE @Name NVARCHAR (30)

 

 

IF @object_type = '12' and @transaction_type = 'U'

BEGIN

SET @usercode = (SELECT user_code FROM [OUSR] WHERE INTERNAL_K = @list_of_cols_val_tab_del)

SET @CurrentLoginDate = GETDATE()

SET @CurrentLoginTime = CONVERT(VARCHAR(8), GETDATE(), 108)

SET @LastLoginTime = (SELECT MAX(U_LoginTime) FROM [@USER_DATA] WHERE U_UserCode = @usercode)

 

IF (Select Max(Code) from [@USER_DATA]) IS NULL

BEGIN

SET @Code = 1SET @Name = 1

END

ELSE

BEGIN

SET @Code = (Select Max(Code) from [@USER_DATA]) + 1

SET @Name = (Select Max(Name) from [@USER_DATA]) + 1

END

 

IF DATEDIFF(s, @LastLoginTime, @CurrentLoginTime) > 60 OR @LastLoginTime IS NULL

BEGIN

INSERT INTO [@USER_DATA] (Code, Name, U_UserCode, U_LoginDate, U_LoginTime) VALUES (@Code, @Name, @usercode, @CurrentLoginDate, @CurrentLoginTime)

END

END

 

 

image

 

Please note in this example i have directly updated via SQL Update stataements a User defined Table. This is permitted as this is not a UDO User defined table.

Conversion Values (formly known as Transform Tables) are plain text files in which you can specify the value mapping rules. It allows you to import a value to SAP Business One according to the value in the legacy system. But it can be used for any value needed to be transformed.

Lets take the example where we want to import Items but in the file we have specified the ItemGroupName and not the ItemGroupCode. You can use this functionality as follows:

 

1. Create a new text file which should have only two columns. Each row has the source data and target data seperated by a comma...please ensure there are no spaces between the commas. Note for each transformation field you need a seperate text file.

Items,100
Raw Materials,101
Services,102
Finished products,103

 

2.  Import the file and on Step 5 click on the tab Mapping Rules select the Source Field you want to map to the Conversion Value. In our example we will select Conversion Values for ItemsGroupCode.

 

image

 

3. We will then create a new conversion/transformation rule by clicking on the Create icon.

 

 image

 

4. In File Name: select the text file you created in Step 1. Conversion Rule Name: provide a name for this rule. Click Save once this is done.

 

image

 

5. Select the Target Field you are creating this conversion rule for from the drop down list. When complete click on the Open icon.

 

image

 

6. To save or reload the mapping rules click on the Map Rule button. Click save and specify the Name and Description of this mapping rule. Finally click on the open icon to load it.

 

image

 

To copy Rules from one computer to another see Note 887761.

The DTW is SAP's migration tool that allows users to transfer data into SAP Business One. In this blog i would like to share with you the improvements we have done in DTW patches and the type of improvements we plan to make in future patches. Our focus is on improving functionality, supportability, useability as well as introducing some new features.

   

DTW PL27 and DTW PL28 (Released with B1 2007A SP1 PL6 and PL7 )

Remeber Logon information:

  • Previously when opening the DTW the logon window remembers the username and password information only.
  • From this patch the DTW logon window will remember all information from the last successful logon.

 Support for UDO (User Defined Object) 

  • DTW now supports adding/updating of UDOs of type Document and Master Data. The template that need to be filled can be generated from the Customize Template menu and being with UDO_.

 

DTW PL29 (Released with B1 2007A SP1 PL8)

New Re-import option:

  • Previously after importing/updating data into the DTW, if there was an error in the DTW the user needed to open the error file, fix the problematic records and start the wizard again in order to import again these records.
  • This is time consuming so in PL29 a Re-import button has been implemented to enable the user to re-import the file without going again through each step of the wizard.
  • If an import/update results in errors then you can simply fix the error in the error file directly. Once complete click on the Reimport button from the Detailed Log window.

 DI Help File and DB Reference:

  • Since the DTW is based on the SDK DI partners need to use the DI online help reference and DB table reference when preparing the templates or troubleshooting issues. The DI API Reference and Database Tables Reference is now included under the DTW Help menu within the tool or via the DTW destination folder.

 

DTW PL30 (Released with B1 2007A SP1 PL9)

Simplify Object Structure:

  • The object structure is now based on Database(DB) Schema rather than on the SDK DI API names. This structure is more familar as users identify with the DB names rather than the DI object names. This new tree structure is reflected in the wiard and the Template folders.  Each template name also starts with the DB table name prefix e.g. CRD1 - Business Partners Addresses.xls.
  • The new object structure is also reflected in the DTW wizard and the Maintain Interface (now named Customise Template). The objects are displayed in an expanded tree rather than a flat list of object names.

Wizard redesign

  • The DTW wizard has also been redesigned to improve useability - it is now simplified, more user friendly, intuitive and self explanatory.
  • The following are the steps in the new wizard:
  1. Select Data Type - this new step helps to better filter the list of  objects (Master Data, Setup, Transactions).
  2. Specify Operation Type - another new step to define whether you are adding a new record or updating an existing record.
  3. Select a Business Object - this simplified object structure displays objects in a hierarchial tree similar to the SAP Business One menu structure.
  4. Select Data Source - you can remove an object from the list.
  5. Map Object Fields - only selected objects displayed.
  6. Define Error Handling - new functionality to simulate import and better explanations of the rollback options.
  7. Start Importing Data.

 Support for New DI objects

  • The DI API 2007A SP1 PL9 supports the following new objects and they are also supported in the latest DTW Patch. They are Business Partner Properties and Discount Groups.

 

DTW PL31 (Released with B1 2007A SP1 PL11)

Improved template structure:

  • The DTW templates now contain two rows - row 1 corresponds to the DI properites and row 2 to the database properties so it makes fields easier to identify.
  • Each field also contains an enhanced and meaningful tooltip with information such as Type, Valid Values, Description, Mandatory, Related Tables etc.
  • This tooltip will also be included if templates are generated via the Customize Template menu.

Removal of RecordKey column:

  • Previously, when users wanted to update a child object in DTW, they had to select also the parent object template.
  • To simplify the process you are now able to update a child object without selecting parent template.
  • This is achieved by removing the RecordKey field from all templates.
  • Instead the parent key field exists in the child templates.
  • In Step 4 of the DTW wizard (when selecting templates for objects), it is now possible to select a child object for update without selecting also the parent object.

Field Mapping:

  • In the ‘Mapping Rules’ tab, the user now has an option to decide whether to show all fields (in the list of all target fields in the table) or only unmapped fields (i.e. target fields column will contain only unmapped fields) – this reduces the risk of mapping a field twice
  • This new checkbox is unchecked by default.

Reload Source File:

  • The user is now able to reload the source file and see the recent updates (if any) reflected in the wizard.
  • To do this there is a new refresh icon in the ‘Source’ tab that will enable to refresh the source file.

Improved Usability

  • Customized template menus contain functionality to show field properties, display only the fields that can be added to the template etc.

Support for DI Objects:

  • The DTW now supports the following SDK objects and properties:
  1. Inventory Transfer Drafts
  2. Hierarchies and Expansions

 

Of course, all these new features described for Version 2007 are also copied to the the DTW in Version 8.8.

Please note all new features from now on will be implemented only in the DTW Version 8.8.

 

DTW 8.8 PL14

Detailed Error Description:

  •  It is now possible to display a detailed error description in the error log window of the DTW.
  • The user will be able to open the error link from the Error Log window and the DTW help file with the relevant error message will open.

 

DTW 8.8 PL18

Create templates for UDOs and UDFs

  •  It is now possible to create templates for UDOs and UDFs that include description, type and size.

 

DTW 8.8 PL19

Support ODBC Scheduled Run

  • Before this patch it was possible to use Scheduled Run only with data from flat files only (e.g. .txt, .csv).
  • From this patch Scheduled Run via ODBC is now supported.

 

We are constantly looking for ways to update and improve the DTW tool. If you have any suggestions on how the tool can be improved please feel free to send me your suggestion.

Happy New Year everyone!

In the third blog in this blog series I will show you how to use DataSources in your UI AddOn. DataSources allow you to work with data in UI forms and populate your forms with data. All items in your add-on forms (except it_STATIC and it_BUTTON items) must be bound to data sources in order to hold and manage data. As you know the UI API supports two types of data sources:

 

  • Database Data Source - allows you to manage data from a table in the SAP Business One database.

  • User Data Source - allows you to manage data defined internally for your add-on.

 

How to use DataSources 

The steps to be followed for Data Binding are:

  1. Add the Data Source
  2. Set the Data Binding to specific item(s)
  3. Get Data from the DataSource

1. Add the Data Source

DBDataSource

oForm.DataSources.DBDataSources.Add ("OINV")   

Here the table associated with the DBDataSource is the Invoices table OINV. You can control the records returned to the data source by creating a Conditions object and passing it with the Query method. For example returning only Invoices where the DocDueDate falls between June 1st and Dec 31st.

UserDataSource

The difference between DBDataSource and UserDataSource is that the UserDataSource holds one value at any time assigned to it by a user.

oForm.DataSources.UserDataSources.Add("UserData", SAPbouiCOM.BoDataType.dt_LONG_TEXT, 30)    

 

2. Bind the Data

After adding the data source, you bind it to your item using the method SetBound.

DBDataSource

oMatrix = oForm.Item.Item("Matrix").Specific

oColumns = oMatrix.Columns

oColumn = oCoumns.Item("CardCode")

Bind the data to the respective column

oColumn.DataBind.SetBound(True, "OINV", "CardCode")

UserDataSource

Bind a UserDataSource to a column - table name will be empty

oEdit = oForm.Items.Item("Remarks")

oEdit.DataBind.SetBound(True, "", "UserData")

 

3. Get data from DataSources

DataSource

Query the datasources already defined above. You cannot run this method on a DBDataSource bounded to a system forms. The system will throw an exception.

oDBDataSource.Query()

You still need to control adding data to the database by using the object/service of the DI API.

UserDataSource

Returns or sets a value in the data source in database format only. You can use Get

oUserDataSource.Value = "Tested by Lisa"

oMatrix.LoadFromDataSource()

 

While having matrix columns bound to a UserDataSource, it is more efficient to update the matrix cells using GetLineData and SetLineData methods (of the matrix object).                                           
Use SetLineData for taking the data from the matrix into the DataSource.  
Use GetLineData for loading data from your DataSource to the matrix.   

It's important to note that updating the DataSource value is valid only currently for UserDataSources.   

 

Set oMatrix = oForm.Items.Item("Matrix").Specific                
Set oColumn = oMatrix.Columns.Item("MyCol")                      
Set UDS = oForm.DataSources.UserDataSources.Item("UserData")    
oMatrix.GetLineData (LineNum)                                     
UDS.Value = "My New Value"                                        
oMatrix.SetLineData (LineNum)
 

                                    

In two weeks time, we will continue with the UI API for Dummies blog series. So catch you then :-)

In the second in this blog series I will show you how to make small changes via the UI API focusing on items: 

 

1. Disable the Accounting tab in a Sales Order

Sometimes it might be necessary to prevent certain users from accessing information in a certain form. While this can't be controlled through the SAP Business One authorisations, it can be easily achieved via the UI API. The first option is to disable a tab - in this example we are disabling a tab/folder on the Business Partner form in Add, Find, OK and Update modes. It is important that you control this for the modes you wish to have it disabled. The following code should be copied into the ItemEvent:

 

If pVal.FormType = "139" Then

If pVal.FormMode = 0 Or pVal.FormMode = 1 Or pVal.FormMode = 2 Or    pVal.FormMode = 3 Then

  oform = SBO_Application.Forms.GetForm("139", 0)

  oitem = oform.Items.Item("138")

  oitem.Enabled = False

End If

End If

 

2. Make the Item Cost field on the Stock Data tab invisible

The ItemCost field is relevant only if the Valuation Method is set to Standard. In this example we do not want users to see this field so we will make both the textbox and it's associated label invisible. The following code should be copied into the ItemEvent:

 

If pVal.FormTypeEx = "150" And pVal.EventType = SAPbouiCOM.BoEventTypes.et_GOT_FOCUS And pVal.FormMode = 3 Then

  oform = SBO_Application.Forms.GetForm("150", 0)

  oFolder = oform.Items.Item("26").Specific 'Stock Data tab

  oFolder.Select() 'click on Stock Data tab

End If

 

If pVal.FormTypeEx = "150" And pVal.ItemUID = "26" And pVal.EventType = SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED And pVal.BeforeAction = False And pVal.ActionSuccess = True Then

  oform = SBO_Application.Froms.GetForm("150", 0)

  oform.Freeze(True) 'freeze to prevent flickering

  oitem = oform.Items.Item("59") 'label

  oitem.Visible = False

  oitem = oform.Items.Item("64") 'textbox

  oitem.Visible = False

  oform.Freeze(False) 'unfreeze once done

End If

 

3. Add a new item on a System Form

When adding a new item on a system form its best to use an existing item on the form to position the new item. For example if we wish to add a new button on a Goods Receipt PO i will use the Cancel button as a marker to create the new one. The properties height, width and top will be the same as the exiting item but the left property needs to be increased otherwise the new button will be created over the Cancel button! The following code should be copied into the ItemEvent:

 

If pVal.FormTypeEx = "143" And pVal.EventType = SAPbouiCOM.BoEventTypes.et_FORM_LOAD And pVal.BeforeAction = False Then

    oform = SBO_Application.Forms.GetForm("143", 0)

    oitem = oform.Items.Item("2") 'Cancel button

    oNewItem = oform.Items.Add("NewButton",   SAPbouiCOM.BoFormItemTypes.it_BUTTON)

    oNewItem.Height = oitem.Height 'same height

    oNewItem.Width = oitem.Width 'same width

    oNewItem.Top = oitem.Top 'same top

    oNewItem.Left = oitem.Left + 70 'add to the left so its positioned next to Cancel

    oButton = oNewItem.Specific

    oButton.Caption = "New Button"

End If

 

4. Automatically open a particular document

Sometimes you may need to open a certain form and do some automatic selections. For example after adding an Invoice automatically open an Incoming Payment for a certain BP and select the Invoice just added from the matrix.

 

Firstly we catch the event when the Invoice is added

Private Sub SBO_Application_FormDataEvent(ByRef BusinessObjectInfo As SAPbouiCOM.BusinessObjectInfo, ByRef BubbleEvent As Boolean) Handles SBO_Application.FormDataEvent

    If BusinessObjectInfo.FormTypeEx = "133" And BusinessObjectInfo.EventType =   SAPbouiCOM.BoEventTypes.et_FORM_DATA_ADD And BusinessObjectInfo.BeforeAction = False And BusinessObjectInfo.ActionSuccess = True Then

    SBO_Application.ActivateMenuItem("2817")

End If

End Sub

 

Then we add the following code to the ItemEvent to fill CardCode and select the newly added Invoice in the list.

 

If pVal.FormTypeEx = "170" And pVal.EventType = SAPbouiCOM.BoEventTypes.et_FORM_LOAD Then

    oform = SBO_Application.Forms.GetForm("170", 0)

    oedit = oform.Items.Item("5").Specific 'CardCode

    oedit.Value = "C20000"

    oMatrix = oform.Items.Item("20").Specific 'matrix

    vRowCount = oMatrix.RowCount 'count rows

    For i = 1 To vRowCount

        oedit = oMatrix.Columns.Item("1").Cells.Item(i).Specific

        If oedit.Value = 14 Then

         oCheckBox = oMatrix.Columns.Item("10000127").Cells.Item(i).Specific

         oCheckBox.Checked = True

        End If

    Next i

End If

 

That's my last blog for 2009 so wishing you a lovely Christmas and every success in the New Year. I'll be back in 2010 with some more blogs that will hopefully be of use to you. So catch you then :-)

In the first in this blog series I will show you how to make small changes via the UI API focusing on menu related activities:

 

Activate a Menu Item

It's very easy to call/activate a menu from SAP Business One. Only one line of code is required:

     SBO_Application.ActivateMenuItem("2561")

To find the string id required for each menu you can do the following:

  1. From the SAP Business One toolbar ensure View -> System Information is activated. When you hover over each menu in the toolbar you will find the associated menu id
  2. In the SDK Help File click on UI API -> Creating Menus -> List of Menu Item IDs for a full list of all menus

 

Change a Menu caption

To change the menu caption we need to access the string value of the menu and give it a new name. In this example i have changed the 'Human Resources' menu to 'HR'. Again we need to access the Menu ID as we did in the previous example.

     Dim oMenus As SAPbouiCOM.Menus

     oMenus = SBO_Application.Menus

     oMenus.Item("43544").String = "HR"

 

Removing a Menu

You can remove a menu in Business One itself via the Form Settings for the Main Menu (using Visible property) but if you need to remove a menu via the UI it can be easily done. In our example we are removing the 'Document Printing' menu from the Sales module.

Dim oMenus As SAPbouiCOM.Menus

oMenus = SBO_Application.Menus

oMenus.RemoveEx("2058")

 

Adding an option to the Right Click Menu

The right click menu allows you to increase the usability of your addon. In this example i added a new entry to the Right Click menu called 'Save as Draft'. I capture the click on the new menu event and activate the Save as Draft menu from the File menu in the toolbar. You can also do this for other menus also e.g. What's This? help menu for an explanation of each field.

 

Right Click Event

Private Sub SBO_Application_RightClickEvent(ByRef eventInfo As SAPbouiCOM.ContextMenuInfo, ByRef BubbleEvent As Boolean) Handles SBO_Application.RightClickEvent

  If eventInfo.FormUID = "F_92" Then

      If (eventInfo.BeforeAction = True) Then

          Dim oMenuItem As SAPbouiCOM.MenuItem

          Dim oMenus As SAPbouiCOM.Menus

          Dim oCreationPackage As SAPbouiCOM.MenuCreationParams

          Try

              Dim oCreationPackage As SAPbouiCOM.MenuCreationParams

              oCreationPackage = SBO_Application.CreateObject (SAPbouiCOM.BoCreatableObjectType.cot_MenuCreationParams)

              oCreationPackage.Type = SAPbouiCOM.BoMenuType.mt_STRING

              oCreationPackage.UniqueID = "Draft"

              oCreationPackage.String = "Save as Draft"

              oCreationPackage.Enabled = True

              oMenuItem = SBO_Application.Menus.Item("1280") 'Data'

              oMenus = oMenuItem.SubMenus

              oMenus.AddEx(oCreationPackage)

          Catch ex As Exception

              MessageBox.Show(ex.Message)

          End Try

     End If

  End If

End Sub

 

Menu Event

Private Sub SBO_Application_MenuEvent(ByRef pVal As SAPbouiCOM.MenuEvent, ByRef BubbleEvent As Boolean) Handles SBO_Application.MenuEvent

  If pVal.MenuUID = "Draft" And pVal.BeforeAction = False Then

     SBO_Application.ActivateMenuItem("5907")

  End If

End Sub

 

In two weeks time, we will continue with the UI API for Dummies blog series. So catch you then :-)

ODBC is a great alternative to import data into SAP Business One. The ODBC allows you to store your client's data in a database table and then you can use ODBC to fill the relevant SAP Business One tables without the need to use the DTW templates

1. Configure a new ODBC data source:

1. Open 'ODBC Data Source Administration' via Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC)

2. Select the 'User DSN' tab and click on the Add Button 

3. In the 'Create New Data Source' window choose 'SQL Server' from the drop down list and click Finish

4. Provide a Name for this DS (Data Source) and the Server name you want to connect to

5. Click Next and select database authentication

6. Click Next and select the checkbox 'Change the default database to' and provide the correct database name you will connect to via the DTW

7. Click Finish

8. When the Setup Window appears with a summary click 'Test Data Source' to ensure the connection succeeds

2. DTW Steps

In Step 2 of the Data Transfer Workbench Wizard select 'ODBC' from the Source Data Type combo box. This will then open up the 'Extract by ODBC' window.
                                                         

image

 

  • DSN - the name of the ODBC data source set up in Step 1
  • User ID - the DBUser used in the data source which is normally 'sa'
  • PassWord - the 'sa' password
  • Simple Select Statement - the SQL statement to extract the database from the database table
  • If the data files contain both header and item information, you must define the primary keys - that is why in our example we set CardCode as RecordKey.
  • If the data file contains only header files you do not need to define the primary key. oChartOfAccounts is an example of this object.                                                                     
  • The maximum data length is set to 10000. Select the "Maximum Data Length Is Larger Than 10000" checkbox and enter the maximum          
    length in the textbox if you have a lot of text in a certain field e.g Remarks.                                                      

image

 

Continue with each step of the DTW wizard as normal to import or update the data.

 

3. Additional Information

Currently DTW does not support importing ODBC via a Scheduled Run.

For each database you wish to connect to you need to set up a seperate ODBC data source.

In this blog we will demonstrate how to send an email with an attachment using both the Message Object and Message Service. Attachments are represented in the SAP Business One DI API from version 2005 via the Attachments2 object. This enables the user to copy files from a source folder to the Attachments folder.

Ensure first that you define the attachments folder in Administration -> System Initialisation -> General Settings. If this is not done you will receive an error such as "Source Files does not exist".

The Message Object is the 'old' way of utilising the functionality. To add the attachment to the email we call the Attachments subobject of the Messages object and provide the location of the file name.

 

image

 

As you know SAP recommends that partners use Services in add-ons. All new functionality will in future be released as a service and not an object. To send an email using Message Services we first need to add the attachment.

 

image

 

In the path you defined in Administration->System Initialization->General 
Setting->Path, every attachment will first be copied to this path. If there is already a file with the same name of the one you are attempting to attach the SAP Business One DI API will try to copy it first to the default path and will then return an error message that the file name exists.       

Once the attachment is added we need to link the attachment/document to the email by setting the AttachmentEntry value. This corresponds to the AbsoluteEntry of the newly added attachment and in our example this is 2. This is stored in the the table ATC1.

 

image

In SAP Business One Global Support we are always looking for ways to share information and empower our partners. We tend to get a lot of similar queries/how to questions and so decided to start a regular blog series to share this information on the SDN which is a hive of activity.

Every two weeks in our support blog we will highlight some commonly asked questions and hopefully help you understand each topic a little better. The focus for now will be on the SDK and DTW areas. All our blogs are published using an image so it will be easy to identify our blogs:

image

 

Current Blogs:

We have a number of interesting and popular blogs already published and can be found in our Blog Archive http://www.sdn.sap.com/irj/sdn/businessone?rid=/webcontent/uuid/70be9afd-2320-2c10-aa87-b40c72e4c9c4

A summary of them are:

  • SDK Licensing - Learn about add-on licensing which can be confusing. Read about the different SDK license types and what they are used for. The blog will also go through the process of licensing your own Add-ons. SDK Licensing
  • SAP Business One License API - The SAP Business One License API is an external API that allows you to read information from the license service to which the application is connected.This blog explains what the SAP Business One License API is and how you can use it in your Add-on. SAP Business One License API
  • The SP_TransactionNotification Stored Procedure - This is a really useful and easy way to do data validation in Business One. In the blog I describe what it is and how you can implement it in your business processes The SP_TransactionNotification Stored Procedure
  • How to create Folders/Tabs via the UI - Adding a folder/tab to your own user form or to a system form is a good way to add functionality to your add-on. We go through some examples of how to add and manipulate folder/tabs.                                      How to create new folders/tabs via the UI API
  • How to solve errors you get when connecting to the DI API/DTW - When you connect to the DI you may get errors such as "-111 Connection to SBO-Common failed", "100000001: Connection to License Server failed", etc. In this blog we show you the checks you can do to find the root cause of the issue and solve it yourself.                                How to solve errors you get when connecting to the DI API/DTW.
  • How to use Macros to import/update data for DI Objects not yet exposed via the DTW e.g. UDO Tables - we will show you how to utilise macros to allow you to import data into your Business One database. How to use macros to import/update data for DI objects not yet exposed via the DTW e.g. UDO tables.
  • How to help find a possible source of a DI Performance Issue - In this blog we highlight some steps and some tools you can utilise to help you identify the performance issue.                           How to help find a possible source of a DI Performance Issue
  • DTW Top Tips - to ensure your DTW import or update is done as smooth as possible we will highlight some tips and checks you can do. DTW Top Tips

 

Future Blogs: 

We plan to continue to publish many more blogs in the future and to keep them as relevant and useful as possible. Planned topics include:

  • Registering your addon
  • Active X
  • How to send an email with an attachment via the DI
  • DTW ODBC

 

Blog Suggestions:

We would be delighted if you could provide any feedback, ideas or suggestions you have for topics you would like us to cover in a blog. All suggestions would be very welcome and we would try to cover as many of them as possible.

Lisa Mulchinock

DTW Top Tips

Posted by Lisa Mulchinock Aug 25, 2009

In this blog we will highlight some tips on how to use the DTW so you can ensure your import/update runs as smoothly as possible.

DI API Help File

The DI API Help File is a vital resource to use when using the DTW along with the comments in the data templates delivered with the DTW. The DI API Reference lists all exposed objects with methods and properties. For each object you can find information on the Detailed Description, Field name & length, Property type and Syntax. 

Example: Import Manual Journal Entries

  • Go to Index Tab and enter "Jou.."
  • Double click "JournalEntries Object" to display the object's details
  • Review the details of all the members including Child objects

image

 

  • View details of property AutoVat.
  • The valid value to enter in the DTW import file must be a member of "BoYesNoEnum" enumeration
  • Enter tNO or tYES in the data import file

image

So for each field if you are unsure which value (especially enumeration fields) should go into the DTW files it is important to verify it in the DI API help file. 

LineNum

If you are adding new records or updating a record to add additional information (e.g. add new Warehouse or add new contact persons) you need to leave LineNum blank. LineNum column is used only during Update mode.

LineNum must be an integer so if you want to update the first line/record you assign the value 0 to the LineNum field in the data file. If you wish to update the second line/record, you assign the value 1 to LineNum and so on. It is important to never remove this column even if no values are entered.

Example:
1. The SAP Business One database has 13 price lists defined in the system
2. You want to import an item for which you define prices for the first, the second and the eighth price list displayed in the Item Master Data.
3. Set the data file Items_Prices as follows:
RecordKey,LineNum,Currency,Price
RecordKey,LineNum,Currency,Price
1,0,USD,12
1,1,USD,24
1,7,USD,28

To import new data to an existing record you need to select the checkbox "Add all Items into Schema if can't find corresponding item when updating".

Object Names

In general, the names of objects in the DTW and SAP Business One are similar. The exceptions are as follows:

Object in DTWObject in Business One
oInventoryGenEntryGoods Receipt
oInventoryGenExitGoods Issue
BoBridgeExchange Rates
oAlternateCatNumBusiness Partner Catalog Numbers
oContactsActivity
oAttachments2Attachments linked to objects such as Messages, Activites,  Service Contracts 
oProductTrees Bill of Materials

Template Structure

Templates explained

The first row consists of the properties from the DI API. We recommend that the first line in the template is not changed as this might cause incorrect mapping. This can result in data not being imported and no error messages returned. The second row consists of the explanations of the properties. This line can be changed for a better explanation of the property. Do not use special characters or empty spaces.

Un-used columns

We strongly suggest that columns are not deleted from the templates. If a value is not required to be added or updated leave the column blank to avoid problems. Deleting columns can cause records not being imported or unexplained error messages.

RecordKey

This key identifies the header record from other records. To improve the performance, RecordKey values are sorted in order during the import. The same RecordKey for one object must be entered in both templates for the header and line templates. 

Special Characters in the template

Special characters such as double byte characters, commas, double quotation marks etc can prevent correct mapping. Microsoft excel generates a .csv/.txt file in non-unicode encoding or seperates the data in additional columns which cannot be recognised by the DTW. Its good to open a file in plain text to see if you can see any incorrect characters.

Other Tips

  • Date format is yyyymmdd
  • When using segmented Chart of Accounts the FormatCode field must be filled e.g. _SYS00000000010. For non-segmented Chart of Accounts use the Code field.
  • If you want to import a Journal Entry for a BP use ShortName (fill it with the BPCode) and leave AccountCode blank
  • Not all objects/properties are exposed in the DI - therefore they are not available also via the DTW e.g. User defined fields for batches, project codes etc. It's important to verify in the DI API Help file if the object/property is exposed.

Our next blog will cover the topic of how to send an Email with an attachment via the DI API. So catch you then :-)

Performance issues can occur with DI Objects and symptoms include Long Processing Time or an eventual crash. In this blog we will highlight some steps that can be taken and tools you can utilise to help you identify the performance issue.

1. Test without the Add-on running

While it seems like a simple thing to do testing the same functionality in Business One is an important step. While the performance issue might occur in your add-on it may also occur in Business One pointing to the fact that it is a core performance issue and not an DI performance issue.

2. Check if there is a memory leak

The Task Manager is the easiest way to detect if there is a memory leak.   

a. Create a small sample to replicate the scenario.

b. Open the Task Manager and choose the Performance tab.

c. Run your sample and monitor the CPU Usage and the Page File Usage in the Task Manager.

 

When starting the Add-on:

  • CPU usage starts at 0 and will rise to 100%
  • Page File Usage starts low and gradually increases. Under normal circumstances it will grow but will be released when the Add-on finishes.
image

 

When ending the Add-on:

  • CPU Usage peaks to 100% regularly
  • Page File Usage remains very high and is not released = this indicates a memory leak.

image

  

3. Check if there is a problem with a query

Another cause for a DI Performance issue might be that a query triggered by the Add-on takes quite a while to run. We can use the SQL Profiler to verify if this is the cause.

a. Create a small sample to replicate the scenario.

b. Open the SQL profiler (Start -> All Programs -> Microsoft SQL Server 2005 -> Performance Tools -> SQL Server Profiler).

c. Select File -> New Trace and connect to the SQL Server if prompted. Provide the Trace file a Trace name.

d. Click on the Events Selection tab. Select the checkbox "Show all columns".

e. Click Column Filters and the Edit Filter window will open. In this window you can assign the DatabaseName, LoginName or other filters to narrow down the results returned.

f. Just before running your Add-on click Run and then run your Add-on.

g. Save your trace file.

image

 

Open the trace file and the three columns to analyse are:

  • ApplicationName (application calling the queries)
  • LoginName (database user you are logged on with)
  • Duration (time it takes for the query to run (milliseconds)

The parameters we will look at include the duration of each query - 105020 is about 1.75 mins and would be indicative of an issue. The number of SQL queries generated can also indicate an issue - if an unacceptable number of queries are generated eg 80000 then it may be the reason.

 

4. SAP Business One Test Tools

The SAP Business One Test Environment is a set of software tools that help profiling the execution of a solution. They are available to download on the SDN and you can download the latest version of the B1TE from http://www.sdn.sap.com/irj/sdn/businessone?rid=/webcontent/uuid/cbb70163-0c01-0010-9b8c-f6ee72de7328

DI Logs Reader

Starting on SAP Business One 2007A version the DI API is able to generate log files tracing all calls made from an add-on to the DI API. The B1DILogsReader is then able to open the DI API logs files and show the information in a very easy way to read. Some of the information recorded is:

  • Time of the call
  • Elapsed time the DI API needed to return the answer back
  • Interface ID giving the reference of the interface instance
  • Interface name of the class being called
  • Method or Property called in the interface, preceded by Set or Get if setting/getting properties
.Net Profiler

This tool keeps track of all the DI API and UI API methods called from a .NET add-on. The tool produces information about the time spent, the exception raised, and possible compatibility problems.

For each call, the following information is collected:

  • Time the function call was executed
  • The Name of the process executing the profiled function call
  • The Win32 Thread Id of the thread executing the profiled function call
  • Whether the call belongs to DI API or UI API
  • Name of the class to which the method belongs
  • Name of the called method
  • For UI API or DI API method calls, the time elapsed to complete the call

If you do suspect there is a memory leak in the DI we would recommend that you log a support message providing the exact scenario, the results and a small sample to reproduce and verify the issue.

 

Our next blog will cover some DTW Top tips so catch you then :-)

1. Prepare the Excel Files 

In SAP Business One i have created a user defined table of type Master Data called MACROUDO. I have created two user defined fields for this table UDF1 and UDF2.

Firstly create two new tabs in a new Microsoft excel file - tab 1 will contain the database connection details for the SAP Business One database we will connect to via the DI API and tab 2 will contain all the UDO information we want to import into the UDO table we have defined in Business One.

image

image

A. Display the excel Forms menu by clicking View -> Toolbars -> Forms

B. Create a new button on the UDO tab by dragging and dropping the button icon onto the excel file.

C. When the Assign Macro window appears change the name of the button function (this is the name you'll use in the code)

D. You can change the caption of the button by right clicking on the Button, highlighting the text and replacing it

image

E. We need to open the Visual Basic Editor. To do this click on Tools -> Macro -> Visual Basic Editor.

F. Create a new Module where we will write our code. To do this right click on Microsoft Excel Objects -> Insert -> Module. You can rename Module1 if you wish.

image

G. Add the SAP Buiness One DI API Version reference via Tools -> References. We need this dll so we can connect and access the SAP Business One database.

image

2. How to connect to the DI API

Create a new procedure called Public Sub login() which will read in the values set in the Login tab of the excel sheet and connect to the DI API with these values. If all is successful you will receive a successful error message "Connected to: db name"

image

3. Filling the UDO table 

Now that we are successfully connected to the DI, we need to add the values to our UDO table by reading them in from the excel file. We have set the Range A6 to A11 as i have values only in these fields. So if you have more/less rows this range will need to be changed.

image

4. How to run the program

We need to trigger this code in order for the import to start. So in the button click event we place the following code which will call the login method, call the import_UDO method to fill the UDO table. It will finally display a message to confirm the import is complete.

image

To run the code, simply click on the new button on your excel sheet and viola! I have also attached the excel file so you can download and test it:

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/b0b25168-e25b-2c10-eab7-855eec5517d0   

The UDO functionality is planned to be included in the next DTW patch - DTW 2005.0.27 which is planned to be released with SAP Business One 2007A SP1 PL06 (end of July).

 

Our next blog will cover the topic of DI Performance and how to help find a possible source of a DI Performance issue. So catch you then :-)

Connection Parameters: 

Although it may sound unnecessary, the first check is ensure you have the correct parameters set in your connection method.

  • Server: Test using IP address as well as Server name. Also include the Instance name if used. When installing the SAP Business One server on Version 8.8, you can no longer enter (local) or a period (.) for the server name. The name must be a server name or IP address.

  • DBType: dst_MSSQL /dst_MSSQL2005 depending if customer is using MSSQL 2000 or MSSQL 2005. Up until Version 2007, the default value for the DBServerType property of the Company object was dst_MSSQL (SQL Server 2000), so you could connect to the DI API without specifying a server type if you were using SQL Server 2000. In Version 8.8, you must specify a server type.

  • LicenseServer: ensure it is started and the correct name with port is used e.g. localhost:30000 or TestPC:30000

  • DBUserName and DBPassword are set and are correct.  

 DI Connection

image

 DTW Connection:
image

SM_OBS_DLL folder is not updated:

This is the most common solution to errors that occur when connecting to the DI/DTW. When connecting to the DI API the following occurs:

  • The SAPbobsCOM connects to the server and requests the OBServer.dll (stored in the SBO-Common database)
  • The OBServer.dll is loaded from the server into process memory and stored in the temp folder on the local system
  • Via the OBServer.dll it accesses the company database

This means the OBServer.dll is downloaded only during the first connection and uses this each time it wants to connect there after. When you upgrade your DI and there has been a database upgrade then the new version of the OBServer.dll will be downloaded automatically to the SM_OBS_DLL folder in the temp folder. If there has only been a client upgrade then the SBO-Common version is unchanged so the OBServer.dll will not be replaced since it doesn't detect the change.

Therefore after each upgrade we recommend that you do the following:

  1. Go to Start -> Run and type %temp%
  2. Locate the folder SM_OBS_DLL
  3. Delete or rename this folder

Version mismatch: 

Ensure the SBO-Common version, the SAP Business One company database version and the DI version are ok.

  • Common version- stored in the SINF table of the SBO-COMMON database

image

 

  • Database version - stored in the CINF table in the company database
image

 

  • DI Version - OBServer.dll -> Properties -> Special Build Description

image

 

While these are common checks you can do, the root cause could be due to a number of other reasons. For more detailed root cause analysis for DI Connection errors I would highly recommend that you visit the SDK Landing Page on the Portal https://sapneth1.wdf.sap.corp/~sapidb/011000358700000070422009E.

You can find really useful RCA(Root Cause Analysis) for many other topics like:

 AR/AP

Documents don’t appear in Dunning Wizard

 Performance

UDF System Freeze

 

Slow Login to SAP Business One

 

Slow Performance in SAP Business One when using Windows Vista

 

Slow Performance in SAP Business One when using Choose From List functionality

 

Application Shutdown on Initial Logging in

 

SQL native client driver not installed for SAP Business One client

 

SAP Business One shuts down when printing a document containing serial numbers

 System

Upgrade Issues

 

Licence & SAP Service Marketplace issues

 

Alerts Management

 

SAP Business One Server Tool: SBO Mailer

 

SAP Business One Backup Issues 

 

SAP Business One and Citrix

 

SAP Business One Add-on Copy Express issues

 

SAP Business One Add-on Outlook Integration issues

 

SAP Business One Add-on XL Reporter issues 

 DTW

Issues during login to DTW

 

Issues when importing or updating data via the DTW 

 Finance

Profit Centers & Distribution Rules 

 

Budget Issues 

 

Rounding Issues 

 

Cannot reconcile Business Partner Transactions 

 

Tax Report Deviates 

 

Payment Wizard Issues 

 

Sales Report Issues 

 

Profit and Loss Statement doesn’t match with Balance Sheet

 

Unbalanced Trial Balance 

 

Bank Statement Processing 

Financial Add-ons

Fixed Assets Issues

 

Payment Engine Issues  

Cross Topics

ALD Issues 

 

PLD Issues  

 

Check Print Issues  

 Logistics

Serial/Batch Issues

 

Incorrect Committed/Ordered Quantity 

         

To find these go to http://service.sap.com/ -> SAP Channel Partner Portal -> Login -> Solutions -> SAP Business One -> Support -> Support Self Help and Library. It can also be accessed via the following link:

http://service.sap.com/~form/sapnet?_SHORTKEY=01100035870000707558&_SCENARIO=01100035870000000183&_ADDINC=&_OBJECT=011000358700000622642008e

Our next blog will cover the topic of macros and how to use these to import/update data for obejcts exposed by the DI but not via the DTW. So catch you then :-)

We will go through a number of small samples in this blog to provide you with the information you need to create your own folders.

Example 1: Adding a new folder to a Sales Order Form

1. Connect to the UI (or use Single Sign On)

2. Open the ItemEvent and add the following code which will:

a. Access the Sales Order form

b. Catch the form load event of the Sales Order

c. Add a folder using the Item Type it_FOLDER. In this case we have created the new folder based on an existing folder (Accounting) so we copy the values top, height, width and left. We also put it next to the Accounting tab by using the GroupWith property.

d. When the form loads it then sets the focus back to the first folder (Pane level 1) which is the Contents tab.

3. Add another event in the Item event that will put the focus on the new tab when it's clicked on.

 

image

image

In Business One this is how your new folder will appear in the Sales Order:

image

Example 2: Adding an item to the new folder on the Sales Order Form

In the above sample we can see that when we create the new empty folder we call a method called AddItemsToOrderForm which is the method we use in this example to add the items to our new folder:

1. Decide what type of items you want to add to the new folder

2. Use an existing item on an existing folder to place the new items on the new folder

3. Most importantly assign the ToPane and FromPane and this will determine on what folders these items are visible on.

 

image

In Business One this is how the new items will appear in the User Folder:

image

Example 3: Adding 4 subfolders within the new folder on the Sales Order form

We are going to now add 4 subfolders to the new folder we added to the Sales Order form and add some item types. We will also demonstrate how to make them visible on certain folders.

1. Add a new User Folder as highlighed in Example 1

2. Add 4 new Folders to the User Folder like we do with the Checkboxes in Example 2:

 

image

3. For all Item types you add ensure you set the correct PaneLevel. If you wish your new items to be visible in all 4 new subfolders it will look like this:

image

If you wish an item to be visible on the first two folders then the FromPane will be 10 and ToPane 12.

4. Finally we catch the Item pressed event on each of the new folders:

 

image

In Business One this is what appears when clicking on Folder 4:

image

 

Our next blog will cover the topic of how to solve errors you get when connecting to the DI API or DTW. So catch you then :-)