How to sort a ClientDataSet on a calculated field?

Question

I'm trying to sort a ClientDataSet on a calculated boolean field, but for some reason the data is not sorted. I have a boolean field named Highlight with FieldKind set to fkInternalCalc, and an index defined like this:

IndexDefs[0].DescFields = 'Highlight';
IndexDefs[0].Fields = 'Highlight;PatNo';
IndexDefs[0].Name = 'PatNo';
IndexDefs[0].Options = [ixDescending];

and obviously I have set my client dataset's IndexName to 'PatNo'. When I run my application, the data in the CDS is sorted on PatNo field only. Any thoughts?

[Understanding ClientDataSet Indexes](http://edn.embarcadero.com/article/29056) — bummi, Jul 27 '13 at 11:07
@bummi Well that article says _You cannot include virtual fields, such as calculated or aggregate fields, in an index_, but my calcuated field is `fkInternalCalc` which according to http://threads.embarcadero.com/threads/threads.exe/view?commentid=34388 can be used in an index — iMan Biglari, Jul 27 '13 at 11:24
In that example they use IndexFieldNames instead of IndexName. I can confirm that working. You can try to add another internal calculated field to make up the proper index as a workaround. — Uwe Raabe, Jul 27 '13 at 18:37
@UweRaabe I changed my boolean field to integer; no change. I'm just curious; how should I force `OnCalcField()` to be executed? I can not call `Refresh()` because my client dataset is not attached to any provider. The only way I have found was to call `Resync([]);` to force recalculation of all calculated fields. — iMan Biglari, Jul 28 '13 at 07:30
Have you tried naming the index different to all dataset fields? For recalculating all fkInternalCalc fields, please open a new question. That might be useful for others, too, and would get lost if answered in a comment. — Uwe Raabe, Jul 28 '13 at 10:28
@UweRaabe Sorry I don't understand what you mean by _Have you tried naming the index different to all dataset fields?_ I have tried creating a single index for the offending field, to no avail. — iMan Biglari, Jul 28 '13 at 10:57
Well, I have a similar case running properly where I have a internal calculated field named "SORT" and setting the IndexFieldNames property to "SORT". So indexing over internal-calc fields is actually working. In your case you have created an IndexDef combining two fields for that and set IndexName property. In addition your index name (PatNo) is the same as one of the field names (PatNo). I suggested to name the index differently so that it doesn't match with any of the fields in the dataset. — Uwe Raabe, Jul 28 '13 at 11:14
@UweRaabe I tried both renaming my index, and using `IndexFieldNames` instead of a predefined index, but the problem persists. — iMan Biglari, Jul 28 '13 at 11:19

MartynA · Answer 1 · 2014-07-30T07:06:02.527

Ime, indexing a ClientDatasSet on a compound index including an fkInternalCalc field works fine.

The code below works in D7 and XE6, and with Midas.Dll version ten years apart, giving

screenshot

So, unless there was some kind of regression in XE2, I would suspect the problem reported was in something at the OP's end.

 // tested with:
//  midas.dll 7.1.1692.668 30 August 2004
//  midas.dll 20.0.16277.1276  16 June 2014

procedure TForm1.SetUp;
var
  Field : TField;
  i : Integer;
begin
  Field := TIntegerField.Create(Self);
  Field.FieldName := 'PatNo';
  Field.FieldKind := fkData;
  Field.Index := 0;
  Field.DataSet := CDS;

  Field := TBooleanField.Create(Self);
  Field.FieldName := 'HighLight';
  Field.FieldKind := fkInternalCalc;
  Field.Index := 1;
  Field.DataSet := CDS;

  CDS.OnCalcFields := CDSCalcFields;

  CDS.CreateDataSet;
  for i := 1 to 10 do begin
    CDS.Insert;
    CDS.FieldByName('PatNo').AsInteger := i;
    CDS.Post;
  end;
end;

procedure TForm1.CDSCalcFields(DataSet: TDataSet);
var
  Value : Integer;
begin
  Value := DataSet.FieldByName('PatNo').AsInteger;
  DataSet.FieldByName('Highlight').AsBoolean := Odd(Value);
end;

const
  scIndexName = 'PatNo';
  scHighlight = 'Highlight';

procedure TForm1.AddHLIndex;
var
  IndexDef : TIndexDef;
begin
  IndexDef := TIndexDef.Create(CDS.IndexDefs, scIndexName, 'Highlight;PatNo', [ixDescending]);
  IndexDef.DescFields := scHighlight;
  CDS.IndexName := scIndexName;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  AddHLIndex;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  SetUp;
end;

How to sort a ClientDataSet on a calculated field?

1 Answers1

Linked