sorting columns, on OnTitleClick lazarus / 13-General ---------------------------------------------------------------------- tatamata · 2009-10-12 10:35 Hello. What would be the generic code for TitleClick event of a DBGrid, for switching sorting ascending and descending? Is it necessary to use IndexFieldNames of the underlying dataset? ---------------------------------------------------------------------- tatamata · 2009-10-12 12:02 I have tried this way: Code: [Select]procedure TFormMain.DBGridProductsTitleClick(Column: TColumn); begin   SortBufDataSet(Products, Column.FieldName); end;    I tried with two approaches: Version A: Code: [Select]function SortBufDataSet(BufDataSet: TBufDataSet;const FieldName: String): Boolean; var   i: Integer;   NewIndexName: String;   IndexOptions: TIndexOptions;   Field: TField; begin Result := False; Field := BufDataSet.Fields.FindField(FieldName); //If invalid field name, exit. if Field = nil then Exit; //if invalid field type, exit. if {(Field is TObjectField) or }(Field is TBlobField) or   {(Field is TAggregateField) or} (Field is TVariantField)    or (Field is TBinaryField) then Exit; //Get IndexDefs and IndexName using RTTI //Ensure IndexDefs is up-to-date BufDataSet.IndexDefs.Update; //If an ascending index is already in use, //switch to a descending index if BufDataSet.IndexName = FieldName + '__IdxA' then   begin     NewIndexName := FieldName + '__IdxD';     IndexOptions := [ixDescending];   end else   begin     NewIndexName := FieldName + '__IdxA';     IndexOptions := [];   end; //Look for existing index for i := 0 to Pred(BufDataSet.IndexDefs.Count) do begin   if BufDataSet.IndexDefs[i].Name = NewIndexName then     begin       Result := True;       Break     end;  //if end; // for //If existing index not found, create one if not Result then     begin       BufDataSet.AddIndex(NewIndexName,         FieldName, IndexOptions);       Result := True;     end; // if not //Set the index BufDataSet.IndexName := NewIndexName; end;                                      Version B: Code: [Select]function SortBufDataSet(DataSet: TBufDataSet;const FieldName: String): Boolean; var   i: Integer;   IndexDefs: TIndexDefs;   IndexName: String;   IndexOptions: TIndexOptions;   Field: TField; begin Result := False; Field := DataSet.Fields.FindField(FieldName); //If invalid field name, exit. if Field = nil then Exit; //if invalid field type, exit. if {(Field is TObjectField) or} (Field is TBlobField) or   {(Field is TAggregateField) or} (Field is TVariantField)    or (Field is TBinaryField) then Exit; //Get IndexDefs and IndexName using RTTI if IsPublishedProp(DataSet, 'IndexDefs') then   IndexDefs := GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs else   Exit; if IsPublishedProp(DataSet, 'IndexName') then   IndexName := GetStrProp(DataSet, 'IndexName') else   Exit; //Ensure IndexDefs is up-to-date IndexDefs.Update; //If an ascending index is already in use, //switch to a descending index if IndexName = FieldName + '__IdxA' then   begin     IndexName := FieldName + '__IdxD';     IndexOptions := [ixDescending];   end else   begin     IndexName := FieldName + '__IdxA';     IndexOptions := [];   end; //Look for existing index for i := 0 to Pred(IndexDefs.Count) do begin   if IndexDefs[i].Name = IndexName then     begin       Result := True;       Break     end;  //if end; // for //If existing index not found, create one if not Result then     begin       DataSet.AddIndex(IndexName, FieldName, IndexOptions);       Result := True;     end; // if not //Set the index SetStrProp(DataSet, 'IndexName', IndexName); end;                                                          Unfortunately, both of the functions raise the following exception: "The maximum amount of indexes is reached." What to do? ---------------------------------------------------------------------- tatamata · 2009-10-12 12:53 OK, I solved it. It was because IndexDefs.MaxCount property, that must be set prior CreateDataSet method. DataSet.MaxIndexesCount:=100; Also, I had to change AddIndex for descending indexes. So, this works now, the ultimate function for DBGrid sorting on TitleClick event in Lazarus, for TBufDataSet: Code: [Select]function SortBufDataSet(DataSet: TBufDataSet;const FieldName: String): Boolean; var   i: Integer;   IndexDefs: TIndexDefs;   IndexName: String;   IndexOptions: TIndexOptions;   Field: TField; begin   Result := False;   Field := DataSet.Fields.FindField(FieldName);   //If invalid field name, exit.   if Field = nil then Exit;   //if invalid field type, exit.   if {(Field is TObjectField) or} (Field is TBlobField) or     {(Field is TAggregateField) or} (Field is TVariantField)      or (Field is TBinaryField) then Exit;   //Get IndexDefs and IndexName using RTTI   if IsPublishedProp(DataSet, 'IndexDefs') then     IndexDefs := GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs   else     Exit;   if IsPublishedProp(DataSet, 'IndexName') then     IndexName := GetStrProp(DataSet, 'IndexName')   else     Exit;   //Ensure IndexDefs is up-to-date   IndexDefs.Update;   //If an ascending index is already in use,   //switch to a descending index   if IndexName = FieldName + '__IdxA'   then     begin       IndexName := FieldName + '__IdxD';       IndexOptions := [ixDescending];     end   else     begin       IndexName := FieldName + '__IdxA';       IndexOptions := [];     end;   //Look for existing index   for i := 0 to Pred(IndexDefs.Count) do   begin     if IndexDefs[i].Name = IndexName then       begin         Result := True;         Break       end;  //if   end; // for   //If existing index not found, create one   if not Result then       begin         if IndexName=FieldName + '__IdxD' then           DataSet.AddIndex(IndexName, FieldName, IndexOptions, FieldName)         else           DataSet.AddIndex(IndexName, FieldName, IndexOptions);         Result := True;       end; // if not   //Set the index   SetStrProp(DataSet, 'IndexName', IndexName); end; ---------------------------------------------------------------------- ecch · 2009-10-16 19:53 Excellent I am just looking for a method like this. I will implement your code and put the results here later. Thanks ecch ---------------------------------------------------------------------- sandy.sakti · 2011-11-11 06:29 Can anyone please explain to me how to use this code step by step, because I still confuse.... Help me please.... ---------------------------------------------------------------------- BigChimp · 2011-11-11 07:16 Haven't tried it myself, but the wiki page has been updated: http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_app lications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDa taSet In short: assign the SortBufDataSet function to the event handler you want to use when sorting (e.g. a button click event). I don't think you need to set any indexes as the SortBufDataSet function will do that for you. If it doesn't work, please post specific questions: what did you try, what didn't work, what operating system, Lazarus (and FPC) version, what don't you understand exactly, etc. ---------------------------------------------------------------------- JuhaManninen · 2011-11-11 08:15 If your solution is generic enough, you should make a patch to integrate it to DBGrid. Use the BugTracker for it. I use TStringGrid myself and I often needed the column sort feature with icons on the column header. I finally implemented a generic version of it. See :   http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Sorting_Colu mns_or_Rows You can use the same icons or you can improve them. Now they are just rotated 90 deg. from another icon. I don't promise to apply the possible patch because I don't use the data-aware controls myself and I don't know the code. However, you can refer to the equivalent feature in TStringGrid when explaining your patch. Juha