lisa.mulchinock

14 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.

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 :-)

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. The specified item was not found.

 

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.

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 :-)

The SP_TransactionNotification is the only legal way in SAP Business One to receive notification of data-driven events - in order words to do some validation in SAP Business One and prevent the user from doing an action if the validation condition fails. This stored procedure(SP) is created automatically with each database and a default SP looks like this:

 

image

What can i do with the SBO_SP_TransactionNotification stored procedure?

You can add your own code in the section 'ADD YOUR CODE HERE'. Since Version 2007 you can now change the Error Code returned by the transaction notification and  prevent the transaction from being committed to the database. The SBO_SP_TransactionNotification must return two values - @error and @error_messages. If what is returned in @error is anything other than zero, the transaction will rollback.

There are five parameters in the TransactionNotification:

  1. @objecttype - the object type you want to use - this is a string value and represents the object eg Business Partner, Invoice etc.
  2. @transaction_type - this is the action type eg Add, Update, Cancel etc.
  3. @num_of_cols_in_key - number of columns/fields in the key of the record.
  4. @list_of_key_cols_tab_del - returns a tab delimited list of column/field names that represent the object key.
  5. @list_of_cols_val_tab_del - returns a tab delimited list of values required to retrieve the object from B1.

 

Examples of how it can be used.

Example 1: Prevent a user from adding a Sales Invoice if the field 'Customer Ref. No' is empty/blank. 

image

 

Example 2: Prevent a Purchase Invoice from being added if it does not have a base document (Purchase Order or Goods Receipt PO)

image

 

Example 3: Prevent an Incoming Payment from being added if the amount is greater than 2500 for all users except the manager.

image

As you can see the structure for all samples remains the same and only the query/logic changes. 

What the other SBO Stored Procedures do.

In the 2007 version there are 4 stored procedures:
- SBO_SP_PostTransactionSupport and SBO_SP_TransactionSupport (since SAP Business One 2007)
- SBO_SP_PostTransactionNotice (since SAP Business One 2007)
- SBO_SP_TransactionNotification

The stored procedures SBO_SP_TransactionSupport and SBO_SP_PostTransactionSupport are encoded and cannot be edited by the user. They are database notification stored procedures which can be used by SAP support to add diagnostic code so they are of no use for customer's daily work. Since they are of no use it is planned to remove them in SAP Business One Version 8.8.

SBO_SP_PostTransactionNotice and SBO_SP_TransactionNotification can by edited by the user to add sql code as part of a notification process. The functionalities of both  are the same, the only differences being when they are triggered and the ability to use the @error functionality of SBO_SP_TransactionNotification to rollback transactions. SBO_SP_PostTransactionNotice can only be triggered at the end of an action (transaction committed). It is preferable to use SBO_SP_PostTransactionNotice - although as this occurs after the transaction, the @error rollback functionality can not be used.

Further Documentation

The following documenatation is available on the SDN:                                                                                
    o  Using the SBO_SP_TransactionNotification Stored Procedure:              
                                                                               
       http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uu       
       id/e991e2b9-0901-0010-0395-ef5268b00aaf                                                 
                                                                                                   
                                                                                
    o  SAP Business One Add-On Solution Certification                          
                                                                               
       http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/libra       
       ry/uuid/81a22ee1-0701-0010-45aa-ec852e882de3                

             

In two weeks time, our blog will cover the topic of UI folders/tabs and how to create a new folder/tab in your form. So catch you then :-)

In this blog we will describe what the SAP Business One License API is and how you can use it in your Add-on. 

With SAP Business One 2005, the License Service moved from DCOM to CORBA to avoid security issues as CORBA is not tied to Windows authentication. Due to this a License bridge to the License Service API was developed. In essence it wraps the CORBA interface which is exposed by the SAP Business One License Service with a COM wrapper.

How to install the License Bridge to access the API

Run the Setup.Exe from the COM License Bridge Setup package provided for download in the following link:

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/508623e3-425c-2910-38be-cbe56e65930c

It's important to Note that it will only work on the system where the License Service is running. Once it's installed you can register it as a COM Server by Start -> All Programs -> SAP Business One -> Server Tools -> COM License Bridge -> Register COM License Bridge. You can also unregister it selecting Unregister COM License Bridge

How to use the COM License Bridge

1. Add a reference “SBOLicense COM Bridge 1.0 Type Library” in your Microsoft Visual Studio project

image

2.Declare a variable for the LicenseInfo interface

Dim lc As SBOLICENSELib.ILicenseInfo - VB

SBOLICENSELib.LicenseInfo lc = null; - C#

3.Instantiate the LicenseInfo interface:

lc = NewSBOLICENSELib.LicenseInfo - VB

lc = SBOLICENSELib.LicenseInfo(); - C#

Once this is done you can then access all the methods of the License API. You can find the full list of methods exposed in the SDK Help File.

Some examples of how to use the License Bridge

1. Retrieve the Hardware Key and Installation Number from your License Service.

Dim hk, ins as String 

lc.GetHardwareKey(hk)

lc.GetInstallationNumber(ins)

MsgBox("Hardware key is " & hk & " and installation number is " & ins)

 

2. Check if a particular user is assigned a Professional User License.

Please note bstrModule expects the SWPRODUCTNAME from the license file.

result = lc.IsUserLicensed("manager", "PROFESSIONAL_MSS")

If result <> 0 Then

MsgBox("User is licensed")

Else

MsgBox("User is not Licensed")

End If

 

3. GetLicenseInfo allows you retrieve specific information from the license file.

Dim num, avail, start, dend as Integer

lc.GetLicenseInfo("PROFESSIONAL_MSS", num, avail, start, dend)

MsgBox("For Addon SDK TEST you have " & num & " in your license file and " & avail & " available. License started on start " & start & " and expires on " & dend)

 

4. GetLoggedinUsers returns a list of users currently logged into Business One - note this is via an XML file.

Dim xmlDoc As New Xml.XmlDocument

Dim productNodes As Xml.XmlNodeList

Dim ProductNode As Xml.XmlNode

Dim baseDataNodes Xml.XmlNodeList

Dim bFirstInRow As Boolean

lc.GetLoggedInUsers(users)

xmlDoc.LoadXml(users)

productNodes = xmlDoc.GetElementsByTagName("users")

For Each productNode In productNodes

baseDataNodes = productNode.ChildNodesbFirstInRow = True

For Each baseDataNode As Xml.XmlNode In baseDataNodes

If (bFirstInRow) Then bFirstInRow = False

Else

Console.Write(", ")

End If

Console.Write(baseDataNode.Name & ": " & baseDataNode.InnerText)

Next

Next

 

Planned changes in Version 8.8:

You can download the COM License Bridge for SAP Business One 8.8 from the link:

http://www.sdn.sap.com/irj/sdn/index?rid=/library/uuid/007a9ef5-9a9c-2d10-f3bc-94f765f235ce

 

Other information on the License Bridge in 8.8 can be found at:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/webcontent/uuid/702bc48d-8c53-2d10-3697-c8e7cd126256?prtmode=wpcpreview&wpcpreviewkey=56c3851fb9accfe8b62ab9290d050e36

 

The planned changes in the license API are as follows:

  • GetInstallationNumberList method replaces the GetInstallationNumber method. The new method returns a list of installation numbers for the loaded license file, instead of a single number.
  • GetSystemNumber method's arguments have changed. The installation Number is passed to get the related system number.
  • IsLicenseFileExist is no longer supported.

In two weeks time, our blog will cover the topic of the SBO_SP_TransactionNotification stored procedure. So catch you then :-)

Lisa Mulchinock

SDK Licensing

Posted by Lisa Mulchinock Apr 1, 2009

In support we tend to get a lot of similar queries/how to questions. Every two weeks in this support blog we will highlight some of these commonly asked questions and hopefully help you understand each topic a little better.

My first topic is add-on licensing which can be confusing. In this blog I’ll go though the different SDK license types and what they are used for. I’ll also go through the process of licensing your own Add-ons.

License types for add-on development phase.

image

If you wish to develop an add-on that will uses the UI API only then you only need the Software Development Kit - Implementing License that is delivered free with the Professional user license. It is a concurrent license and will allow you only to change/add to/update the SAP Business One GUI. You cannot access Database tables or Business Objects in your add-on if you only have the Implementation license.

If you wish to develop an add-on that will use the DI API or a combination of the DI and UI API then you need a Software Development Kit - Development License. This is a concurrent license and it involves an additional cost and must be requested separately from the Service Market Place. This will allow you to access all Business Objects exposed in both APIs.

 

Options for licensing your completed add-on.

You now have some choices on how to license your own SDK add-on:

  1. Use your own License Mechanism for your add-on.
  2. Use the SDK License Mechanism available from SAP.
  3. Do not use any license mechanism - Use the Compatibility License which is the simplest option! 

1. Partners are free to develop their own license mechanism if they wish.

 

2. If you want to use SAP’s licensing mechanism, then you will need to utilise the AddOnIdentifier string value. This is a property of the Company(DI) and SBOGuiAPI(UI) objects in the SDK. This identifier can be generated from Administration -> License -> Add-on Identifier Generator.

 

image

 

Both the Implementation and the Development license are not necessary. They are available to use and I will explain what they do but its fine to use the Compatibility license in their place.

 

Implementation Identifier:                                                     

This identifier should be used with UI add-ons only. Once the UI add-on is developed at the partner site, an identifier must be generated at the customer network before compiling it using the customer's SAP Business One Implementation license, insert this identifier in your code and compile the add-on. It will only work in the customer network as the identifier is network dependent. You must follow this procedure for each customer using your UI add-on.   

 

Development Identifier:

This identifier should be used with DI or DI/UI add-ons. The partner will generate the Development add-on Identifier and include it in their code while they are developing and testing the Add-on.                          

                                             

Solution identifier:

This is the most important identifier and the only one I would suggest you need to use if you wish to use SAP's license mechanism.

Once the Add-on is finished you need to request a BASIS license from SAP in order to generate a Solution identifier. A BASIS license is needed for generating the add-on Identifier for the Solution identifier. You need to generate this Solution identifier if providing the add-on you developed to an end customer and wish to use SAP’s license mechanism.

Procedure:

 

1. Request the BASIS license from SAP via a customer message. Use the component SBO-SDK-AA providing the following information:

             * Your partner Number                                         
             * The full name (short description) of the solution you wish to generate    
                the license key name for.  

             * Is this a solution for a single end customer or a global available        
                solution? 

2. Generate the Solution Identifier once you receive the BASIS license from SAP

3. Insert the string generated into your add-on code (AddOnIdentifier property) and compile the exe file.   

 

Solution names with special characters (&,%,#) will cause problems in the SAP Business One system after installation of the license key for this add-on so please DO NOT use these characters on your addOn name.   

 

At the customer site:

Once the add-on has been installed you will need to update/request a new license file to include this add-on. This can be done through the SAP SMP (http://service.sap.com/licensekey) as it is done now for the other SAP Business One licenses types. When requesting a license you will see the name of your add-on you registered in the drop down list. Once this license is requested and downloaded the end customer will be able to work with the partner Add-on. This is a named user solution so licenses will have to be allocated to designated users.  

 

3. If you don't set the AddonIdentifier property in your code then you run your add-on using the Compatibility license. With the compatibility license you can work with your add-on without licensing it - so after all that maybe this is the easiest option available!

                                                                                                                       

In two weeks time, we will do another blog on licensing – this time the SAP Business One License API - an external API that allows you to read information from the license service to which the application is connected. So catch you then :-)