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

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


Labels in this area