Adjusting formulas on row insert

[barcode]

Hello,

I programmatically insert new rows and columns through the following code

GridControl1.Model.Rows.InsertRange(1, 1)

I have a formula referencing a cell below the inserted row, i.e. (=B3)

The B3 is not adjusting to B4. Is there any property I must set to force this to occur?

Thanks

Nick

[haneefm]

Hi Nick,

You can try setting FormulaCopyFlags property to tell the grid to adjust references in different situations.

GridFormulaEngine engine = ((GridFormulaCellModel)grid.Model.CellModels["FormulaCell"]).Engine;
engine.FormulaCopyFlags = GridFormulaCopyFlags.ClipBoardReferencesAdjusted | GridFormulaCopyFlags.InsDelRangeReferencesUpdated;

Please refer this sample that show what are the things that are supported: (all the FormulaCopyFlags)
http://www.syncfusion.com/Support/user/uploads/GridFormulaCells_1786a6a5.zip

Best regards,
Haneef

[barcode]

Thank you for the quick response and sample code Haneef.

I seem to be having a problem when processing multiple inserts with beginUpdate and endUpdate. It seems to adjust the formula for the first insert but neglects any other insert. Below is the code I use:

Dim gm As GridModel = GridControl1.Model

Dim formulaModel As GridFormulaCellModel = gm.CellModels.Item("FormulaCell")
Dim engine As GridFormulaEngine = formulaModel.Engine
engine.FormulaCopyFlags = engine.FormulaCopyFlags Or GridFormulaCopyFlags.InsDelRangeReferencesUpdated

gm.BeginUpdate()
Me.GridControl1.Model.Rows.InsertRange(1, 1)
Me.GridControl1.Model.Rows.InsertRange(2, 1)
gm.EndUpdate()The result would shift a formula =B5 to =B6 but not B7.

I am running version 4.3.0.30. Is this problem present in the current release or is there a work around?

Thank you for the help.

Nick

[Administrator]

Locking the drawing is preventing some of the events that the formulaengine listens to from being raised on the two sequential calls to InsertRows. And this is causing the engine to not be able to update things properly.

Here are some things that worked for me to avoid this problem.

1) Remove the grid.BeginUpdate / grid.EndUpdate calls. (Then all the events are raised and the references get updated.

2) End the update immediately after the InsertRows call, and then restart it.

 GridControl1.BeginUpdate()
        Me.GridControl1.Model.Rows.InsertRange(1, 1)
        GridControl1.EndUpdate()
        GridControl1.BeginUpdate()
        Me.GridControl1.Model.Rows.InsertRange(2, 1)
        GridControl1.EndUpdate()

3) Instead of sequentially inserting the 2 rows, insert them in one call.

 GridControl1.BeginUpdate()
        Me.GridControl1.Model.Rows.InsertRange(1, 2)
        GridControl1.EndUpdate()

This problem is also in the latest code base.

[barcode]

Thank you for the work arounds.

My code is doing far more than the example posted. Normally rows will not be inserted seqentially but in a scattered fashion in different points in the grid. i.e insert at row 10, 5 lines, insert at column 5 lines 3, etc.

There are many operations occuring besides row and column inserts (such as shifting and applying values and formatting) and efficency is a big issue. The begin and end update statements are used for efficency purposes. I open and close the begin and end after all my operations have occurred and refresh only the parts on the grid which changed. Everything is shifted correctly on the grid but the formulas are not adjusting. Option 2 seems like a possible work around.

Are there plans to fix this bug or should I submit it to direct-trac?

Thank you for the help.

Nick

[Administrator]

The problem lies with an optimization line code in our formula engine. The line checks if the affected cell is visible, and if it is, does not actually force the change. This prevents some blinking on the visible cells and speeds things up a little as the cells are automatically updated as they are drawn due to the insert or insert. So, only non-visible affected cells are 'forced' to be adjusted. Well, when you are in a BeginUpdate block, the visible affected cells are not automatically adjusted as they are not automatically redrawn. The fix for this problem would be to ignore this optimization if the grid.Updating property is set true. This will require a code change in our library that we will make. It should be available in the next release. If you want to track this defect process, submit a Direct Trac support incident referring to this forum post.

Until there is a release with this code change, calling the EndUpdate/BeginUpate immediately after the Insert should avoid this problem.

[barcode]

Very good to hear. Thank you for all the information and all your help.

[barcode]

Sorry to repoen the topic but I am having trouble closing all the BeginUpdate statements. I comment out every BeginUpdate and EndUpdate method calls I can find and still encounter the same issue. Is there any other code which may cause the events to be surpressed? Is there any way to manually call the surpressed events which prevent formula recalculation after each insert and remove?

Thank you.

Nick

[Administrator]

What version of our libraries are you using? Is it 5.2.0.25, the latest version? There were problems with cell references updating in earlier versions, but we thought they were fixed by 5.x.

I do not have any specific ideas about what to look for. Essentially, style.GetFormattedtext has to be triggerred between every Insert/Remove call. Calling grid.BeginUpdate interferes with process, and that was what was causing the problem in the sample you provided. What else are you doing at the point you are inserting/deleting rows/columns?