calculated fields, master/detail calculation sequence
lazarus / 16-Databases

----------------------------------------------------------------------
mobilevil · 2018-05-26 10:57

hello,
it may sound stupid but i want to put some table data with a local
calculated field in a grid,
but in order to calculate the field in the master table, i need some
value from the detail table.

in the on calc event of the master table, the value of the field from
the detail table is always 0. seems the detail table has no chance to
do the update yet.

currently i am working around by copying all the rows from the master
table to the memtable, and do the calculation along the way.

is there any way to force the detail table to do the update? something
like the Application.ProcessMessages for the data layer.

thanks

----------------------------------------------------------------------
Thaddy · 2018-05-26 11:26

Calculated fields are not stored fields. It requires by simple logic
to have the query on the stored fields to be available first.
Also, by proxy, calculated fields are LOCAL fields, so queries that
include them need to be processed locally, not server side.
An alternative to solve your issue is probably a combination of one
stored procedure and triggers on the children.
So basically: usually let the query on the actual data finish and THEN
perform the calculations, present with TBufDataset (for intermediate
programmers).
Otherwise use the former scenario. Stored procedure and triggers.
(That's the professional option, but requires more knowledge, look up
views)

----------------------------------------------------------------------
valdir.marcos · 2018-05-26 16:58

@mobilevil, Thaddy is right.

Follow an empty hypothetical example.
Image is attached.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
IBConnection, sqldb, db, FileUtil, Forms, Controls,  Graphics,
Dialogs, StdCtrls, Grids; type   { TForm1 }   TForm1 = class(TForm)  
Button1_SaveAndExit: TButton;    Button2_ExitWithoutSaving: TButton; 
 Button3_NewDetailRecord: TButton;    Button4_Save: TButton;   
Button5_Cancel: TButton;    Button6_DeleteDetailRecord: TButton;   
DataSource1_Master: TDataSource;    DataSource2_Detail: TDataSource; 
 Edit1_Date: TEdit;    Edit2_CustomerName: TEdit;   
Edit3_InvoiceNumber: TEdit;    Edit4_InvoiceValue: TEdit;   
GroupBox1: TGroupBox;    GroupBox2: TGroupBox;    IBConnection1:
TIBConnection;    Label1: TLabel;    Label2: TLabel;    Label3:
TLabel;    Label4: TLabel;    SQLQuery1_Master: TSQLQuery;   
SQLQuery2_Detail: TSQLQuery;    SQLTransaction1: TSQLTransaction;   
StringGrid1: TStringGrid;    procedure
Button1_SaveAndExitClick(Sender: TObject);    procedure
Button2_ExitWithoutSavingClick(Sender: TObject);    procedure
Button3_NewDetailRecordClick(Sender: TObject);    procedure
Button4_SaveClick(Sender: TObject);    procedure
Button5_CancelClick(Sender: TObject);    procedure
Button6_DeleteDetailRecordClick(Sender: TObject);    procedure
FormCreate(Sender: TObject);  private   public   end; var  Form1:
TForm1; implementation {$R *.lfm} { TForm1 } procedure
TForm1.FormCreate(Sender: TObject);begin  // open dtabase and
datasets  // check all basic information   // new invoice: 
Edit4_InvoiceValue.Text := '0';   // existing invoice:  //
Edit4_InvoiceValue.Text :=
FloatToStr(StoredProcedureReturnValue());end; procedure
TForm1.Button1_SaveAndExitClick(Sender: TObject);begin  // all
information is already calculated,  // just save them to
databaseend; procedure TForm1.Button2_ExitWithoutSavingClick(Sender:
TObject);begin  // cancel everything and exitend; procedure
TForm1.Button3_NewDetailRecordClick(Sender: TObject);begin 
SQLQuery2_Detail.Append;end; procedure
TForm1.Button4_SaveClick(Sender: TObject);begin 
SQLQuery2_Detail.FieldByName('TotalValue').AsFloat :=
SQLQuery2_Detail.FieldByName('Quantity').AsFloat  *
 SQLQuery2_Detail.FieldByName('UnitValue').AsFloat; 
Edit4_InvoiceValue.Text :=
FloatToStr(StrToFloat(Edit4_InvoiceValue.Text) +
SQLQuery2_Detail.FieldByName('TotalValue').AsFloat); 
SQLQuery2_Detail.Post;  SQLQuery2_Detail.ApplyUpdates; 
SQLTransaction1.Commit;  SQLQuery2_Detail.Open; 
SQLQuery2_Detail.Last;end; procedure
TForm1.Button5_CancelClick(Sender: TObject);begin 
SQLQuery2_Detail.Cancel;end; procedure
TForm1.Button6_DeleteDetailRecordClick(Sender: TObject);begin 
Edit4_InvoiceValue.Text :=
FloatToStr(StrToFloat(Edit4_InvoiceValue.Text) -
SQLQuery2_Detail.FieldByName('TotalValue').AsFloat); 
SQLQuery2_Detail.Delete;  SQLQuery2_Detail.ApplyUpdates; 
SQLTransaction1.Commit;  SQLQuery2_Detail.Open; 
SQLQuery2_Detail.Last;end; end.

----------------------------------------------------------------------
valdir.marcos · 2018-05-26 17:02

There was not enough free space...
Follow the dummy project attached.

----------------------------------------------------------------------
mobilevil · 2018-05-28 03:37

Thanks! I think my work around is fine so far so I will leave it as is
and move forward....