cancel
Showing results for 
Search instead for 
Did you mean: 

How to add an UDF on a SAP system table(document) via DI API

former_member244307
Participant
0 Kudos

Hi All,

Basically I want to create an UDF on SAP system document like MarketingDocument header or rows. I can't find a sample in the forum. Can anyone post some sample code ?

Thanks,

Lan

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Lan

Try this:

Firstly an Excel VBA sample:

Sub AddFields()

Dim vCmp As SAPbobsCOM.Company
Dim lRetCode, lErrCode As Long
Dim sErrMsg As String
Dim lRet_val As Long
Set vCmp = New SAPbobsCOM.Company

vCmp.Server = "(local)"
vCmp.CompanyDB = "SBODemoZA"
'vCmp.LicenseServer = "localhost:30000"
vCmp.UserName = "manager"
vCmp.Password = "ebs123"
vCmp.Language = ln_English
vCmp.DbServerType = dst_MSSQL2012
vCmp.DbUserName = "sa"
vCmp.DbPassword = "*******"
vCmp.UseTrusted = False

lRetCode = vCmp.Connect

If lRetCode <> 0 Then
    vCmp.GetLastError lErrCode, sErrMsg
    MsgBox (sErrMsg)
End If

On Error GoTo Error_handler
Worksheets("Fields").Select
row_no1 = 2

Get_Table:
    Table_no = Range("A" & row_no1 & "").Value
    Field_no = Range("B" & row_no1 & "").Value
    If Field_no = "" Then GoTo End_sub
    Field_no2 = Range("B" & row_no1 + 1 & "").Value
    Field_name = Range("C" & row_no1 & "").Value
    Field_desc = Range("D" & row_no1 & "").Value
    Field_type = Range("E" & row_no1 & "").Value
    Field_subt = Range("F" & row_no1 & "").Value
    Field_size = Range("G" & row_no1 & "").Value
    Field_dflt = Range("I" & row_no1 & "").Value
    Field_null = Range("J" & row_no1 & "").Value
    Field_tabl = Range("L" & row_no1 & "").Value
    Field_lnk1 = Range("P" & row_no1 & "").Value
    Field_lnk2 = Range("Q" & row_no1 & "").Value
    GoSub Write_Table
    row_no1 = row_no1 + 1
    GoTo Get_Table
   
Write_Table:
    Dim vUDF As SAPbobsCOM.UserFieldsMD
    Set vUDF = vCmp.GetBusinessObject(oUserFields)
    vUDF.TableName = Table_no
    vUDF.Name = Field_name
    vUDF.Description = Field_desc
    If Field_type = "A" Then vUDF.Type = db_Alpha
    If Field_type = "B" Then vUDF.Type = db_Float
    If Field_type = "D" Then vUDF.Type = db_Date
    If Field_type = "N" Then vUDF.Type = db_Numeric
    If Field_subt = "%" Then vUDF.SubType = st_Percentage
    If Field_subt = "S" Then vUDF.SubType = st_Sum
    If Field_subt = "Q" Then vUDF.SubType = st_Quantity
    vUDF.Size = Field_size
    If Field_null = "Y" Then vUDF.Mandatory = tYES Else vUDF.Mandatory = tNO
    If Field_tabl <> "" Then vUDF.LinkedTable = Field_tabl
    If Field_lnk1 <> "" Then vUDF.ValidValues.Description = Field_lnk2
    If Field_lnk1 <> "" Then vUDF.ValidValues.Value = Field_lnk1
    If Field_lnk1 <> "" Then row_no1 = row_no1 + 1: GoSub Get_Next
    If Field_no <> Field_no2 Then
        RetVal1 = vUDF.Add
        vCmp.GetLastError lErrCode, sErrMsg
        Range("S" & row_no1 & "").Value = RetVal1 & "-" & sErrMsg
    End If
    If Field_no <> Field_no2 Then Return
    RetVal1 = vUDF.Add
    Return

Get_Next:
    vUDF.ValidValues.Add
    Field_no = Range("B" & row_no1 & "").Value
    Field_no2 = Range("B" & row_no1 + 1 & "").Value
    Field_lnk1 = Range("P" & row_no1 & "").Value
    Field_lnk2 = Range("Q" & row_no1 & "").Value
    If Field_lnk1 <> "" Then vUDF.ValidValues.Description = Field_lnk2
    If Field_lnk1 <> "" Then vUDF.ValidValues.Value = Field_lnk1
    If Field_no2 <> 0 Then vUDF.DefaultValue = Field_dflt: Return
    If Field_no = Field_no2 Then row_no1 = row_no1 + 1: GoTo Get_Next
    vUDF.DefaultValue = Field_dflt
    Return

Error_handler:
    MsgBox ("Error!")
    Resume
   
End_sub:
    MsgBox ("Disconnecting")
    vCmp.Disconnect
      
End Sub


Secondly a vb,net sample:

Private Sub CreateField(ByVal sTable As String, ByVal sName As String, ByVal sDesc As String, ByVal iSize As Integer, ByVal aFieldType As SAPbobsCOM.BoFieldTypes, ByVal aSubType As SAPbobsCOM.BoFldSubTypes, ByVal sLink As String, ByVal bMand As SAPbobsCOM.BoYesNoEnum, ByVal bDefl As String)

        Dim iResult As Long
        Dim sMsg As String = ""
        Dim i As Integer
        Dim bFound As Boolean = False
        Dim oRc As SAPbobsCOM.Recordset = Nothing
        Dim FieldIndex As Integer

        Try
            oRc = Me.muc_ParentAddon.SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            oRc.DoQuery("SELECT aliasID, TableID, FieldID FROM CUFD WHERE aliasID='" & sName & "' and (TableID = '@" & sTable & "' or TableID = '" & sTable & "')")
            oRc.MoveFirst()

            If oRc.RecordCount > 0 Then
                bFound = True
                FieldIndex = oRc.Fields.Item(2).Value
            End If
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oRc)
            oRc = Nothing

            If Not bFound Then
                Dim oUserFieldsMD As SAPbobsCOM.UserFieldsMD = Nothing
                oUserFieldsMD = Me.muc_ParentAddon.SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields)
                oUserFieldsMD.TableName = sTable
                oUserFieldsMD.Name = sName
                oUserFieldsMD.Description = sDesc
                oUserFieldsMD.Type = aFieldType
                oUserFieldsMD.EditSize = iSize
                oUserFieldsMD.SubType = aSubType
                oUserFieldsMD.Mandatory = bMand
                If sLink <> "" Then oUserFieldsMD.LinkedTable = sLink
                If bDefl <> "" Then oUserFieldsMD.DefaultValue = bDefl

                If Not ValidVals Is Nothing Then
                    For i = 0 To ValidVals.Length - 1
                        oUserFieldsMD.ValidValues.Add()
                        oUserFieldsMD.ValidValues.SetCurrentLine(i)
                        oUserFieldsMD.ValidValues.Value = ValidVals(i).Value
                        oUserFieldsMD.ValidValues.Description = ValidVals(i).Description
                        'If i = 0 Then
                        '    oUserFieldsMD.DefaultValue = ValidVals(i).Value
                        'End If
                    Next
                End If

                iResult = oUserFieldsMD.Add()
                If iResult <> 0 Then
                    Me.muc_ParentAddon.SBO_Company.GetLastError(iResult, sMsg)
                    Me.muc_ParentAddon.SBO_Application.SetStatusBarMessage("Exception Creating Field " & sName & " in Table " & sTable & " : " & sMsg, EventLogEntryType.Error)
                End If
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserFieldsMD)
                oUserFieldsMD = Nothing
            End If

        Catch ex As Exception
            Me.muc_ParentAddon.SBO_Application.SetStatusBarMessage("Exception in Create Field U_" & sName & " in Table " & sTable & "  : " & ex.Message, EventLogEntryType.Error)
        Finally

        End Try

    End Sub


Hope this helps.

Kind regards

Peter Juby

former_member244307
Participant
0 Kudos

Hi Peter,

I am able to create the UDF now, thanks. But the only thing is as other people mentioned that I have to execute code right after connect to SAP company, otherwise I will get error: Ref count for this object is higher than 0, error code -1120. Not sure if it is a bug or I did something wrong.

Lan

bruno_peyron
Active Participant
0 Kudos

I Lan

you must release the object oUSerField after create  UDF et before create an another UDF

   System.Runtime.InteropServices.Marshal.ReleaseComObject(oUserFieldsMD)

oUserFieldsMD = Nothing


Regards

Answers (0)