Skip to Content
author's profile photo Former Member
Former Member

Ordering and Reordering rows in a Matrix

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2008 at 06:34 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2008 at 12:11 AM

    This works - seems the easiest way

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.