Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos

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.

Labels in this area