Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Firedac Aggregate field Use for Master Detail calculation


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Feb 24, 2017 11:08 PM Last Post By: Dmitry Arefiev
Eddie Muniz

Posts: 12
Registered: 8/11/16
Firedac Aggregate field Use for Master Detail calculation  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2017 5:00 AM
I am new to Delphi, using Berlin. Just started building some database applications using Firedac and some Clientdataset and also Firedac cached updates and really enjoying it. I come from the Dbase, Clipper world and Visual Dbase. This has been a big jump for me despite 30 years of coding experience but like I said I am having a lot of fun with it.

Every so often I run into a bit of a wall on how to do things, not so much how to because I can do what I am about to ask about, but my method is probably antiquated and a much better tool or method exist.

Its very simple, I am trying to maintain my Totals on my Invoices and using the line items of the invoice in the detail file. Of course I have my Master/Detail setup and it works great, Its fully operational. The question comes with how to maintain those totals and whats best practice ?? I can manually calc invoice total based on the items changes each time it happens depending on Add.Edit.Delete . So I researched a bit and found aggregate fields. I know I can use them to perform this operation. I currently use a calculated field in the invoice detail file to show the total of Qty Ordered and Unit Price. Now I would like to get the invoice total to maintain without the need for all the manual coding and I feel that the Aggregate field is the way but cannot for the life of me get it to work nor can I find any kind of clear guidance or example.

From what I know I set the aggregate field in the master table (INVOICE MAIN), I need to set the Aggregates to TRUE, but this is where I am not sure how to handle the rest of the process. Is the aggregate field set at the master table or the detail query ? I assume its on the master table so that can then use that Taggregate field to display the total of the invoice and then for calculations of balance as well. I am bit stumped here and probably very simple solution.

I searched the forum for aggregates but did not find anything, your help and advise would be greatly appreciated.

Advice includes not using Taggregates for what I am trying to accomplish and just stay with the calculation based on events.

(EDIT)Doing more research I want to add that now I see that FDQuery has Aggregates property as well that is not the Field definition. Again best practices is what I am looking for and some guidance on how to set that up for a M/D Invoice, Invoice Items scenrario.

THANK YOU !!

(EDIT)
Here is a snippet of code that I use on the FormShow Method of the invoice editing form. When I run I am getting an error

procedure TfrmInvoiceEdit.FormShow(Sender: TObject);
begin

with dmTestApp.FDQInvoiceDetail.Aggregates.Add do Begin
expression := 'sum(invoicedtqty * invoicedtprice)' ;
IndexName := 'invoicedtnum' ;
Active := True ;
End;
dmTestApp.FDQInvoiceDetail.IndexName := 'invoicedtnum' ;
dmTestApp.FDQInvoiceDetail.AggregatesActive := True ;

label19.Caption := 'Invoice Total: ' + VarToStr(dmTestApp.FDQInvoiceDetail.Aggregates[0].Value) ;

I get an error "Aggregate [] definition is not complete for dataset [FDQInvoiceDetail]

Just adding things as I further research and await some feedback. THANK YOU AGAIN !!!!

Eddie M

Edited by: Eddie Muniz on Feb 22, 2017 6:13 AM

Edited by: Eddie Muniz on Feb 22, 2017 6:19 AM

Edited by: Eddie Muniz on Feb 22, 2017 6:44 AM
Serge Girard

Posts: 29
Registered: 1/5/02
Re: Firedac Aggregate field Use for Master Detail calculation  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2017 6:22 AM   in response to: Eddie Muniz in response to: Eddie Muniz
Hello,

if you still use DBase, I am not sure (i do not have to use Firedac functions like TFDLocalSQL yet ) , but if you use a "modern" SGBD it's easy to join 2 tables with SQL .

If in the past times I was using Tables, now i work almost exclusively with Querys. To complete this goal , I use this way

// SQL (SGBD Firebird)
WITH L AS (SELECT INVOICE_NUMBER,SUM(QTE*PRICE) AS LINES_AMOUNT FROM INVOICE_LINES
                   GROUP BY INVOICE_NUMBER)
SELECT M.INVOICE_NUMBER,.....,COALESCE(L.LINES_AMOUNT,0) AS TOTAL FROM INVOICES M JOIN L ON L.INVOICE_NUMBER=M.INVOICE_NUMBER
Eddie Muniz

Posts: 12
Registered: 8/11/16
Re: Firedac Aggregate field Use for Master Detail calculation  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 22, 2017 6:41 AM   in response to: Serge Girard in response to: Serge Girard
Serge Girard wrote:
Hello,

if you still use DBase, I am not sure (i do not have to use Firedac functions like TFDLocalSQL yet ) , but if you use a "modern" SGBD it's easy to join 2 tables with SQL .

If in the past times I was using Tables, now i work almost exclusively with Querys. To complete this goal , I use this way

// SQL (SGBD Firebird)
WITH L AS (SELECT INVOICE_NUMBER,SUM(QTE*PRICE) AS LINES_AMOUNT FROM INVOICE_LINES
                   GROUP BY INVOICE_NUMBER)
SELECT M.INVOICE_NUMBER,.....,COALESCE(L.LINES_AMOUNT,0) AS TOTAL FROM INVOICES M JOIN L ON L.INVOICE_NUMBER=M.INVOICE_NUMBER

Yes I see how that would work but would still like to know if TaggregateField or the Aggregates in the FDQuery are also useful for this and best practice. Trying to use Firedac and all of its tools at least for now. While our SQL statement would seem logical I still think and believe that the use of Aggregates is perhaps a bit more elegant and in full use of what the platform and Firedac offer. Just an opinion.

I appreciate the feedback !!

EM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firedac Aggregate field Use for Master Detail calculation  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 24, 2017 11:08 PM   in response to: Eddie Muniz in response to: Eddie Muniz
For your task the aggregate fields may be the right tool. You should define aggregates on a detail dataset. The exception you are getting is because you did not specified aggregation expression. For more details please read topic:
http://docwiki.embarcadero.com/RADStudio/Seattle/en/Using_Maintained_Aggregates

--
With best regards,
Dmitry

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02