According to the help formulas on bound columns are supported but I can't get it to work. http://help.infragistics.com/Help/NetAdvantage/WinForms/2011.1/CLR2.0/HTML/WinCalcManager_Formula_and_Reference_Guide.html
When I assign a formula to a bound column I get strange behaviour. When the source cell is changed via the underlying DataTable it's value displayed in the grid doesn't update - but it does redraw when the mouse hovers over the cell! But also the calculated formula value doesn't update at all. If I add an unbound column with the same formula this also doesn't update when the source cell changes. But if I remove the formula on the bound column the cell updates properly and the unbound column shows the calculated value. Note that if the user modifies the value in the cell directly then both the bound and unbound cells update correctly, so it's only when the update is through the datasource.
I really need to be able to use a bound column as I need a 1-1 mapping between the columns in the grid and the underlying datatable. I'm using infragistics v9.2
This is simply demonstrated by the following code, just drop it into a forms constructor. It creates a grid with a bound source column, a bound formula column and one unbound formula column. The calculated value depends on the first cell. It also adds a button that toggles the value of the first cell which should change the formula. Commenting out the line that assigns the formula to bound column 1 shows the unbound cell changing correctly.
Thanks, Martin
UltraGrid grid = new UltraGrid { Name = "grid", Dock = DockStyle.Fill }; grid.CalcManager = new UltraCalcManager(); Controls.Add(grid); DataTable dt = new DataTable(); dt.Columns.Add("col1", typeof(object)); dt.Columns.Add("col2", typeof(object)); dt.Rows.Add(new object[] { DBNull.Value, "" }); grid.DataSource = dt; grid.Rows.Band.Columns.Add("unbound"); grid.Rows.Band.Columns[1].Formula = "IF([col1] = \"\", \"Blank\", \"Value\")"; grid.Rows.Band.Columns[2].Formula = "IF([col1] = \"\", \"Blank\", \"Value\")"; UltraButton btn = new UltraButton() { Dock = DockStyle.Top, Text = "Toggle" }; Controls.Add(btn); btn.Click += (s, e) => dt.Rows[0][0] = dt.Rows[0][0] == DBNull.Value ? (object)1.0 : DBNull.Value;
Hi,
If you assign a Formula to any column, then the Formula calculation will overwrite any other value in the cell. There's no way to have a column with a Formula and also set the Value of the cell in the column to anything other than the formula result.
Generally speaking, you should always make a formula column readonly.
Hi Mike.
The column is only editable because that is the default on the grid in the small sample I put together. In my real use case I do indeed want the column to be read only.
In my post when I mentioned editing the cell manually I was referring to amending the source column that the formula depends on, not the formula column itself. If I do make the column read only I still get the same problem...
Thanks
Martin
Hi Martin,
marjones said:The column is only editable because that is the default on the grid in the small sample I put together. In my real use case I do indeed want the column to be read only.
Okay, that makes sense.
marjones said:In my post when I mentioned editing the cell manually I was referring to amending the source column that the formula depends on, not the formula column itself. If I do make the column read only I still get the same problem...
If you have a column in the grid that has a formula, then any changes you make in the grid or any other controls upon which that formula relies will cause the formula to be recalculated. This will, of course, blow away any value you assigned to the data source for that field.
There's no way for the grid to know that you don't want it to recalculate or update that field in the data source because you wrote to that field behind the grid's back, so to speak.
You could set the UltraCalcManager's CalcFrequency to Manual and that would give you the ability to choose when the calculations are performed. So maybe you just want to calculate the column once initially and then never recalculate it again after that and that would be a way to do it.
But by default, the calculations are performed continuously, updating the results any time something changes.
Sorry we still have crossed wires, I'll try to make myself clearer!
I am _not_ changing the cell value of the formula column, I am changing the source cell to which the formula refers, and the problem I am reporting is that the dependent formula cell doesn't update if it is a bound column. Instead it causes a display issue where the source cell doesn't display the new value until the mouse passes over the cell and the formula cell doesn't recalculate. I've tried forcing the calc manager to update and that also has no effect.
The behaviour you describe is exactly what I want to happen, but it does not work on bound columns.
- I don't ever want to change the formula cell manually.
- I want it to write the calculated value to the underlying data source, as is described in the help
- I want the calc manager to recalculate it immediately when the source values change.
If this is a bug in 9.2 that is fixed in 10.x then maybe this is a reason for me to upgrade, but I need confirmation of this first...
Thanks,
It all depends on what you are doing to update the grid. There's no built-in functionality in the grid to import from Excel.
So what is your grid's DataSource and how you are getting the values from Excel into the grid? The issue described here is a very specific and unusual, and there's nothing you described here that matches up with the original issue that was reported, so I'm not sure what you are talking about is the same thing.
Hi Mike,
Kind od similar problem for me. I have grid bound to BindingList<> and we have the capability to add new columns to the grid in run time( thru propertydescriptor).
Assume user adds Col A and Col B and set the formula for Col B = Col A /10 . When i update value for Col A in the cell , it recalculates correctly as per the formula in Col B BUT when i try to update underlying object in case of excel import , i get the updated values from excel into Col A BUT Col B doesn't recalculate.
Then i f i select some filter in COL B , the it recalculates. Please let me know if this is a known issue?
Valliappan
marjones said:I modified the test to use an UltraDataSource instead of a DataTableand everything seems to work fine - which I didn't expect as I thought the UltraDataRow would need updating afterwards for the same reasons.
My guess is that, unlike the DataTable, the UltraDataSource still sends ListChanged notifications even during an edit. That might be a bug in the UltraDataSource, but it's something we wouldn't change unless there's some compelling reason to do so, since it would be a breaking change.
marjones said:this still seems like a problem that could be avoided.
The only way that the grid could avoid this would be to automatically EndUpdate on the row every time a CalcManager calculation changed the value of a bound cell. This is certainly possible, but it would have to be optional - we couldn't just change this behavior of the grid, since it would be pretty inefficient and slow down the calculations quite a bit.
marjones said:I don't really want to do the workaround through the grid, the business logic is separated from the presentation layer and it doesn't know the grid that it is bound to when updating the row. However I can just call DataRow.EndEdit() instead after updating the value rather than UltraGridRow.Update().
Calling EndEdit immediately before making the changes to the data source seems like a good way to handle it.
Thanks for the information Mike, I appreciate infragistics taking the time to look into this for me.
ok I now understand what is going on here. So what we are saying here is that BeginEdit is called without a corresponding call to EndEdit - this still seems like a problem that could be avoided. I modified the test to use an UltraDataSource instead of a DataTableand everything seems to work fine - which I didn't expect as I thought the UltraDataRow would need updating afterwards for the same reasons.
I don't really want to do the workaround through the grid, the business logic is separated from the presentation layer and it doesn't know the grid that it is bound to when updating the row. However I can just call DataRow.EndEdit() instead after updating the value rather than UltraGridRow.Update().
So this is now sorted for me.
Many thanks,
I just wanted to let you know that we investigated this issue and it turns out that it's not a bug.
What's happening here is that since the formula column is bound, as soon as the formula gets calculated the first time, the row is marked dirty and the BindingManager calls BeginEdit on the row in the DataTable. So the DataTable is in a state where it has pending changes.
While it is in this state, either the DataTable or the BindingManager (or both) do not send notifications that a value in the data source has been changed. So the grid does not know the value in the data source was changed and it cannot update it's display.
A second potential solution is to commit the pending changes in the grid before you make the change to the data source. You would do this using grid.UpdateData (to commit all pending changes in the grid) or you could call Update on the individual row in the grid you want.
The latter is a bit trickier, since you have to find the row in the grid that corresponds to the row in the data source. This is not terribly difficult in a simple case like your sample here with a flat data source.
UltraGridRow gridRow = grid.Rows.GetRowWithListIndex(0);
But if you are going to get the grid row anyway, you might as well set the Value on the grid cell rather than committed the row and then updating the data source.