on 09-24-2008 9:16 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.