How to maintain sort order in master-detail clientdatasets Delphi?

I have two CDS arranged as master-detail on the common field 'upn'. They are both fed by separate bits of sql running queries against a SQLite database. The master SQL sorts its records by another field. The detail SQL sorts its data first by 'upn' and then by another field 'x'

If I remove the master-detail relationship, the 'detail' dataset shows the records in the correct sort order as determined by the sql.

However once I re-create the master-detail link, although the mater-detail relationship works correctly to filter the detail records according to upn, the detail data loses its sort order on 'x' (although the master is still in the correct order). I can't really tell what order the detail is appearing in, it seems random.

Any idea what is happening and how I can make the detail appear in the correct order?

Other info that might help

The master detail is set up at design time by setting values in the Detail CDS as follows

DataSouce1.Dataset = CDS_Master
CDS_Detail.mastersource = datasource1 (which is pointing at CDS_Master)
CDS_Detail.MasterFields = upn
CDS_Detail.IndexFieldNames = upn

Also, because of the CDS bug described here http://forums.devart.com/viewtopic.php?f=28&t=25620 I have set PacketRecords = -1 in the Detail CDS

Solution

You can sort the detail rows in any order you'd like by adding an index, although it's usually better from a performance standpoint to make sure the first field in the index matches the key field from the master dataset.

Here's a quick example that adds a couple of "customer" records, along with a dummy log of contacts. It will show the contacts in a detail table (I used two TDBGrids, each connected to a TDataSource that was in turn connected to a TClientDataSet). Everything else is done in code for clarity. Note that the client records are first filtered to only match the current ID in the master (ClientDataSet1), but are then sorted in date order within the detail grid:

procedure TForm1.FormCreate(Sender: TObject);
begin
  ClientDataSet1.FieldDefs.Add('ID', ftInteger);
  ClientDataSet1.FieldDefs.Add('CustName', ftString, 25);
  ClientDataSet1.CreateDataSet;
  ClientDataSet1.Open;
  ClientDataSet1.AppendRecord([1, 'Smith Co.']);
  ClientDataSet1.AppendRecord([2, 'Jones Bros Inc.']);
  ClientDataSet1.IndexFieldNames := 'CustName';

  ClientDataSet2.FieldDefs.Add('ID', ftInteger);
  ClientDataSet2.FieldDefs.Add('Contacted', ftDate);
  ClientDataSet2.FieldDefs.Add('Notes', ftString, 50);
  ClientDataSet2.CreateDataSet;
  ClientDataSet2.Open;
  ClientDataSet2.AppendRecord([1, EncodeDate(2014, 10, 1), 'First contact.']);
  ClientDataSet2.AppendRecord([1, EncodeDate(2014, 10, 3), 'Called again.']);

  // Intentionally added out of sequence, so effect of index will be clear.
  ClientDataSet2.AppendRecord([2, EncodeDate(2014, 9, 1), 'Order placed.']);
  ClientDataSet2.AppendRecord([2, EncodeDate(2014, 8, 15), 'Initial call.']);

  // Note indexing on both ID and Contacted fields.
  ClientDataSet2.IndexFieldNames := 'ID;Contacted';
  ClientDataSet2.MasterSource := DataSource1;
  ClientDataSet2.MasterFields := 'ID';
end;

As you've indicated you need to make the client rows show in descending order, you have to do a little more work. First, you have to create an index at the time the ClientDataSet is first created. Here, I've done it with a modification of the code above. To do it at designtime, open the TClientDataSet.IndexDefs property and manually add the index there, setting the same properties. (The property changing it to descending order is the TIndexDefs.Options flag ixDescending.)

ClientDataSet1.FieldDefs.Add('ID', ftInteger);
ClientDataSet1.FieldDefs.Add('CustName', ftString, 25);
ClientDataSet1.CreateDataSet;
ClientDataSet1.Open;
ClientDataSet1.AppendRecord([1, 'Smith Co.']);
ClientDataSet1.AppendRecord([2, 'Jones Bros Inc.']);
ClientDataSet1.IndexFieldNames := 'CustName';

ClientDataSet2.FieldDefs.Add('ID', ftInteger);
ClientDataSet2.FieldDefs.Add('Contacted', ftDate);
ClientDataSet2.FieldDefs.Add('Notes', ftString, 50);
with ClientDataSet2.IndexDefs.AddIndexDef do
begin
  Name := '';
  Fields := 'Id;Contacted';
  Options := [ixDescending];
end;
ClientDataSet2.CreateDataSet;
ClientDataSet2.Open;
// Append data to ClientDataSet2 as above in first example