Ordering and Reordering rows in a Matrix

[Former Member]

Hi,

I am using a DBDataSource (User Defined) to populate a Matrix.

I use Conditions to filter this matrix.

I have a field called Seq in the Matrix and want to be able to order the matrix by Seq.

Can this be done without using a SQL Query in the datasource.

Also I have up and down buttons next to the Matrix - is there a way to move rows up and down (or reorder the rows) in the matrix without changing a code and requerying the Datasource ?

Thanks

Nick

Pages: 1

[Former Member · Accepted solution]

I think that only way for ordering is requery the datasource with proper ORDER BY statement. Add into table field order and order by this filed. When you need change order of 2 rows, youll have to change the ordering field and requery and rebind the datasource.

[Former Member · Accepted solution]

Can you please be more explicitly how cand i dow this.... can u please add an code example

[Former Member · Accepted solution]

Hi Dragos,

Hope this can help.

In my form I have 2 matrix's that need reordering, and I have 2 buttons next to each up1/dn1 and up2/dn2

Here is the event code :

Private Sub FLBOM_ItemPress(ByVal FormUID As String, ByRef pVal As SAPbouiCOM.ItemEvent, ByVal oform As SAPbouiCOM.Form)
        Dim s As Integer = 0
        'Dim oform2 As SAPbouiCOM.Form
        'oform2 = SBO_Application.Forms.Item("FLBOM")
        Try
            If pVal.Before_Action = True Then

                Select Case pVal.ItemUID
       
                    Case "up1"
                        FLBOM_MoveRow(pVal, oform, "matBOM", Direction.Up)
                    Case "dn1"
                        FLBOM_MoveRow(pVal, oform, "matBOM", Direction.Down)
                    Case "up2"
                        FLBOM_MoveRow(pVal, oform, "matSubBOM", Direction.Up)
                    Case "dn2"
                        FLBOM_MoveRow(pVal, oform, "matSubBOM", Direction.Down)
          End Select
            End If
        Catch ex As Exception
            MessageBox.Show("s=" & s.ToString & ":" & ex.Message)
        End Try
    End Sub

I also pin this to the itempress event on the matrix itself - if the user selects the top row of the matrix

it disables the up button, bottom of the matrix disables down button

      If pVal.FormMode = 1 Then

                                Dim m As SAPbouiCOM.Matrix = oform.Items.Item("matBOM").Specific
                                Dim r As Integer = pVal.Row
                                Dim rc As Integer = m.RowCount

                                Select Case r
                                    Case 1
                                        FLBOM_ItemEnable(oform, "up1", True)
                                        FLBOM_ItemEnable(oform, "dn1", False)
                                        FLBOM_ItemEnable(oform, "btnBOMDel", False)

                                    Case rc
                                        FLBOM_ItemEnable(oform, "up1", False)
                                        FLBOM_ItemEnable(oform, "dn1", True)
                                        FLBOM_ItemEnable(oform, "btnBOMDel", False)

                                    Case Else
                                        FLBOM_ItemEnable(oform, "up1", False)
                                        FLBOM_ItemEnable(oform, "dn1", False)
                                        FLBOM_ItemEnable(oform, "btnBOMDel", False)


                                End Select

ItemEnable looks like this

Private Sub FLBOM_ItemEnable(ByVal oform As SAPbouiCOM.Form, ByVal Itm As String, ByVal Off As Boolean)
        Dim e As SAPbouiCOM.Item
        e = oform.Items.Item(Itm)
        If Off Then
            e.Enabled = False
        Else
            e.Enabled = True
        End If

    End Sub

Then I have code to handle the moving of the rows - it is fairly generic - you feed it the form, matrix name and the direction and the code does the rest.

You need to change the SQL to update the right UDT

The matrix name(s) - remember I have 2 on the same form that I reorder so this code could easily be simplified.

Private Sub FLBOM_MoveRow(ByVal pval As SAPbouiCOM.ItemEvent, ByVal oform As SAPbouiCOM.Form, ByVal Mat As String, ByVal dir As Direction)
        If pval.FormMode = 1 Then
            Dim oMatrix As SAPbouiCOM.Matrix = oform.Items.Item(Mat).Specific
            Dim irow As Integer
            Try
                irow = oMatrix.GetNextSelectedRow(0, SAPbouiCOM.BoOrderType.ot_SelectionOrder)
                If irow = 1 And dir = Direction.Up Then
                    SBO_Application.StatusBar.SetText("Cannot Go Up ", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
                    Exit Sub
                End If
            Catch
                SBO_Application.StatusBar.SetText("Must Select a Line in First", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
                Exit Sub
            End Try

            If irow < 0 Then
                SBO_Application.StatusBar.SetText("Must Select a Line First", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
                Exit Sub
            End If

            ' CODE HERE
            Dim oTbl As SAPbouiCOM.DataTable
            Dim sSql As String
            Dim Seq1 As Integer
            Dim Seq2 As Integer
            Dim code1 As String
            Dim code2 As String
            Dim oe As SAPbouiCOM.EditText

            oe = oMatrix.Columns.Item("colSeq").Cells.Item(irow).Specific
            Seq1 = oe.Value
            oe = oMatrix.Columns.Item("#").Cells.Item(irow).Specific
            code1 = oe.Value

            Select Case dir
                Case Direction.Up
                    code2 = oMatrix.Columns.Item("#").Cells.Item(irow - 1).Specific.value
                    Seq2 = oMatrix.Columns.Item("colSeq").Cells.Item(irow - 1).Specific.value
                Case Direction.Down
                    code2 = oMatrix.Columns.Item("#").Cells.Item(irow + 1).Specific.value
                    Seq2 = oMatrix.Columns.Item("colSeq").Cells.Item(irow + 1).Specific.value
            End Select
            oTbl = oform.DataSources.DataTables.Item("TEMPTable1")
            sSql = "UPDATE [@LYNXS_SP] SET U_Seq=" & Seq2 & " WHERE Code = '" & code1 & "'"
            oTbl.ExecuteQuery(sSql)
            sSql = "UPDATE [@LYNXS_SP] SET U_Seq=" & Seq1 & " WHERE Code = '" & code2 & "'"
            oTbl.ExecuteQuery(sSql)

            If Mat = "matBOM" Then
                FLBOM_After_CFLBP(oform)
            Else
                Dim om1 As SAPbouiCOM.Matrix = oform.Items.Item("matBOM").Specific
                Dim e As SAPbouiCOM.EditText = om1.Columns.Item("#").Cells.Item(irow).Specific
                LoadSubBom(e.Value, oform)
            End If

            Select Case dir
                Case Direction.Up
                    irow = irow - 1
                Case Direction.Down
                    irow = irow + 1
            End Select
            oMatrix.SelectRow(irow, True, False)
            Dim rc As Integer = oMatrix.RowCount

            Select Case irow
                Case 1
                    FLBOM_ItemEnable(oform, "up1", True)
                    FLBOM_ItemEnable(oform, "dn1", False)
                Case rc
                    FLBOM_ItemEnable(oform, "up1", False)
                    FLBOM_ItemEnable(oform, "dn1", True)

                Case Else
                    FLBOM_ItemEnable(oform, "up1", False)
                    FLBOM_ItemEnable(oform, "dn1", False)


            End Select

            Try
                If Mat = "matBOM" Then
                    Dim e As SAPbouiCOM.EditText = oMatrix.Columns.Item("#").Cells.Item(irow).Specific
                    LoadSubBom(e.Value, oform)
                End If
            Catch ex As Exception
                MsgBox(ex.Message)

            End Try

        Else
            SBO_Application.StatusBar.SetText("Must Update Form First", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error)
        End If
    End Sub

So Finally - here are the 2 routines that load the matrix - notice I use the same routines to initially load the matrix as I do after the above updates when the row is moved.

 Private Sub FLBOM_After_CFLBP(ByVal oform As SAPbouiCOM.Form)

        Dim oMatrix As SAPbouiCOM.Matrix
        Dim oMatrix2 As SAPbouiCOM.Matrix
        oMatrix = oform.Items.Item("matBOM").Specific
        Dim oe As SAPbouiCOM.EditText = oform.Items.Item("3").Specific
        Dim oc As SAPbouiCOM.ComboBox = oform.Items.Item("cbArea").Specific

        Dim SQLSTR As String = "SELECT * FROM [@LYNXS_SP] WHERE U_PARENTCODE IS NULL AND U_CARDCODE = '" & oe.Value & "' AND U_AREAID = '" & oc.Selected.Value & "'  ORDER BY U_AREAID, U_SEQ"

        oform.DataSources.DataTables.Item("TEMP").ExecuteQuery(SQLSTR)
        oMatrix.LoadFromDataSource()

        oMatrix2 = oform.Items.Item("matSubBOM").Specific
        oMatrix2.Clear()
    End Sub

and

 Private Sub LoadSubBom(ByVal ParentID As Integer, ByVal oform As SAPbouiCOM.Form)
        Dim m As SAPbouiCOM.Matrix = oform.Items.Item("matSubBOM").Specific
        m.Clear()

        Dim SQLSTR As String = "SELECT * FROM [@LYNXS_SP] WHERE U_PARENTCODE = '" & ParentID & "' ORDER BY U_SEQ"

        oform.DataSources.DataTables.Item("TEMP").ExecuteQuery(SQLSTR)

        m.LoadFromDataSource()

    End Sub

one last bit of code that is missing from the above

    Public Enum Direction
        Up
        Down
    End Enum

Let me know how this goes for you.

Regards,

Nick

[Former Member]

This works - seems the easiest way