* * *

Author Topic: calculated fields, master/detail calculation sequence  (Read 778 times)

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com
calculated fields, master/detail calculation sequence
« on: May 26, 2018, 12:57:55 pm »
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

  • Hero Member
  • *****
  • Posts: 6527
Re: calculated fields, master/detail calculation sequence
« Reply #1 on: May 26, 2018, 01:26:42 pm »
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)
« Last Edit: May 26, 2018, 01:50:41 pm by Thaddy »
Ada's daddy wrote this:"Fools are my theme, let satire be my song."

valdir.marcos

  • Sr. Member
  • ****
  • Posts: 415
Re: calculated fields, master/detail calculation sequence
« Reply #2 on: May 26, 2018, 06:58:17 pm »
@mobilevil, Thaddy is right.

Follow an empty hypothetical example.
Image is attached.

Code: Pascal  [Select]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
  9.   Graphics, Dialogs, StdCtrls, Grids;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     Button1_SaveAndExit: TButton;
  17.     Button2_ExitWithoutSaving: TButton;
  18.     Button3_NewDetailRecord: TButton;
  19.     Button4_Save: TButton;
  20.     Button5_Cancel: TButton;
  21.     Button6_DeleteDetailRecord: TButton;
  22.     DataSource1_Master: TDataSource;
  23.     DataSource2_Detail: TDataSource;
  24.     Edit1_Date: TEdit;
  25.     Edit2_CustomerName: TEdit;
  26.     Edit3_InvoiceNumber: TEdit;
  27.     Edit4_InvoiceValue: TEdit;
  28.     GroupBox1: TGroupBox;
  29.     GroupBox2: TGroupBox;
  30.     IBConnection1: TIBConnection;
  31.     Label1: TLabel;
  32.     Label2: TLabel;
  33.     Label3: TLabel;
  34.     Label4: TLabel;
  35.     SQLQuery1_Master: TSQLQuery;
  36.     SQLQuery2_Detail: TSQLQuery;
  37.     SQLTransaction1: TSQLTransaction;
  38.     StringGrid1: TStringGrid;
  39.     procedure Button1_SaveAndExitClick(Sender: TObject);
  40.     procedure Button2_ExitWithoutSavingClick(Sender: TObject);
  41.     procedure Button3_NewDetailRecordClick(Sender: TObject);
  42.     procedure Button4_SaveClick(Sender: TObject);
  43.     procedure Button5_CancelClick(Sender: TObject);
  44.     procedure Button6_DeleteDetailRecordClick(Sender: TObject);
  45.     procedure FormCreate(Sender: TObject);
  46.   private
  47.  
  48.   public
  49.  
  50.   end;
  51.  
  52. var
  53.   Form1: TForm1;
  54.  
  55. implementation
  56.  
  57. {$R *.lfm}
  58.  
  59. { TForm1 }
  60.  
  61. procedure TForm1.FormCreate(Sender: TObject);
  62. begin
  63.   // open dtabase and datasets
  64.   // check all basic information
  65.  
  66.   // new invoice:
  67.   Edit4_InvoiceValue.Text := '0';
  68.  
  69.   // existing invoice:
  70.   // Edit4_InvoiceValue.Text := FloatToStr(StoredProcedureReturnValue());
  71. end;
  72.  
  73. procedure TForm1.Button1_SaveAndExitClick(Sender: TObject);
  74. begin
  75.   // all information is already calculated,
  76.   // just save them to database
  77. end;
  78.  
  79. procedure TForm1.Button2_ExitWithoutSavingClick(Sender: TObject);
  80. begin
  81.   // cancel everything and exit
  82. end;
  83.  
  84. procedure TForm1.Button3_NewDetailRecordClick(Sender: TObject);
  85. begin
  86.   SQLQuery2_Detail.Append;
  87. end;
  88.  
  89. procedure TForm1.Button4_SaveClick(Sender: TObject);
  90. begin
  91.   SQLQuery2_Detail.FieldByName('TotalValue').AsFloat := SQLQuery2_Detail.FieldByName('Quantity').AsFloat  *  SQLQuery2_Detail.FieldByName('UnitValue').AsFloat;
  92.   Edit4_InvoiceValue.Text := FloatToStr(StrToFloat(Edit4_InvoiceValue.Text) + SQLQuery2_Detail.FieldByName('TotalValue').AsFloat);
  93.   SQLQuery2_Detail.Post;
  94.   SQLQuery2_Detail.ApplyUpdates;
  95.   SQLTransaction1.Commit;
  96.   SQLQuery2_Detail.Open;
  97.   SQLQuery2_Detail.Last;
  98. end;
  99.  
  100. procedure TForm1.Button5_CancelClick(Sender: TObject);
  101. begin
  102.   SQLQuery2_Detail.Cancel;
  103. end;
  104.  
  105. procedure TForm1.Button6_DeleteDetailRecordClick(Sender: TObject);
  106. begin
  107.   Edit4_InvoiceValue.Text := FloatToStr(StrToFloat(Edit4_InvoiceValue.Text) - SQLQuery2_Detail.FieldByName('TotalValue').AsFloat);
  108.   SQLQuery2_Detail.Delete;
  109.   SQLQuery2_Detail.ApplyUpdates;
  110.   SQLTransaction1.Commit;
  111.   SQLQuery2_Detail.Open;
  112.   SQLQuery2_Detail.Last;
  113. end;
  114.  
  115. end.
« Last Edit: May 26, 2018, 07:08:32 pm by valdir.marcos »

valdir.marcos

  • Sr. Member
  • ****
  • Posts: 415
Re: calculated fields, master/detail calculation sequence
« Reply #3 on: May 26, 2018, 07:02:10 pm »
There was not enough free space...
Follow the dummy project attached.
« Last Edit: May 26, 2018, 07:11:32 pm by valdir.marcos »

mobilevil

  • Jr. Member
  • **
  • Posts: 54
    • http://www.kachun.com
Re: calculated fields, master/detail calculation sequence
« Reply #4 on: May 28, 2018, 05:37:26 am »
Thanks! I think my work around is fine so far so I will leave it as is and move forward....

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus