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,
Okay... so let me see if I have this right.
Let's say you have 2 columns, A, and B. Both columns are bound.
Column B has a Formula which refers to Column A.
What you are saying is that you are changing the value of a cell in column B by setting the value in the data source directly (not via the grid). And when you do this, Column A is not displaying the new cell value until you mouse over that cell and since Column A is not getting the new value, Column B is not recalculating.
If that's what you are saying, then it doesn't sounds to me like the formula has anything to do with it, so that might just be confusing the issue.
If you update a field in your data source (whether that field is the source of a formula or not) the grid should be updating it's display to show the new value.
If that's not happening, then clearly something is wrong.
There are several reasons why this might happen:
1) Your DataSource is an IList instead of an IBindingList. IList is not a robust interface for binding and it doesn't send notifications for many types of changes to the data or the data source. Since the sample code you posted in your original post is using a DataTable and DataTable implements IBindingList, this is probably not the problem.
2) Something about the way in which you are updating the DataSource value is causing the data source not to send a notification, even though it's an IBindingList. Again, the sample code you posted in your original post is applying a value to the DataRow directly, so I don't see any problems there.
3) This is a bug in the version of the grid you are using.
Since #3 seems to be the most likely explanation at this point, I think your best bet is to try to verify that.
As I mentioned above, I doubt the formula has anything do with this. But... just as a test, it might be interesting to remove the Formula(s) from the columns and see if the cell updates it's display without any Formulas applied.
Either way, in order to verify that this is a bug and that it's fixed, it's best for us to try the same sample project with both versions. So I recommend that you try to duplicate the problem in a small sample project and post it here. I can then try it out and see if I can reproduce the problem in any version.
An alternative is for you to download a trial version of NetAdvantage 11.1 (the latest) and try it yourself. Or, you could just download the latest service release for v9.2 (which is free) and try that.
How to get the latest service release - Infragistics Community
Ok so I downloaded the trial version of 11.1 and it still exhibits exactly the same problem.
This time I've provided a full project rather than just the self contained constructor code. Now I create two grids, which both have a bound and unbound column and the only difference is that on one grid the bound column has a formula and the other one doesn't.
Clicking the bound button at the top of the form modifies the source cell on the datatable of both grids. You can see that the grid with only the unbound formula updates correctly and the other one displays the behaviour I have described before.
Thanks for your help.
Martin.
oops I didn't mean to confuse things by saying 'bound button', instead I just meant to say that it was docked.
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.