Sorting up and down

Back to basics today: the occupational psychologist (OP) wanted a report in which she could sort by each column, like in Excel. Clicking the first column would sort by this column, clicking the second would sort by the second column, etc. This isn't difficult to do in Delphi; it requires one to understand the dbGridTitleClick method and how to build indices for a clientdataset.

The other day, she asked me whether it would be possible once the report is sorted by a column to click again on the titlebar and get the column to sort itself in reverse. I was non-committal, but a little thought and work gave me the answer.

I am enclosing the code to a very simple but complete example program. The program uses a dbgrid, a datasource and a clientdataset, all inter-connected. For the example, I am using a standalone clientdataset, in which I define the fields and populate them; the indexing technique works exactly the same when the clientdataset is populated via queries.

As the data can be sorted by several columns (in the example, only two) and each column has to 'remember' in which direction it was last sorted (so that clicking on the grid's title bar will cause the data to be sorted in the reverse order), an array of boolean has to be declared, one element per column (this is called 'directions' in the example). 

For every sortable column, two indices have to be created - one for ascending sort and one for descending sort. Each index is created using the 'AddIndex' method of the clientdataset; a descending index has the third parameter set to ixDescending, whereas an ascending index has the third parameter empty.

Every time the grid's title bar is clicked, the index of the clientdataset is changed. But how does the program know which index to use, especially as there are two indices per column? The elegant solution that I invented was to number each index, where the number is the column number doubled; if the index is descending, then the number is incremented. Thus for column 0, the ascending index will be idx0 (0 times 2 = 0, no increment), whereas the descending index will be idx1. For column 1, the ascending index will be idx2 and the descending index idx3. This simple scheme will allow the indices to be chosen as a function of the column number.

Here is the program...

unit Unit1;

interface

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, DB, DBClient, Grids, DBGrids, StdCtrls;

type

  TForm1 = class(TForm)

    DBGrid1: TDBGrid;

    qTest: TClientDataSet;

    DataSource1: TDataSource;

    procedure FormShow(Sender: TObject);

    procedure DBGrid1TitleClick(Column: TColumn);

  private

    { Private declarations }

    directions: array [0..1] of boolean;

  public

    { Public declarations }

  end;

var

  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);

var

 strings: tstrings;

begin

 directions[0]:= true; directions[1]:= true;

 with qTest do

  begin

   fielddefs.add ('num', ftInteger, 0, false);

   fielddefs.add ('extra', ftInteger, 0, false);

   createdataset;

   open;

   addindex ('idx0', 'num', [], '', '', 0);

   addindex ('idx1', 'num', [ixDescending], '', '', 0);

   addindex ('idx2', 'extra', [], '', '', 0);

   addindex ('idx3', 'extra', [ixDescending], '', '', 0);

   strings:= tstringlist.create;

   getindexnames (strings);   // this line seems to be essential!

   strings.free;

   append;

   fieldbyname ('num').asinteger:= 2;

   fieldbyname ('extra').AsInteger:= 10;

   post;

   append;

   fieldbyname ('num').asinteger:= 1;

   fieldbyname ('extra').AsInteger:= 14;

   post;

   append;

   fieldbyname ('num').asinteger:= 3;

   fieldbyname ('extra').AsInteger:= 20;

   post;

  end;

end;

procedure TForm1.DBGrid1TitleClick(Column: TColumn);

var

 n, ex: word;

begin

 n:= column.Index;

 directions[n]:= not directions[n];

 ex:= n * 2;

 if directions[n] then inc (ex);

 with qTest do

  try

   disablecontrols;

   close;

   indexname:= 'idx' + inttostr (ex);

   open

  finally

   enablecontrols

  end;

end;

end.

There is one small 'gotcha' - the cliendataset 'GetIndexNames' method has to be called (its results are stored in a temporary string list). Without this, one receives an error message 'qTest: Index idx0 not found' should one click on the grid's title bar.