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?

Bivalent

Understanding ClientDataSet Indexes – Trespass

@Trespass 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 threads.embarcadero.com/threads/threads.exe/… can be used in an index – Bivalent

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. – Cankerous

@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. – Bivalent

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. – Cankerous

@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. – Bivalent

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. – Cankerous

@UweRaabe I tried both renaming my index, and using IndexFieldNames instead of a predefined index, but the problem persists. – Bivalent

Answer 1

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

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;

Abilene