Skip to Content

This is a method that I wrote that’s fairly simple, but that I’m proud of for its simplicity. It takes in a reference to our AddOnApp, the pVal ItemEvent, the UID for the specific Matrix that you want to work with, and the UID for the “key field” that must be populated for a row to be a valid row. Then it spits back out a VB.net DataTable:

”’ <summary>

”’ Builds a VB.NET DataTable based on an SAP form Matrix

”’ </summary>

”’ <param name=”oAddOnApp”>A reference to the AddOnApp</param>

”’ <param name=”pVal”>A reference to the ItemEvent pVal</param>

”’ <param name=”sMatrixUID”>The UID for the matrix (different for different types of forms)</param>

”’ <param name=”sKeyFieldID”>The UID for the “Key” Field, the one that must be populated for it to be a valid row</param>

”’ <returns>A VB.Net DataTable clone of the SAP matrix</returns>

”’ <remarks>This method was primarily built so that we can query the data in the matrix without having to constantly go through it. Use the DT.Select statement</remarks>

Public Shared Function buildMatrixTable(ByRef oAddOnApp As IAddOnApp, ByRef pVal As SAPbouiCOM.ItemEvent, ByVal sMatrixUID As String, ByVal sKeyFieldID As String) As DataTable

    Dim oForm As SAPbouiCOM.Form = Nothing

    Dim oMatrix As SAPbouiCOM.Matrix = Nothing

    Try

        Dim oDT As New DataTable

        oForm = oAddOnApp.SBO_Application.Forms.GetForm(pVal.FormType, pVal.FormTypeCount)

        oMatrix = oForm.Items.Item(sMatrixUID).Specific

        ‘Add all of the columns by unique ID to the DataTable

        For iCol As Integer = 0 To oMatrix.Columns.Count – 1

            ‘Skip invisible columns

            If oMatrix.Columns.Item(iCol).Visible = False Then Continue For

            oDT.Columns.Add(oMatrix.Columns.Item(iCol).UniqueID)

        Next

        ‘Now, add all of the data into the DataTable

        For iRow As Integer = 1 To oMatrix.RowCount

            Dim oRow As DataRow = oDT.NewRow

            For iCol As Integer = 0 To oMatrix.Columns.Count – 1

                If oMatrix.Columns.Item(iCol).Visible = False Then Continue For

                oRow.Item(oMatrix.Columns.Item(iCol).UniqueID) = oMatrix.Columns.Item(iCol).Cells.Item(iRow).Specific.Value

            Next

            ‘If the Key field has no value, then the row is empty, skip adding it.

            If oRow(sKeyFieldID).ToString.Trim = “” Then Continue For

            oDT.Rows.Add(oRow)

        Next

        Return oDT

    Catch ex As Exception

        AsapSharedClasses.ErrorLog.AddEntryWithTrace(ex)

        Return Nothing

    Finally

        ‘releaseCOMobject is our method that checks if an SAPBouiObject is Nothing or not, and if it’s not Nothing,

        ‘uses System.Runtime.InteropServices.Marshal.ReleaseComObject to release it from memory, and then sets it to nothing.

        ‘This prevents memory leaks in the program from unreleased COM objects.

        NewSharedMethods.releaseCOMobject(oMatrix)

        NewSharedMethods.releaseCOMobject(oForm)

    End Try

End Function

Now, why would you want to do this? Well, we are constantly doing checks on the user’s Add/Update to verify certain information within the form to see if it’s correct (using our own validation methods). This method will help us simplify this. Instead of having to go through the UI API multiple times and looping through the rows and columns to find the data we’re looking for, instead we can do it just once, and then we can use the VB.NET DataTable to do some interesting things.

Specifically, we can use the DataTable.Select method to query for the specific rows and data that we want to find. Below is an example validateForm method that I wrote for a Bill of Materials form to validate that if there is a “labor” line and an “overhead” line, that the labor is not greater than the overhead:

Private Function validateForm(ByRef pVal As SAPbouiCOM.ItemEvent, ByRef BubbleEvent As Boolean) As Boolean

        Try

            ‘Pull the matrix into a DataTable for querying

            Me.m_dtMatrix = NewSharedMethods.buildMatrixTable(Me.AddOnApp, pVal, CONSTANTS.BOM_MATRIX, CONSTANTS.BOM_MATRIX_ITEM_CODE_COL)

            ‘Make sure that there’s not more than one WIPLABOR line item on the form

            ‘Note the brackets that are used here that are necessary in case the column name includes spaces or starts with a number. The brackets

            ‘denote to the Select statement that the contents within the brackets are in fact a column name, rather than a value/variable to compare.

            Dim oWIPLABORrows() As DataRow = Me.m_dtMatrix.Select(“[” & CONSTANTS.BOM_MATRIX_ITEM_CODE_COL & “]” & “=’WIPLABOR'”)

            If oWIPLABORrows IsNot Nothing AndAlso oWIPLABORrows.Length > 1 Then

                Me.AddOnApp.showMessageBoxWithOverride(“Error, there is more than one WIPLABOR line. Only one is allowed, please correct.”, BubbleEvent)

                If BubbleEvent Then

                    Email.sendWarningEmail(Me.m_ClassName & “.” & System.Reflection.MethodInfo.GetCurrentMethod.Name,

                                           “BoM was overridden to allow more than one WIPLABOR row.”, False)

                Else

                    Return False

                End If

            End If

            ‘Make sure that there’s not more than one WIP O/H line item on the form

            Dim oWIPOHrows() As DataRow = Me.m_dtMatrix.Select(“[” & CONSTANTS.BOM_MATRIX_ITEM_CODE_COL & “]” & “=’WIP O/H'”)

            If oWIPOHrows IsNot Nothing AndAlso oWIPOHrows.Length > 1 Then

                Me.AddOnApp.showMessageBoxWithOverride(“Error, there is more than one WIP O/H line. Only one is allowed, please correct.”, BubbleEvent)

                If BubbleEvent Then

                    Email.sendWarningEmail(Me.m_ClassName & “.” & System.Reflection.MethodInfo.GetCurrentMethod.Name,

                                           “BoM was overridden to allow more than one WIP O/H row.”, False)

                Else

                    Return False

                End If

            End If

            ‘Prevent the WIPLABOR from being greater than the WIP O/H

            If oWIPLABORrows IsNot Nothing AndAlso oWIPOHrows IsNot Nothing Then

                ‘Get the strings from the total price fields – note that we are always only using the FIRST found row of these values.

                ‘As per Todd, if there exists more than one row and they overrode it, we will only use the value of the first row for this check.

                Dim sLabor As String = oWIPLABORrows(0).Field(Of String)(CONSTANTS.BOM_MATRIX_TOTAL_PRICE_COL)

                Dim sOH As String = oWIPOHrows(0).Field(Of String)(CONSTANTS.BOM_MATRIX_TOTAL_PRICE_COL)

                ‘Try to convert the strings to decimals

                sLabor = sLabor.Replace(“$”, “”).Trim

                sOH = sOH.Replace(“$”, “”).Trim

                Dim dLabor As Decimal = 0.0

                Dim dOH As Decimal = 0.0

                ‘Compare the decimals. If the Labor is greater than the O/H, report and abort

                If Decimal.TryParse(sLabor, dLabor) AndAlso Decimal.TryParse(sOH, dOH) Then

                    If dLabor > dOH Then

                        Me.AddOnApp.showMessageBoxWithOverride(“Labor charge of ” & dLabor.ToString & ” is greater than the overhead charge of ” & dOH.ToString & “. ” & _

                                                               “This is not allowed. Please correct and try again.”, BubbleEvent)

                        If BubbleEvent Then

                            Email.sendWarningEmail(Me.m_ClassName & “.” & System.Reflection.MethodInfo.GetCurrentMethod.Name,

                                                   “BoM was overridden to allow WIPLABOR > WIP O/H.”, False)

                        Else

                            Return False

                        End If ‘BubbleEvent Then

                    End If ‘dLabor > dOH Then

                End If ‘Decimal.TryParse(sLabor, dLabor) AndAlso Decimal.TryParse(sOH, dOH) Then

            End If ‘oWIPLABORrows IsNot Nothing AndAlso oWIPOHrows IsNot Nothing Then

            ‘If we got here, then everything checks out, return true

            Return True

        Catch ex As Exception

            AsapSharedClasses.ErrorLog.AddEntryWithTrace(ex)

            Return False

        End Try

    End Function

The CONSTANTS you see in the code are our direct references to the UIDs of UI API columns and other UI API objects. By using the buildMatrixTable method right at the start, as you can see, this validation method doesn’t require any looping. That means less overall calls to SAP to check the UI (just one big call at the start), then the rest of the checks/etc. can be done entirely in memory.

I feel that this method will benefit us greatly in the future, not just in this instance, but in future instances when we need to “rip” data out of a matrix that hasn’t yet been committed to the SAP database, but that we would like to go through and process to follow business rules.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply