Skip to Content
Author's profile photo Former Member

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

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.