Calculations on conditional value

[Jorge]

Hi,

I have a GridControl with three columns: A, B C

When the column C value is 1, then column B value must be 0, and must change his format.
It is solved by the following code in PrepareViewStyleInfo event:

If e.RowIndex > 0 And e.ColIndex = 2 Then
GridControl1.IgnoreReadOnly = True
If GridControl1(e.RowIndex, 3).FormattedText = "1" Then
e.Style.Text = 0
e.Style.ReadOnly = True
e.Style.BackColor = Color.AliceBlue
ElseIf GridControl1(e.RowIndex, 3).FormattedText = "0" Then
e.Style.ReadOnly = False
e.Style.BackColor = Color.White
End If
GridControl1.IgnoreReadOnly = False
End If



Nevertheless, though the value of B changes 0, the calculations on B do not change.

I add a sample project. (VB 2005)

Example:
1. In the combo, Choose Vegetables
2. Type 1 Tomatoes, 2 Onions (b2=2, b3=2)
3 Now, in the combo, change vegetables for fruits, (b2 and b3 changes to 0)
4 Type 2 apples, 3 pears (b4=2, b5=3)
5 Why total is 8 instead of 5?

How I can change this behavior?
I use Essential Studio 4.2.0.37

Best regards,

P.D. I do not speak english, forgive the mistakes of translation

Prueba.zip

[Administrator]

Hi Jorge,

The reason for getting this behavior is that you are using the PrepareViewStyleinfo event to set the CellValue format. The PrepareviewStyleInfo event does not store any styleInfo properties in a grid. It just set the visual apperence of the grid. If you want to store the data dynamically, you can use the grid.Model.QueryCellInfo event of the grid and set cellvalue using Style.CellValue. Please refer to the modified attached sample for more details and let me know if this helps.

[#VB.Net]

Private Sub GridModel_QueryCellInfo(ByVal sender As Object, ByVal e As Syncfusion.Windows.Forms.Grid.GridQueryCellInfoEventArgs)
If e.RowIndex > 0 And e.ColIndex = 2 Then
GridControl1.IgnoreReadOnly = True
'To avoid the Stack overflow exception you need to get the style property using Model between the remove and add GridQueryCellInfoEventHandler.
RemoveHandler GridControl1.Model.QueryCellInfo, New Syncfusion.Windows.Forms.Grid.GridQueryCellInfoEventHandler(AddressOf GridModel_QueryCellInfo)
Dim format As String = GridControl1(e.RowIndex, 3).FormattedText
AddHandler GridControl1.Model.QueryCellInfo, New Syncfusion.Windows.Forms.Grid.GridQueryCellInfoEventHandler(AddressOf GridModel_QueryCellInfo)
If format = "1" Then
e.Style.Text = 0
e.Style.ReadOnly = True
e.Style.BackColor = Color.AliceBlue
ElseIf format = "0" Then
e.Style.ReadOnly = False
e.Style.BackColor = Color.White
End If
GridControl1.IgnoreReadOnly = False
End If
End Sub

Sample : ModifiedPrueba.zip

Best regards,
Haneef

[Jorge]

Hi Haneef,

Thank you for your quickly answer, I believe that now I am orientated by the correct way, but the modified example has the same behavior that my example, I have noticed two things:


1) The column B values change to 0 (e.Style.Text = 0) when combo changes, but the typed values remain stored.

2) When you change the combo, the columnx C formulas are calculated automatically but the summary formula on column B do not (you need to change manually a value on B after that changes combo for recalculated it)

Thank you for you patience and help.
Best regards

[Administrator]

Hi Jorge,

Thanks for your update.

1) The column B values change to 0 (e.Style.Text = 0) when combo changes, but the typed values remain stored.
I am really sorry to inform you that I am not clear about your question.Can you please give me a more details regarding this issue?

2) When you change the combo, the columnx C formulas are calculated automatically but the summary formula on column B do not (you need to change manually a value on B after that changes combo for recalculated it)

>>>>>>>>>>
There are a couple of things you can do to force calculations in CurrentCellCloseDropDown event. To force any cell that depends on a particular cell to be recalculated, you can call the Engine.Refresh method. Here is some code the refreshes any cell depending on cell B1.

Dim model As Syncfusion.Windows.Forms.Grid.GridFormulaCellModel = Me.GridControl1.CellModels("FormulaCell")
model.Engine.Refresh("B2") ' recalc any cell depending on 2,2 

To force a particular cell to be recalculated, you can use code like

'force cell 5,2 to recalculate
Me.GridControl1(5,2).FormulaTag = Nothing
Me.GridControl1(5,2).GetFormattedText(Me.GridControl1(5,1).CellValue)

Best regards,
Haneef

[Jorge]

Hello Haneef,

Thank you again for your help.

"1) The column B values change to 0 (e.Style.Text = 0) when combo changes, but the typed values remain stored.
I am really sorry to inform you that I am not clear about your question.Can you please give me a more details regarding this issue?"

Maybe if I describe an example to you:
1. In the combo, choose "Vegetables"
2. In "Tomatoes" type 3 (b2=3)
3. In "Onions" type 2 (b3=2)
4. Changes the combo to "Fruits" -this step changes the numbers in tomatoes and onions (b2=0,b3=0)-
5. In "Apples" type 1 (b4=1)
6. In "Pears" type 5 (b5=4)
7. This changes the summary formula to "6", is correct.
8. Now, changes the combo to "Vegetables"

Issue A: tomatoes cell -b2- stores the value: 3, and onions cell -b3- stores the value: 2!. Why?

Issue B: The summary formula not are calculated when you exit from B1 (the combo cell) to any cell outside range B2:B6 . (B6 continues being 6, must to be 5)


Issue B comments:

* I need that all the formulae are calculated immediately after going out of every cell, because the formulas are elaborated in runtime and are not known in design time.

* The conditions of the formulas not always are combobox based formulas like in this example. Because of it I cannot use the CurrentCellCloseDropDown event.

I try to use the CurrentCellDeactivated event, but as you can see in the example that I attach , it does not work.

In advance I am grateful for your help, que tengas un buen dia.









Prueba(2).zip

[Administrator]

Hi Jorge,

Issue 1:
Try calling the e.Style.EndUpdate and BeginUpdate method in a Model. QueryCellInfo event of the grid and let me know if this helps.

Issue 2:

You can handle the CurrentCellMoving/ CurrentCellDeactivating event and call the RecalculateRange for whole grid. Here is a code snippet.

Private Sub GridControl1_CurrentCellDeactivating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles GridControl1.CurrentCellDeactivating
If GridControl1.CurrentCell.ColIndex = 2 And GridControl1.CurrentCell.RowIndex > 0 Then
Dim model As Syncfusion.Windows.Forms.Grid.GridFormulaCellModel = Me.GridControl1.CellModels("FormulaCell")
model.Engine.RecalculateRange(Syncfusion.Windows.Forms.Grid.GridRangeInfo.Table()) ' GridRangeInfo.Table() indicates the full grid range.
End If
GridControl1.Refresh()
End Sub

Please refer to the attached sample for implementation.
Prueba(2).zip

Best regards,
Haneef

[Jorge]

Thank you Haneef,

It's all that a I need

Best regards