SDAC Calculated and aggregated fields

Calculated and aggregated fields are dynamic fields that are not physically stored in a database. Their values are calculated on the client side when required.

The following sections outline the process of working with these fields and detail their main characteristics.

Before getting started

The article demonstrates how to use calculated and aggregated fields through practical examples. The examples are based on a test database table named demo.

You can create the test table by running the following script:

 
  CREATE TABLE demo
(
    id integer PRIMARY KEY,
    name varchar(50),
    itemprice money,
    itemcount integer,
    itemtaxes integer
)

Note: Each sample query processes all records in the demo table.

Calculated fields

Calculated fields are designed to perform calculations using any defined calculation logic or formula. These calculations can be carried out through various types of calculated fields.

SDAC supports the following types of calculated fields:

• fkCalculated - A simple calculated field that allows performing calculations using a single function defined in the OnCalcFields event.
• fkInternalCalc - An advanced calculated field where a calculation is defined by a single default expression or in the OnCalcFields event.
• fkLookup - A lookup field is used to retrieve values from related tables or datasets.

Calculated fields can be defined either at design time through the user interface or at runtime using code. The following sections provide practical examples of how to work with the fields programmatically.

fkCalculated usage example

This code example demonstrates how to define and apply a function to a calculated field using fkCalculated. When you run the code, the UName field displays the uppercase value of the Name field followed by the lowercase value of the Trim field.

Note: When using fkCalculated, you can assign custom procedure names to your calculations.

// Triggered when Button1 is clicked
procedure TForm1.Button1Click(Sender: TObject);
var
  oField: TField;
  i: Integer; 
begin
  // Refresh the dataset structure before making changes
  MSQuery1.Close;
  MSQuery1.FieldDefs.Updated := False;
  MSQuery1.FieldDefs.Update;

  // Create persistent fields from field definitions
  for i := 0 to MSQuery1.FieldDefs.Count - 1 do
    MSQuery1.FieldDefs[i].CreateField(Self);

  // Create a new calculated field with specific parameters
  oField := TStringField.Create(MSQuery1);
  oField.Size := 50;
  oField.FieldName := 'UName';
  oField.FieldKind := fkCalculated;
  oField.DataSet := MSQuery1;

  // Assign the OnCalcFields event handler for calculated fields
  MSQuery1.OnCalcFields := MyCalcFields;

  // Open the query to fetch data and trigger the calculation
  MSQuery1.Open;
end;

procedure TForm1.MyCalcFields(DataSet: TDataSet);
begin
  // Convert the Name field value to uppercase and display the result in the UName field.
  DataSet.FieldByName('UName').AsString := UpperCase(DataSet.FieldByName('Name').AsString) + LowerCase(DataSet.FieldByName('Trim').AsString);
end;

fkInternalCalc usage example

This code example illustrates how to define and apply a single expression to a calculated field using fkInternalCalc. When you run the code, the UName field displays the uppercase value of the Name field.

// Triggered when Button2 is clicked
procedure TForm1.Button2Click(Sender: TObject);
var
  oField: TField;
  i: Integer;
begin
  // Refresh the dataset structure before making changes
  MSQuery2.Close;
  MSQuery2.FieldDefs.Updated := False;
  MSQuery2.FieldDefs.Update;
  // Create persistent fields from field definitions
  for i := 0 to MSQuery2.FieldDefs.Count - 1 do
    MSQuery2.FieldDefs[i].CreateField(Self);

  // Create a new calculated field with specific parameters
  oField := TStringField.Create(MSQuery2);
  oField.Size := 50;
  oField.FieldName := 'UName';
  oField.FieldKind := fkInternalCalc;
  oField.DefaultExpression := 'UPPER(Name)';
  oField.DataSet := MSQuery2;

  // Open the dataset to make fields available for use
  MSQuery2.Open;
end;

fkLookup usage example

This code example illustrates how to define and apply a dynamic lookup field to a dataset. When you run the code, the Lookup field displays values retrieved from the ID fields in the main (MSQuery4) and lookup (MSQuery5) datasets.

// Triggered when Button3 is clicked
procedure TForm1.Button3Click(Sender: TObject);
var
  LookupField: TStringField; 
  i: integer;
begin
  // Refresh the dataset structure before making changes
  MSQuery4.Close;
  MSQuery4.FieldDefs.Updated := False;
  MSQuery4.FieldDefs.Update;

  //Create fields for MSQuery4
  for i := 0 to MSQuery4.FieldDefs.Count - 1 do
  MSQuery4.FieldDefs[i].CreateField(Self);
 
  // Create a new lookup field with specific parameters
  LookupField := TStringField.Create(MSQuery4);
  LookupField.FieldName := 'Lookup';
  LookupField.FieldKind := fkLookup;

  //Define a secondary dataset used for lookup values
  LookupField.LookupDataSet := MSQuery5;

  //Define a key field in the main dataset (MSQuery4) that links to the lookup dataset
  LookupField.KeyFields := 'ID'; 

  //Define the corresponding key field in the lookup dataset (MSQuery5)
  LookupField.LookupKeyFields := 'ID'; 

  //Specify a field in the lookup dataset to display as the lookup result
  LookupField.LookupResultField := 'Name';
  LookupField.DataSet := MSQuery4;
  LookupField.Index := MSQuery4.FieldDefs.Count; 
  MSQuery4.Open;
end;

Aggregated fields

Aggregated fields allow you to perform calculations by using SQL aggregate functions such as MIN(), MAX(), SUM(), COUNT(), and AVG().

This example shows how to define and apply an aggregate function to an aggregated field. When you run the code, the Total field displays the total value of items by summing the result of the formula (itemprice + itemtaxes) * itemcount across all records.

// Triggered when Button4 is clicked
procedure TForm1.Button4Click(Sender: TObject);
var
  oField: TAggregateField;
  i: Integer;
begin

  // Refresh the dataset structure before making changes
  MSQuery3.Close;
  MSQuery3.FieldDefs.Updated := False;
  MSQuery3.FieldDefs.Update;

  // Create persistent fields from field definitions
  for i := 0 to MSQuery3.FieldDefs.Count - 1 do
    MSQuery3.FieldDefs[i].CreateField(Self);

  // Create a new aggregated field with specific parameters
  oField := TAggregateField.Create(MSQuery3);
  oField.Active := True;
  oField.FieldName := 'Total';

  // Define the aggregated expression
  oField.Expression := 'SUM((itemprice + itemtaxes) * itemcount)';
  oField.DataSet := MSQuery3;
  
  // Enable aggregated calculations and associate the aggregated field with the dataset
  MSQuery3.AggregatesActive := True;
  MSQuery3.Open;

  // Display the calculated total value in a message box
  ShowMessage(FloatToStr(MSQuery3.FieldByName('Total').Value));
end;