I have a simple edit form with 3 fields:

InvoiceAmt, TaxRate, TaxOwing

The TaxOwing is a Computed column using the column values in InvoiceAmt and TaxRate, so it is a TDBText control on the form (display only).

The InvoiceAmt and TaxRate are both TDBEdit controls so the user can change these values. When either of these control values change, the underlying TaxOwing table field value is changed (verified when leaving either of these edit fields), BUT the corresponding TDBText control for TaxOwing is NOT updated with the newly computed value until the row is saved.

Why? I thought the TDBText controls (and other db controls) would be automatically updated when the underlying table field value changes, as it did with BDE and DBISAM.

What am I missing???

TIA
Barry
v2.09 & XE2

Barry


To the best of my memory DBISAM and the BDE didn't have COMPUTED columns. I think you're confusing COMPUTED columns with calculated fields. One is an SQL construct and the other a VCL one.

I'm guessing here but I'd say that the COMPUTED column can only be updated when the database knows about the change which is when the data is posted. A calculated field can be refreshed when the application knows about the change ie when you exit the control you've been editing in.

Think of the potential for confusion if a COMPUTED column was updated when you exited the control and someone on a different PC was running a query using the COMPUTED column.

Roy Lambert [Team Elevate]

Barry,

> I have a simple edit form with 3 fields:

In addition to Roy's answer:

Actually a control which is bound to a computed field would refresh when
it gets the focus (you can try that with a DBEdit control) without
saving changes to the table.
Since you are using a DBText control, that doesn't help.

That's different from a generated field which refreshes only after an
update has been posted.

Uli

Roy Lambert wrote:

>>To the best of my memory DBISAM and the BDE didn't have COMPUTED columns. I think you're confusing >>COMPUTED columns with calculated fields. One is an SQL construct and the other a VCL one.

Correct. I was hoping the Computed column would update the Controls the same way it would using Delphi code like Table1.FieldByName("TaxOwing").AsFloat := Table1.FieldByName("InvoiceAmt").AsFloat * Table1.FieldByName('TaxRate').AsFloat. When this Delphi code is executed after exiting either InvoiceAmt or TaxRate TDBEdit controls, then the TaxOwing TDBText is automatically refreshed with the new value. But this isn't the case with an EDB Computed column even though the FieldByName('Tax_Owing') Computed value DOES get updated automatically when exiting the TDBEdit controls.  

I consider this a flaw (either that or I am doing something wrong). Imagine if someone was was creating an invoice program and none of the calculations or subtotal controls (based on COMPUTED columns) were being updated on the form until AFTER the Post. So you would have to Post to see the subtotal, re-edit to add more items, post again to see the Computed columns re-calculated, then re-edit etc..

>>I'm guessing here but I'd say that the COMPUTED column can only be updated when the database knows >>about the change which is when the data is posted. A calculated field can be refreshed when the application >>knows about the change ie when you exit the control you've been editing in.

I'd be happy if it updated TaxOwing TDBText field when either of the TDBEdit controls were exited. But it is not doing that. The only time the TDBEdit control gets updated is after the Post.

>>Think of the potential for confusion if a COMPUTED column was updated when you exited the control and >>someone on a different PC was running a query using the COMPUTED column.

Not sure I follow. When I say updated, I'm referring to the window Control, not the database record. My problem is the window controls are not being updated with the new value in the Computed column. The Computed column DOES HAVE the new value when exiting the TDBEdit controls, it just won't display it in the TDBText control.

Barry

Uli Becker wrote:

>>In addition to Roy's answer:

>>Actually a control which is bound to a computed field would refresh when
>>it gets the focus (you can try that with a DBEdit control) without
>>saving changes to the table.
>>Since you are using a DBText control, that doesn't help.

Correct. It should still refresh. Even TDBText.Refresh; does not cause it to display the new value. I am stuck with the old value on the screen until the post.

>>That's different from a generated field which refreshes only after an
>>update has been posted.

Correct. The Computed column is behaving like a Generated column if it is attached to a TDBText control. I don't think it should. Especially when the Computed column has the correct value, there is no way to get it to display it in a TDBText field. At least I haven't found a way to do it.

The only solution I have come up with is to switch the TDBText controls with TLabel and update the TLabel with the value of the computed column as in:

lbTaxOwing.Caption := Table1.FieldByName('TaxOwing').AsString;

This of course means adding another layer of code to handle displaying Computed columns on a form. BTW, I don't think grids have this problem with Computed columns. The Computed value updates immediately in a grid.

I was hoping someone had some ideas to get the Computed column to refresh the TDBText control.

Barry

Barry

>Not sure I follow. When I say updated, I'm referring to the window Control, not the database record. My problem is the window controls are not being updated with the new value in the Computed column. The Computed column DOES HAVE the new value when exiting the TDBEdit controls, it just won't display it in the TDBText control.

So how do you know its updated?

I'm guessing that you do something like call showmessage(field.asstring). If so my take would be that by accessing the data the computation was updated so try TDBEdit.Refresh for the control displaying the computed column.

Roy Lambert [Team Elevate]

Hi Roy,
  Working Sunday too I see.

>>So how do you know its updated?

A couple of ways.

1) Setting a breakpoint on TDBEdit.OnExit for either of the 2 controls used in the calculation, and surprisingly the Computed TField.Value is updated at this point, which is great. Works as expected.

2) I can update the window caption with the Computed column when exiting the TDBEdit controls and the caption has the correct Computed value, which is also great. Works as expected.

>>I'm guessing that you do something like call showmessage(field.asstring). If so my take would be that by >>accessing the data the computation was updated so try TDBEdit.Refresh for the control displaying the >>computed column.

Again, the Computed field gets updated perfectly without any assistance from me. It is just the TDBText control is pretty much brain dead as to displaying the new Computed value. Either EDB doesn't send an update command to the TDBText control, or the TDBText control is just clueless.

As far as executing TDBText.Refresh/Update on the Computed column control, I have done that ad nausem and still has no effect. If I could manually refresh the TDBText controls I'd be somewhat happier. But again the TDBText control is pretty much brain dead.

I think I may have to switch the TDBText to TDBEdit and colorize it and make it read-only so user knows not to try and edit it. The TDBEdit should get automatically refreshed when the Computed value changes. I will post back later on this.

I've only been using EDB for a couple of weeks and thought you guys would have displayed Computed columns on forms and already encountered this problem with TDBText controls not being updated.

This may be more of a TDBText control problem than EDB problem. I'm not sure at this point.

Barry
Apparently the bugs stop here.

Barry

I've been having a bit of a play with this and as you've found computed columns are only updated when the control (or in the case of a DBGrid the cell) gains focus or the record is posted.

I suggest emailing Tim because I think he said there'll be an update out soon for a bug fix.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

>>I've been having a bit of a play with this and as you've found computed columns are only updated when the control >>(or in the case of a DBGrid the cell) gains focus or the record is posted.

Roy,
   That's not really what is happening. The Computed column get updated immediately when any of the columns that make up the Computed column changes, which is exactly as it should be. So the database table always has the correct calculations.  The problem is the Delphi control attached to the Computed column do not update until they receive focus. And if it is a TdbText control that can't receive focus, it won't update until after the Post. So there is a broken "connection" between the visual control and the Computed column.

I "solved" the problem by replacing the TdbText controls with TLabel and changed the TLabel caption when any of the fields that make up the Computed column changed. This is a PIA but the only solution I could think of.

>>I suggest emailing Tim because I think he said there'll be an update out soon for a bug fix.
Good idea. I'll see what the master has to say.

Barry

Barry

> That's not really what is happening. The Computed column get updated immediately when any of the columns that make up the Computed column changes, which is exactly as it should be. So the database table always has the correct calculations. The problem is the Delphi control attached to the Computed column do not update until they receive focus. And if it is a TdbText control that can't receive focus, it won't update until after the Post. So there is a broken "connection" between the visual control and the Computed column.

Having just (I hope, for the second time) finished sorting out my own TDataset descendent I can really appreciate how bloody difficult it is to get it all to work. I believe TDataset is responsible for notifying all the controls to update themselves but that (I think) relies on the correct dataevent getting fired so that might be where some work is needed.


>I "solved" the problem by replacing the TdbText controls with TLabel and changed the TLabel caption when any of the fields that make up the Computed column changed. This is a PIA but the only solution I could think of.

That would certainly work.

Roy Lambert [Team Elevate]