Indexing on a calculated field in a TClientDataSet

Two months ago I wrote about generic code for creating indexes (or indices, as I would prefer) on every field in a clientdataset. Yesterday I discovered two caveats to this code

• An index is not created for a field which is not displayed
• An index is not created for a calculated field - in fact, trying to create an index for such a field causes an error.

 I was working on a report whose query contains a field which returns a number which is then used as an index into an array ('cash', 'cheque', 'transfer'). It seems like too much overhead to define a table for means of payment when there are only three such means, so the program contains an array of strings, and certain tables contain values which index into this array. I want the report to display the name of the means of payment as opposed to the index, and so I have to use a calculated field. If the user should click on the grid's title bar, then the report is supposed to sort itself according to the chosen field; that's why there's an index for each field. But how is one supposed to create an index for the calculated field?

It took me a while to get to the correct answer. The first caveat above states that an index is not created for a field which is not displayed - but this does not mean that I can't manually create such an index. The calculated field's value is based on the value of the non-displayed field, so what I did was to create an index for the non-displayed field, but number it as if it were the calculated field.

I had to alter the 'BuildIndices' procedure so that it now reads like this

Procedure BuildIndices (cds: TClientDataSet);
var
 i, j: integer;
 alist: tstrings;

begin
 with cds do
  begin
   open;
   for i:= 0 to FieldCount - 1 do
    if fields[i].fieldkind <> fkCalculated then
     begin
      j:= i * 2;
      addindex ('idx' + inttostr (j), fieldlist.strings[i], [], '', '', 0);
      addindex ('idx' + inttostr (j+1), fieldlist.strings[i], [ixDescending], '', '', 0);
     end;
   alist:= tstringlist.create;
   getindexnames (alist);
   alist.free;
   close;
  end;
end;

Then I added two extra lines to the calling program; the calculated field is the sixth field so its indices are idx10 and idx11, and these values would have been skipped in the above code.

 BuildIndices (qReceiptsList);

 with qReceiptsList do

  begin

   open;

   addindex ('idx10', 'cash', [], '', '', 0);

   addindex ('idx11', 'cash', [ixDescending], '', '', 0);

   alist:= tstringlist.create;

   getindexnames (alist);

   alist.free;

  end;

This works very nicely.