cancel
Showing results for 
Search instead for 
Did you mean: 

Matrix with Info Columns from different Table

Former Member
0 Kudos

Hi, how can i manage to have a column in a matrix that doesn't represent a value of the table the other columns are using?

For example, i have a matrix and this matrix has values of the UDT (@Table) and one column contains the projectcode. now i want one column in my matrix to show the project name of the OPRJ. how can i manage that, so that the value of this column in the matrix changes when i change the value of the other column with the project code?

best regards

Philipp

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi

1) You can fill the matrix from Recordset.

For example: UDT is [@O01_USERTABLE].

PrjCode foreign key to the Projects table (OPRJ - not exposed through the DI API).

So, if you type correctly PrjCode, you can fetch description using method below.


Dim sSql As String = ""
sSql = " SELECT U.PrjCode AS PrjCode, S.PrjName As PrjName" & _
           " FROM [@O01_USERTABLE] AS U, OPRJ AS S" & _
           " WHERE U.PrjCode = S.PrjCode"
oRS = SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
oRS.DoQuery(sSql)
If oRS.RecordCount > 0 Then
  Dim i As Integer = 0
  oRS.MoveFirst()
  While oRS.EoF = False
    i = i + 1
    oForm.DataSources.UserDataSources.Item("uNr").Value = i     ' 1" matrix column for row number
    oForm.DataSources.UserDataSources.Item("uPrjCode").Value = oRS.Fields.Item("PrjCode").Value
    oForm.DataSources.UserDataSources.Item("uPrjName").Value = oRS.Fields.Item("PrjName").Value
    oMatrix.AddRow()
    oRS.MoveNext()
  End While
End If

2) On ManageDataEvent, call Matrix_FetchDescriptions(...)


Public Sub ManageDataEvent(ByRef BusinessObjectInfo As SAPbouiCOM.BusinessObjectInfo, ByRef BubbleEvent As Boolean)
  Dim oForm As SAPbouiCOM.Form = SBO_Application.Forms.Item(BusinessObjectInfo.FormUID)
  If BusinessObjectInfo.EventType = SAPbouiCOM.BoEventTypes.et_FORM_DATA_LOAD Then
    If Not BusinessObjectInfo.BeforeAction Then
      Try
	  oForm.Freeze(True)
	Dim oMatrix As SAPbouiCOM.Matrix
	oMatrix = oForm.Items.Item("mtx00").Specific
	If oMatrix.RowCount > 0 Then
	  Call Matrix_FetchDescriptions(oForm, -1)
	End If
      Catch ex As Exception
      Finally
	oForm.Freeze(False)
	oForm.Update()
      End Try
    End If
  End If
  oForm = Nothing
End Sub

' Update column Name
Private Sub Matrix_FetchDescriptions(ByRef oForm As SAPbouiCOM.Form, ByVal iRow As Integer)
  Dim oMatrix As SAPbouiCOM.Matrix
  Dim oColumn As SAPbouiCOM.Column
  Dim oCell As SAPbouiCOM.Cell
  Dim oEdit As SAPbouiCOM.EditText
  Try
    If SBO_Company.Connect Then
	oMatrix = oForm.Items.Item("mtx00").Specific
	If oMatrix Is Nothing Then Throw New Exception("ERROR: matrix object is nothing")
	Dim iNrRows As Integer = oMatrix.RowCount ' iRow
	If iNrRows > 0 Then
	  Dim i As Integer = 1
	  For i = 1 To iNrRows
	    Dim sCode As String = ""
	    Dim sName As String = ""
	    Try
              ' Matrix column PrjCode
	      oColumn = oMatrix.Columns.Item("ePrjCode")
	      oCell = oColumn.Cells.Item(i)
	      oEdit = oCell.Specific
	      sCode = oEdit.Value
	      sName = GetNameByCode(sCode)
	      If Not sName.Equals("") Then
                 ' Matrix column PrjName
		  oColumn = oMatrix.Columns.Item("ePrjName")
		  oCell = oColumn.Cells.Item(i)
		  oEdit = oCell.Specific
		  oEdit.Value = sName
	      End If
	      If iRow = i Then Exit Sub
	    Catch ex As Exception
	    Finally
	    End Try
	  Next
	  oForm.Refresh()
	End If
    End If
  Catch ex As Exception
    ' log exception
  Finally
    oMatrix = Nothing
    oColumn = Nothing
    oCell = Nothing
    oEdit = Nothing
    System.GC.Collect() 'Release the handle to the table 
  End Try
End Sub

To get Name using Code...


Private Function GetNameByCode(ByVal sCode As String) As String
  If sCode.Trim.Equals("") Then Return ""
  Dim sName As String = ""
  Dim oRS As SAPbobsCOM.Recordset
  Try
    oRS = SBO_Company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
    Dim sSql As String = "SELECT PrjName FROM OPRJ WHERE PrjCode = '" & sCode.Trim & "'"
    oRS.DoQuery(sSql)
    oRS.MoveFirst()
    While oRS.EoF = False
      sName = oRS.Fields.Item("PrjName").Value()
      Exit While
    End While
  Catch ex As Exception
  Finally
    If Not oRS Is Nothing Then
      System.Runtime.InteropServices.Marshal.ReleaseComObject(oRS)
      oRS = Nothing
    End If
  End Try
  Return sName
End Function

Note: Matrix_FetchDescriptions() have second argument (iRow As Integer) what may be used to indicate in what row you need to update PrjName column.

May be ItemManagment event when item pressed can be used... Try

HTH

BR

Sierdna S.

Former Member
0 Kudos

GREAT, i will try it!!

Thank you

Former Member
0 Kudos

Ok, I think you solve problem

Best regards

Sierdna S.

Former Member
0 Kudos

hey, i tried it like you wrote.

but when i want to assign a value to the cell with that:

oColumn = oMatrix.Columns.Item("ProjName");
                            oColumn.Editable = true;
                            oCell = oColumn.Cells.Item(i);
                            oText = (SAPbouiCOM.EditText)oCell.Specific;
                            oText.Value = sName;
                            oColumn.Editable = false;

it doesn't work. the oText.Value has no value after assigning. what can that be?

Former Member
0 Kudos

does the column need an userdbsource?

Former Member
0 Kudos

This is example of function what create form by loading xml file.

In my case it was a read-only column, only for code description.


<dbdatasources>
 <action type="add">
   <datasource tablename="@TABLE_NAME_HERE" /> 
  </action>
</dbdatasources>
<userdatasources>
 <action type="add">
  <datasource uid="uPrjName" type="9" size="250" /> 
  </action>
</userdatasources>

.. and matrix column ...


<column uid="ePrjName" type="16" title="Project Name" 
             description="Project Name" visible="1" AffectsFormMode="1" 
             width="150" disp_desc="0" editable="0" right_just="0" val_on="" 
             val_off="" backcolor="-1" forecolor="-1" text_style="0" font_size="-1">
  <databind databound="1" table="" alias="uPrjName" />
  <ExtendedObject />
</column>

In your case need to bind your column to dbdatasource.

... matrix column ...


<dbdatasources>
 <action type="add">
   <datasource tablename="@TABLE_NAME_HERE" /> 
  </action>
</dbdatasources>
<! ... -->
<column uid="ePrjName" type="16" title="Project Name" 
             description="Project Name" visible="1" AffectsFormMode="1" 
             width="150" disp_desc="0" editable="0" right_just="0" val_on="" 
             val_off="" backcolor="-1" forecolor="-1" text_style="0" font_size="-1">
  <databind databound="1" table="@TABLE_NAME_HERE" alias="uPrjName" />
  <ExtendedObject />
</column>

P.S.

Here example of CreateForm() function.


Private oDBDataSource As SAPbouiCOM.DBDataSource
...
Public Function CreateForm( _
      ByRef in_oApp As SAPbouiCOM.Application, _
      ByRef in_oCmp As SAPbobsCOM.Company _
) As SAPbouiCOM.Form
  Dim oForm As SAPbouiCOM.Form
  Try
    SBO_Application = in_oApp
    SBO_Company = in_oCmp
    ' Load form from xml
    Dim newUniqueID As String = "O01_" & Microsoft.VisualBasic.Format(DateTime.Now.Millisecond, "000")
    Dim sFormXmlFile As String = "MY_XML_FILE.xml"
    ReplaceUIDandLoadToB1(sFormXmlFile, newUniqueID)
    oForm = SBO_Application.Forms.Item(newUniqueID)

    ' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    oDBDataSource = oForm.DataSources.DBDataSources.Item("@TABLE_NAME_HERE")
    ' ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Dim oMatrix As SAPbouiCOM.Matrix
    Dim oItem As SAPbouiCOM.Item
    oItem = oForm.Items.Item("mtx00")
    oMatrix = oItem.Specific
    oMatrix.SelectionMode = SAPbouiCOM.BoMatrixSelect.ms_Single
    oMatrix.Clear()
    oDBDataSource.Query()
    oMatrix.LoadFromDataSource()
    'oMatrix.AutoResizeColumns()
    oItem = Nothing
    oMatrix = Nothing
    ' Call InitilizeForm(oForm)
  Catch ex As Exception
    ' log exception
    If Not oForm Is Nothing Then
      oForm.Close()
      oForm = Nothing
    End If
  Finally
    System.GC.Collect() 'Release the handle to the table 
  End Try
  Return oForm
End Function
...

Edited by: Sierdna S on Sep 24, 2008 2:32 PM

Answers (0)