Without question, the software industry is a data-driven environment. All of the IT industry runs on data (we are in the big data era, guys!)—customers, orders, purchases, billings; every day, our applications transform in data interactions between them. Undoubtedly, data is the wellspring of all IT businesses, so we must choose the best programs to interact with it, and fortunately with Delphi we are safe, we have FireDAC.

FireDAC is a unique set of Universal Data Access Components for developing multi-device database applications for Delphi and C++Builder. Here are some features that make this framework special:

• Cross-platform support
• You can use FireDAC on Windows, macOS, Android, iOS, and Linux applications
• Drivers for almost every major relational database, both commercial and open source

In the 90s, the catchphrase in software development was developing database applications. Delphi was the master, thanks to the way it was designed (TDataSet interface, Data Module, and ClientDataSet) and its frameworks (Borland Database Engine). In the spring of 2013, Embarcadero acquired AnyDAC and re-branded it as FireDAC. Now Delphi Database Developers have made an unrivaled framework available again.

The TFDTable component implements a dataset that works with a single database table. What makes this component amazing is a set of additional features—filtering, indexing, aggregation, cached updates, and persistence.

In this recipe, we'll see some of these at work: how to configure an TFDTable, how to manage the indexes to sort an associated grid, and how to collect new information via aggregates.

Let's look at the following steps:

1. Create a new VCL application by selecting File | New | VCL Forms Application.

2. Put a DBNavigator (aligned to the top), a DBGrid (aligned to the client), a DataSource, and a PopUpMenu into the form.
3. Set the DataSource property of DBGrid1 to DataSource1.
4. Select the EMPLOYEE connection in the Data Explorer and then drag and drop it on the form to generate the EmployeeConnection.
5. Put a TFDTable in the form and rename it to SalesTable.
6. The connection property of SalesTable is automatically set to EmployeeConnection.
7. Set the DataSet property of DataSource1 to SalesTable.
8. To choose the Table, you have to expand the Table property combobox and select SALES:

9. If you performed all the steps correctly, you should be in this situation:

10. Declare the CreateIndexes procedure under the private section of the form and implement it with the following code:

procedure TMainForm.CreateIndexes;
var
  LCustNoIndex: TFDIndex;
begin
  LCustNoIndex := SalesTable.Indexes.Add;
  LCustNoIndex.Name := 'MyCustNoIdx';
  LCustNoIndex.Fields := 'Cust_No';
  LCustNoIndex.Active := true;
end;

11. Declare the CreateAggregates procedure under the private section of the form and implement it with the following code:
    

procedure TMainForm.CreateAggregates;
begin
  with SalesTable.Aggregates.Add do
  begin
    Name := 'CustomerTotal';
    Expression := 'SUM(TOTAL_VALUE)';
    GroupingLevel := 1;
    Active := true;
    IndexName := 'MyCustNoIdx';

  end;

  with SalesTable.Aggregates.Add do
  begin
    Name := 'CustomerMax';
    Expression := 'MAX(TOTAL_VALUE)';
    GroupingLevel := 1;
    Active := true;
    IndexName := 'MyCustNoIdx';

  end;

  with SalesTable.Aggregates.Add do
  begin
    Name := 'CustomerLastDate';
    Expression := 'MAX(ORDER_DATE)';
    GroupingLevel := 1;
    Active := true;
    IndexName := 'MyCustNoIdx';

  end;
end;

12. Now, we are able to set up the SalesTable component. So, implement the OnCreate event handler for the form and include this code:

procedure TMainForm.FormCreate(Sender: TObject);
begin
  SalesTable.Active := false;
  CreateIndexes;
  CreateAggregates;
  SalesTable.IndexName := 'MyCustNoIdx';
  // index activated
  SalesTable.IndexesActive := true;
  // aggregates activated
  SalesTable.AggregatesActive := true;
  SalesTable.Active := true;
end;

13. Now, we have to implement DBGrid1TitleClick to perform the right sorting method when the user clicks on a specific title:

procedure TMainForm.DBGrid1TitleClick(Column: TColumn);
begin

  // if reset the column caption of LastColumnClickIndex, because index could be change...
  if FLastColumnClickIndex > 0 then
    DBGrid1.Columns[FLastColumnClickIndex].Title.Caption :=
      DBGrid1.Columns[FLastColumnClickIndex].FieldName;

  // if the order is descending set the IndexFieldNames to ''.
  if SalesTable.IndexFieldNames = (Column.Field.FieldName + ':D') then
  begin
    Column.Title.Caption := Column.Field.FieldName;
    SalesTable.IndexFieldNames := '';
  end
  // if the order is ascending set it to descending
  else if SalesTable.IndexFieldNames = Column.Field.FieldName then
  begin
    SalesTable.IndexFieldNames := Column.Field.FieldName + ':D';
    Column.Title.Caption := Column.Field.FieldName + ' ▼';
  end
  // if no order is specified I'll use ascending one
  else
  begin
    SalesTable.IndexFieldNames := Column.Field.FieldName;
    Column.Title.Caption := Column.Field.FieldName + ' ▲';
  end;

  // set last column index
  FLastColumnClickIndex := Column.Index;

end;

14. It's time to insert the aggregates. The goal is to show some aggregated information through a simple ShowMessage procedure. Add a new menu item to PopupMenu1, rename it to Customer Info, and implement the OnClick event with the following code:

procedure TMainForm.CustomerInfoClick(Sender: TObject);
var
  LOldIndexFieldNames: string;
begin

  // i use LOldIndexFieldNames to reset the index to last user choice
  LOldIndexFieldNames := SalesTable.IndexFieldNames;
  DBGrid1.Visible := false;
  // the right index for aggregate
  SalesTable.IndexName := 'MyCustNoIdx';

  // show some customer info
  ShowMessageFmt('The total value of order of this customer is %m. ' +
    'The max value order of this customer is %m. ' + 'Last order on %s ',
    [StrToFloat(SalesTable.Aggregates[0].Value),
    StrToFloat(SalesTable.Aggregates[1].Value),
    DateTimeToStr(SalesTable.Aggregates[2].Value)]);

  SalesTable.IndexFieldNames := LOldIndexFieldNames;
  DBGrid1.Visible := true;
end;

15. Run the application by hitting F9 (or by going to Run | Run):

16. Click on the Total Value column twice in the descending order:

17. Right-click on the first record to bring up the pop-up menu, then click on Customer Info:

The core concepts of this recipe are enclosed in the DBGrid1TitleClick and CustomerInfoClick functions.

In the first procedure, we used the IndexFieldNames property to generate a temporary index to perform sorting based on a field related to the DBGrid column clicked, and also applying a graphical change to the column to better understand the ordering.

In the second procedure, we used Aggregate to report some customer info:

• Total Value: This represents the total amount of all orders
• Max Value: This represents the order with the maximum amount
• Last Order: This represents the last date order

Aggregate are created in the CreateAggregates procedure. Here is some more information about the properties used:

• Expression property: This defines the expression to be used to calculate the aggregate.
  • GroupingLevel property: This defines the number of indexed fields to use for grouping. By default, its value is set to 0 (no fields and no grouping; all records in a dataset).
• IndexName property: This defines the name of the index to use for grouping. If none is specified, it will use the IndexName property of DataSet.

The expression engine provided by FireDAC is a powerful engine used for filtering, indexing, and calculated fields. For more information on how to write powerful expressions, go here: http://docwiki.embarcadero.com/RADStudio/en/Writing_Expressions_(FireDAC).

More information about aggregate and calculated fields can be found here: http://docwiki.embarcadero.com/RADStudio/en/Calculated_and_Aggregated_Fields_(FireDAC).