Build a VB.Net DataTable out of an SAPBouiCOM Matrix

Build a VB.Net DataTable out of an SAPBouiCOM Matrix: 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:

”’

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

”’

”’A reference to the AddOnApp

”’A reference to the ItemEvent pVal

”’The UID for the matrix (different for different types of forms)

”’The UID for the “Key” Field, the one that must be populated for it to be a valid row

”’ A VB.Net DataTable clone of the SAP matrix

”’ 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

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.