TClientDataset: Is there a way to make a StringField behave like an IntegerField when setting an index?

Question

I want to sort a ClientDataset by a StringField which contains Integers. These Integers are used as item numbers on invoices. When I sort the ClientDataset by that field using a simple index the result is this:

1
10
100
101
11
110
111
12
120

However I'd like them to be sorted like an IntegerField:

1
10
11
12
100
101
110
111
120

Is there a quick way to do this?

Answer 1

You don't need to change your existing field to a different type. Instead, a way to do what you want is to define an ftInteger field on the CDS which is of FieldFind fkInternalCalc. Then in the CDS's OnCalcFields event, set the field's value by converting the value of your string field to an integer.

The point of setting the calculated field's find tp fkInternalCalc is that the CDS can be indexed on, and hence sorted by, an fkInternalCalc field, unlike an fkCalculated field. So, by indexing your CDS on the added fkInternalCalc field, the records should be appear in their numeric order rather that the alphanumeric order of your string field.

Code:

procedure TForm1.CDS1CalcFields(DataSet: TDataSet);
begin
  if CDS1IntField.IsNull then
    CDS1IntField.AsInteger := StrToInt(CDS1StringField.AsString);
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  i : Integer;
begin
  CDS1.CreateDataSet;
  for i := 1 to 100 do
    CDS1.InsertRecord([IntToStr(i)]);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  CDS1.IndexFieldNames := 'IntField';
end;