DI UI : fill matrix with a query from user table data

[Former Member]

hello,

I create a user table.

I want to fill a matrix on a form with some aggregated data of this user table.

I do :

Public Sub SetMatrixRep()

Dim oRecordSet As SAPbobsCOM.Recordset

Dim oCentre As SAPbouiCOM.EditText

Dim oCompte As SAPbouiCOM.EditText

Dim oBudget As SAPbouiCOM.EditText

Dim oDepense As SAPbouiCOM.EditText

Dim oSolde As SAPbouiCOM.EditText

Dim i As Long

i = 0

Set oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

oRecordSet.DoQuery ("select * from [@CAP_OPRC_BUDGET]")

While oRecordSet.EOF = False

' filling the matrix

Set oCentre = oMatrixRep.Columns("clCentre").Cells.Item(i).Specific

oCentre.String = oRecordSet.Fields.Item(0).Value

'oMatrixRep.Columns("clCentre").Cells(1).Specific.String = oRecordSet.Fields.Item(0).Value

'SBO_Application.MessageBox (oRecordSet.Fields.Item(0).Value)

oMatrixRep.AddRow

oRecordSet.MoveNext

i = i + 1

Wend

End Sub

I've got an error : Row - Index invalid.

Could you help me please ?

Thanks.

Romeo.

[Former Member]

Hi Lutz,

can u please tell me in which part if the market Place is post the PDF, I can find it.

Thank's

[Former Member]

Hi,

I have found it in the SMB portal, Solution Development, SAP Business One - Software Development Kit, Downloads, Tips and Tricks.

Regards,

Ibai Peñ

[Former Member]

Hi !

I'va tried to do this ( Thanks Lutz in fact I had to create a row before ! )

Dim oRecordSet As SAPbobsCOM.Recordset

Dim oCentre As SAPbouiCOM.EditText

Dim oCompte As SAPbouiCOM.EditText

Dim oBudget As SAPbouiCOM.EditText

Dim oDepense As SAPbouiCOM.EditText

Dim oSolde As SAPbouiCOM.EditText

Dim i As Long

i = 1

Set oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

oRecordSet.DoQuery ("select * from [@CAP_OPRC_BUDGET]")

While oRecordSet.EOF = False

' HERE ADD A ROW TO THE MATRIX

' Addrow (number of the row to be added, number next row)

oMatrixRep.AddRow i, i + 1

' filling the matrix

Set oCentre = oMatrixRep.Columns("clCentre").Cells.Item(i).Specific

oCentre.String = oRecordSet.Fields.Item(0).Value

' SBO_Application.MessageBox (oRecordSet.Fields.Item(0).Value)

oRecordSet.MoveNext

i = i + 1

Wend

Thanks,

Romeo.

[Former Member]

hello,

thanks for answer.

But Giovani how do you proceed if your select query is like that :

select count(*), field1 from table1 group by field1

Is there a solution with Condition Objects ?

regards,

Romeo.

[Former Member]

Hi,

if you want to fill a matrix using an edittext (or other control) property, you need to add a row first. Then you can access the cells of this row. This does make sense, too. You cannot write in a row which does not exist yet. This is why you get the exception.

If you use the userdatasource.value property (as recommended by SAP), you first fill the value properties and then add a row as one would expect.

HTH Lutz Morrien

[Former Member]

Hi,

you say that SAP recommends to use a userdatasource. Where does it? Any link about this would be appreciated.

If there isn´t any related link, could you explain why does recommend it with a simple sample?

Thanks,

Ibai Peñ

[Former Member]

Ibai,

SAP has published a pdf file in SAP Portal. It is called something like "Performance issues when filling a matrix". Take a look at it.

I'll see if i can get a hold of the real title.

I tried both variants (filling by control and filling by userdatasource). I could not see a lot of difference between the two performancewise. (For some reason, they both are really slow)

However, filling by userdatasource seems to make more sense to me. You fill the datasource and then add the row.

HTH Lutz Morrien

[Former Member]

Hi

This is a routine that I call from my Item Event handler

Hope it can help you

Public Sub PopolaMatrice(oApplicazione As SAPbouiCOM.Application, pVal As SAPbouiCOM.IItemEvent)

Dim oForm As SAPbouiCOM.Form

Dim oMatrix As SAPbouiCOM.Matrix

Dim oDBDataSource As SAPbouiCOM.DBDataSource

Dim oConditions As New SAPbouiCOM.Conditions

Dim oCondition As SAPbouiCOM.Condition

Dim oDocNum As SAPbouiCOM.EditText

Dim lIndice As Long

Set oForm = oApplicazione.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount)

Set oDBDataSource = oForm.DataSources.DBDataSources.Item("MyUserTableName")

Set oMatrix = oForm.Items("MyMatrixName").Specific

oMatrix.Clear

'setting the condition object

'this is equal to the following SQL statement

'WHERE UserFieldName1 = "MyCriteria1" and UserFieldName2 = "MyCriteria2"

Set oCondition = oConditions.Add()

oCondition.BracketOpenNum = 2

oCondition.Alias = "UserFieldName1"

oCondition.Operation = co_EQUAL

oCondition.CondVal = "MyCriteria1"

oCondition.BracketCloseNum = 1

oCondition.Relationship = cr_AND

Set oCondition = oConditions.Add()

oCondition.BracketOpenNum = 1

oCondition.Alias = "UserFieldName2"

oCondition.Operation = co_EQUAL

oCondition.CondVal ="MyCriteria2"

oCondition.BracketCloseNum = 2

oDBDataSource.Query oConditions

'filling the matrix

For lIndice = 0 To oDBDataSource.Size - 1

oDBDataSource.Offset = lIndice

oMatrix.AddRow

Next lIndice

End Sub

[Former Member]

Sorry, never performed a query like that with a condition object, I'm just a beginner

[Former Member]

Hi again (still waiting for any answer to my previous post),

I have had same problem with the Query object. It seems that is not possible to use order or group functionallity with it. Does anybody have another solution for this?

Regards,

Ibai Peñ