Create SapbouiCOM.Matrix from Query
This is a little method that I’m fairly proud of. We have a requirement for the end users to be able to search for multiple values on a UDO. Well, as I learned with my recent work looking into CFLs, that’s not an option normally. So, what I’m going to do is this:
I’m going to get the input from the users by using a comma-delimited list in the desired field, and parse it out into an array, and then use the array to automatically create a SQL query. Then, I’m going to circumvent the “Find Results Window” (Form 9999), hide the existing matrix, and replace it with my own based on that query that I will pass into the method below. The method below will take the query in as an input (along with our AddOnApp object, the Form object where the matrix will be placed on, and some string names for the objects that it will create), and will automatically create a new SAPbouiCOM.DataTable using the awesome ExecuteQuery function, and put that DataTable on the form. Then, it will look through that data and automatically create a matrix to match the data, and put it on the form, binding it to the DataTable. It will then return the completed matrix requested to the calling procedure. That procedure can then handle the matrix as-needed.
I’m pretty proud of this, as messy as some of the code is (to handle multiple uses of this method on a single form, ugh.).
Public Shared Function createMatrixFromQuery(ByRef oAddonApp As IAddOnApp,
ByRef oForm As SAPbouiCOM.Form,
ByVal sMatrixUID As String,
ByVal sDataTableUID As String,
ByVal sQuery As String,
Optional ByVal sKeyField As String = Nothing,
Optional ByVal iSAPbouiCOMBoLinkedObject As Integer = Nothing,
Optional ByVal sUDOObjectName As String = Nothing,
Optional ByVal bAutoSizeCols As Boolean = False,
Optional ByVal iColumnWidth As Integer = 50) As SAPbouiCOM.Matrix
Dim oMatrix As SAPbouiCOM.Matrix = Nothing
Dim oDataTable As SAPbouiCOM.DataTable = Nothing
Dim oLink As SAPbouiCOM.LinkedButton = Nothing
Try
oForm.Freeze(True)
oAddonApp.SBO_Application.SetStatusBarMessage(“Attempting to add new DataTable and Matrix to the form, please wait…”, SAPbouiCOM.BoMessageTime.bmt_Short, False)
‘Get the system font for measuring size
Dim fntUser As New Font(oAddonApp.SBO_Application.FontName, oAddonApp.SBO_Application.FontHeight)
‘Add the new DataSource to the form’s DataSources, and try to keep adding it until you can successfully add a new one
Dim bDataTableExists As Boolean = False
Dim iLastDataTableNumTried As Integer = -1
Do While Not bDataTableExists
Try
‘Make sure that the DataTable name is never longer than 10 characters as per SAP limitations
If sDataTableUID.Length > 10 Then
sDataTableUID = sDataTableUID.Substring(0, 10)
End If
oDataTable = oForm.DataSources.DataTables.Add(sDataTableUID)
bDataTableExists = True
Exit Do
Catch ex As Exception
iLastDataTableNumTried += 1
‘If we’ve tried to add over 9 elements, then error out as a critical failure
If iLastDataTableNumTried > 9 Then
oAddonApp.SBO_Application.MessageBox(“Critical failure of method ” & System.Reflection.MethodInfo.GetCurrentMethod.Name & _
“. Attempted to add more than 9 DataTables to the same form. Please close the window and try again.”)
Return Nothing
End If
‘Double check that the DataTable UID never gets bigger than 10 characters long
Dim sNewDataTableUID As String = Nothing
If sDataTableUID.Length >= 9 Then
sNewDataTableUID = sDataTableUID.Substring(0, 9) & iLastDataTableNumTried.ToString
Else
sNewDataTableUID = sDataTableUID & iLastDataTableNumTried.ToString
End If
oAddonApp.SBO_Application.SetStatusBarMessage(“DataTable ” & sDataTableUID & ” already exists, adding DataTable ” & sNewDataTableUID,
SAPbouiCOM.BoMessageTime.bmt_Short, False)
sDataTableUID = sNewDataTableUID
End Try
Loop
‘Do the same to create the matrix
Dim bMatrixExists As Boolean = False
Dim iLastMatrixNumTried As Integer = -1
Do While Not bMatrixExists
Try
‘Make sure that the Matrix name is never longer than 10 characters as per SAP limitations
If sMatrixUID.Length > 10 Then
sMatrixUID = sMatrixUID.Substring(0, 10)
End If
oMatrix = oForm.Items.Add(sMatrixUID, SAPbouiCOM.BoFormItemTypes.it_MATRIX).Specific
bMatrixExists = True
Exit Do
Catch ex As Exception
iLastMatrixNumTried += 1
‘If we’ve tried to add over 9 elements, then error out as a critical failure
If iLastMatrixNumTried > 9 Then
oAddonApp.SBO_Application.MessageBox(“Critical failure of method ” & System.Reflection.MethodInfo.GetCurrentMethod.Name & _
“. Attempted to add more than 9 Matrices to the same form. Please close the window and try again.”)
Return Nothing
End If
‘Double check that the matrix UID never gets bigger than 10 characters long
Dim sNewMatrixUID As String = Nothing
If sMatrixUID.Length >= 9 Then
sNewMatrixUID = sMatrixUID.Substring(0, 9) & iLastMatrixNumTried.ToString
Else
sNewMatrixUID = sMatrixUID & iLastMatrixNumTried.ToString
End If
oAddonApp.SBO_Application.SetStatusBarMessage(“Matrix ” & sMatrixUID & ” already exists, adding Matrix ” & sNewMatrixUID,
SAPbouiCOM.BoMessageTime.bmt_Short, False)
sMatrixUID = sNewMatrixUID
End Try
Loop
‘Run the Query to get the data
oDataTable.ExecuteQuery(sQuery)
For iCol As Integer = 0 To oDataTable.Columns.Count – 1
‘Set the column to process
Dim sColName As String = oDataTable.Columns.Item(iCol).Name
‘If we chose to autosize the columns, then go through the data from the rows and figure out the maximum width
‘WARNING, this is a SLOW process, so this option is not generally recommended to be used.
‘Far better to manually set the initial columns and let the end user resize them as needed.
Dim iMaxWidth As Integer = 0
If bAutoSizeCols Then
oAddonApp.SBO_Application.SetStatusBarMessage(“Please wait, determining optimal column size for column ” & sColName, SAPbouiCOM.BoMessageTime.bmt_Short, False)
For iRow As Integer = 0 To oDataTable.Rows.Count – 1
Dim sValue As String = oDataTable.Columns.Item(sColName).Cells.Item(iRow).Value.ToString.Trim
Dim szCurrentColData As Size = TextRenderer.MeasureText(sValue, fntUser)
If szCurrentColData.Width > iMaxWidth Then
iMaxWidth = szCurrentColData.Width
End If
Next
End If
‘If there is a key field denoted…
If sKeyField <> Nothing Then
‘…and the key field matches the current DataTable row…
If sColName = sKeyField Then
oMatrix.Columns.Add(sColName, SAPbouiCOM.BoFormItemTypes.it_LINKED_BUTTON)
oLink = oMatrix.Columns.Item(sColName).ExtendedObject
‘Check if we want to associate with a UDO object
If sUDOObjectName <> “” Then
oLink.LinkedObject = SAPbouiCOM.BoUDOObjectType.udo_Document
oLink.LinkedObjectType = sUDOObjectName
Else
‘If not, link to whatever was passed in
oLink.LinkedObject = iSAPbouiCOMBoLinkedObject
End If
Else
‘Otherwise, set it up as a basic EditText
oMatrix.Columns.Add(sColName, SAPbouiCOM.BoFormItemTypes.it_EDIT)
End If
‘Setup the default settings that we desire
oMatrix.Columns.Item(sColName).TitleObject.Caption = sColName
oMatrix.Columns.Item(sColName).TitleObject.Sortable = True
oMatrix.Columns.Item(sColName).Editable = False
If bAutoSizeCols Then
oMatrix.Columns.Item(sColName).Width = iMaxWidth
Else
oMatrix.Columns.Item(sColName).Width = iColumnWidth
End If
‘Bind the column to the datatable column
oMatrix.Columns.Item(sColName).DataBind.Bind(oDataTable.UniqueID, sColName)
End If
Next
‘Load the data into the matrix
oMatrix.LoadFromDataSource()
‘Finally, return the filled source
oAddonApp.SBO_Application.SetStatusBarMessage(“Objects successfully added to the form!”, SAPbouiCOM.BoMessageTime.bmt_Short, False)
Return oMatrix
Catch ex As Exception
AsapSharedClasses.ErrorLog.AddEntryWithTrace(ex)
Return Nothing
Finally
‘Note that we are NOT releasing the oMatrix object here, otherwise we’ll end up with RCW errors.
‘Instead, the calling procedure should release the matrix object when it is done with it.
releaseCOMobject(oDataTable)
releaseCOMobject(oLink)
oForm.Freeze(False)
End Try
End Function