VFP Grid.Optimize Gotcha

A new optimize property was added to grid's in VFP9. When this property is set to true, the grid uses Rushmore optimization. For backward compatability, this property defaults to false in the VFP grid base class. Seeing as Rushmore optimization is a good thing, we set it to true in the cGrid class in VFE which is the base grid class in the framework.

We've been shipping VFE this way since August and it hasn't seemed to be an issue - except for one customer. This customer has been consistently getting a "Uniqueness of index xxxx is violated" error whenever the user enters a duplicate value in a textbox that's bound to a primary or candidate key AND there's a grid bound to the same table is also visible.

I've been looking all over for changes we've made to framework code, as there's been a fair amount of work done in grids recently, and it turns out, it's just a simple property setting. This situation only occurs when the user is doing data entry directly into tables (as opposed to views), the user can edit a primary or candidate key and there's a grid that's visible at the same time. When the user exits the control (generally a textbox) associated with the primary or candidate key and VFP updates it, it also tries to refresh the grid at that point in time and this is when the error occurs. What's really confusing about the whole thing is that no events fire that you can see, so what exactly is happening at this point in time took a while to unravel. At any rate, the moral of the story is that if you've got primary or candidate keys that the user can enter, you bind directly to table fields and you display this data in a grid, it's probably best to set the optimize property of the grid to false. If this is how you build your VFE apps, it'd be wise to set this property to false in iGrid of iContrls.

Oh, and BTW, if you do RTFM, there's a note in the doc for the optimize property that explains this:

As with any data manipulation command that can impact Rushmore optimization, you should be aware that actions taken against the Grid's data source could result in behavior that differs from when the Optimize property is set to False (.F.). For example, Rushmore optimization might trigger an implicit TABLEUPDATE to occur for a row buffered cursor. And an error may or may not occur depending on whether the record is blank or whether the cursor is a view.

Now that I've encountered this scenario, I actually understand what the note refers to!

I lost some hair on this one, hopefully this'll save somebody else from early baldness.