I have a master/detail ClientDataSet as follows (these are created/populated at run-time and populated with data returned from an API call, no database connection):
Services:
ID
Name
BasePrice
etc.
AddOns:
Selected
ID
ServiceID
Name
Quantity
UnitCost
TotalCost
etc.
I display the Services as a dropdown field that then populates a grid with the available add-ons for that service. The field 'TotalCost' is a calculated field displayed in the grid in its own column. The 'Selected' field is used to track a checkbox shown in the grid to indicate the customer wants that particular add-on.
This all works as expected. I now need to calculate the total cost of the service plus any add-ons. The cost of the service I am able to retrieve using:
ClientDataSetServices.FieldByName('BasePrice').Value
However, I am unable to retrieve the TotalCost from each selected add-on. I thought I could use an Aggregate field but in my searches I found that this is not doable using a master/detail setup. I also tried to simply iterate through the details ClientsDataSet as follows:
(within the CalcFields method of the details ClientDataSet)
// Add parts to parts cost
grdMain.DataSource.DataSet.First;
while not grdMain.DataSource.DataSet.Eof do begin
if (grdMain.DataSource.DataSet.FieldByName('Selected').Value) then begin
FPartsCost := FPartsCost +
grdMain.DataSource.DataSet.FieldByName('TotalPrice').Value;
end;
grdMain.DataSource.DataSet.Next;
end;
But this causes an infinite loop. When I debug this portion of code I find that the ...DataSet.First is calling CalcFields (or something else that is in turn calling CalcFields).
How can I iterate over the DataSet of selected add-ons to calculate the total costs dynamically (whenever a selection or quantity changes)?
--EDIT--
I tried to setup an Aggregate on the details table as follows:
- Added TAggregate 'AggregatePrice'
- Set the fields as follows: Active - True Name - AggregatePrice' Expression - SUM(TotalPrice) GroupingLevel - 1 IndexName - ServiceId Visible - True
When I run this I get the error message "Field 'TotalPrice' is not the correct type of calcualted field to be used in an aggregate, use an internalcalc"