Can I sort on the values of the Lookup field?

use a query a lookupdataset and sort there the entries with an order by clause

meikl ;-)

HI,
How do you create your lookup field and where do you wish to
show it and sort it, what DB you use and what components
(TTable, TQuery or TClientDataset).
Sincerely,
Nestorua.

I create my lookup field in a table, I want to show it in a DBGrid.

>I create my lookup field in a table
well sure, must be doen in this way,
but what lookupdsataset do you use?
(the sortorder must be adjusted in the lookupdsataset)

meikl ;-)

The lookup dataset is also a table, not a query.

well, if you've a table,
then you've to use an index for the field you want to sort

or
replace the ttable with a tquery and use
in the tquery sql-statement something like

select * from tablename order by wantedsortfield

hope this helps

meikl ;-)

HI,
Then you can use the IndexFieldNames property of your LookupDataSet indicating by what field you want to sort.
In this case you can sort in ascending order only.
If you use local db (Paradox f.e.) you must create corresponding secondary indexes in that table
(and then you can as well use IndexName instead of IndexFieldNames).
If you use MSSQL f.e., you need not to create indexes on the fields you are going to sort.
Sincerely,
Nestorua.

I Just do not fully understand. Make myself more clear, what I want to do IS when you click on a column's title, the grid will be sort on the values of the column which be clicked, the problem is some columns are lookup fields.
I need a more clear anwser(with sample code is more appreciable) and MUST NOT Use query.
You see I have just increased the point to 600.

well ok,

you need indexes on the fields you want to be sorted

a sample

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  if Column.Field.FieldNo in [0..4] then   //if a field selected where an index exists
    if pos(Column.Field.FieldName,table1.IndexName) > 0 then  //same field
      if Pos('Asc',table1.IndexName) > 0 then                 //ascending
        Table1.IndexName := Column.Field.FieldName + '_Desc'  //switch desc
      else
        Table1.IndexName := Column.Field.FieldName + '_Asc'   //switch asc
    else
      Table1.IndexName := Column.Field.FieldName + '_Asc';    //other field, switch asc
end;

in this sample i have named the indexes with the fieldname as prefix and _asc or _desc as the sort direction

another sample with a query

unit sortquery_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, DBCtrls, ExtCtrls, Db, DBTables;

type
  TForm1 = class(TForm)
    Query1: TQuery;
    DataSource1: TDataSource;
    Panel1: TPanel;
    Panel2: TPanel;
    DBNavigator1: TDBNavigator;
    DBGrid1: TDBGrid;
    UpdateSQL1: TUpdateSQL;
    procedure Query1AfterPost(DataSet: TDataSet);
    Procedure SortQuery(FieldNo : Integer; SortOrder : Boolean);
    procedure FormCreate(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

const
  mysql = 'Select * from records order by ';
  ToSort : Array [False..True] of String[5] = (' DESC',' ASC');

var
  NoField : Integer = 0;  //FieldNumber to Sort
  SortAsc : Boolean = True; //SortOrder True-Ascending,False-Descending


{$R *.DFM}


Procedure TForm1.SortQuery(FieldNo : Integer; SortOrder : Boolean);
var
  s : String;
  Fields : String;
  Values : Variant;
  I : Integer;
begin
  Screen.Cursor := crSQLWait;
  query1.DisableControls;
  s := mySQL+query1.Fields[FieldNo].FieldName+ToSort[SortOrder];
  Fields := '';
  Values := VarArrayCreate([0,query1.FieldCount-1],VarVariant);
  for I := 0 to query1.FieldCount - 1 do  //Remember Current Record
  begin
    Fields := Fields+query1.Fields[I].FieldName+';';
    Values[i] := query1.Fields[I].Value;
  end;
  Delete(Fields,Length(Fields),1);
  query1.Close;
  query1.Sql.Clear;
  query1.Sql.Text := s;
  query1.Open;
  query1.Locate(Fields,Values,[]);  //Goto to Current Record
  query1.EnableControls;
  Screen.Cursor := crDefault;
end;

procedure TForm1.Query1AfterPost(DataSet: TDataSet);
begin
  query1.ApplyUpdates;
  query1.CommitUpdates;
  SortQuery(NoField,SortAsc);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  query1.Open;
  SortQuery(NoField,SortAsc);
end;

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  if Column.Field.FieldNo - 1= NoField then
    SortAsc := Not(SortAsc)
  else
  begin
    SortAsc := True;
    NoField := Column.Field.FieldNo - 1;
  end;
  SortQuery(NoField,SortAsc);
end;

end.

hope this helps

tell us which database you use

meikl ;-)

The column I what to sort is a LOOKUP field, which means the values if from another table, your code is based on the index which I do not know how to build with.

ok,

in this case, as the keyfield may differ from the display-field for sorting, there is no possibility with the standard dbgrid to sort this properly with a ttable.

there are some thirdparty grids (mostly comercial),
which make this possible
(the quantumgrid from devexpress (www.devexpress.com) for example

about the indexes, they must be created maybe with the database-desktop, but in your case, this may not helpfull

a workaround could be to use a tquery (again),
where the displayfield is joined in (but hidden to the user) and ordered by this field.

meikl ;-)

HI, Seashore,
Don't worry I understood you quite well.
And I told you exactly what you need to do to have what you wish.
Now, I asked you about DB you are using and you answered me nothing.
So I assume you it's the Paradox tables you're using.
In this case you must do the following:
1. Put on your form two TTables, one TDataSource, one
TDataSetProvider and one TClientDataSet.
2. Join them in the usual way.
3. tblMain is connected to Orders.db,
tblLookup - to the Customers.db.
DBDEMOS alias is used.
4. Create lookup field 'luCustNo' for tblMain
(CustNo->CustNo->Country).
For the sample this is all you need.

There is the code of the sample UNIT:
{--------------------------------------------------------}
UNIT SampleUnit;

INTERFACE

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, Grids, DBGrids, ExtCtrls, DBCtrls, DBTables, StdCtrls, Provider, DBClient;

type
  TSampleForm = class(TForm)
    tblLookup: TTable;
    tblMain: TTable;
    Database1: TDatabase;
    dsMain: TDataSource;
    dbgrMain: TDBGrid;
    tblMainOrderNo: TFloatField;
    tblMainCustNo: TFloatField;
    tblMainItemsTotal: TCurrencyField;
    tblMainAmountPaid: TCurrencyField;
    tblLookupCustNo: TFloatField;
    tblLookupCompany: TStringField;
    tblLookupCountry: TStringField;
    tblMainluCustNo: TStringField;
    btActive: TButton;
    cdsMain: TClientDataSet;
    dspMain: TDataSetProvider;
    cdsMainOrderNo: TFloatField;
    cdsMainCustNo: TFloatField;
    cdsMainItemsTotal: TCurrencyField;
    cdsMainAmountPaid: TCurrencyField;
    cdsMainluCustNo: TStringField;
    procedure dbgrMainTitleClick(Column: TColumn);
    procedure btActiveClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  SampleForm: TSampleForm;

{--------------------------------------------------------}
IMPLEMENTATION
{--------------------------------------------------------}

{$R *.dfm}

{--------------------------------------------------------}
procedure TSampleForm.dbgrMainTitleClick(Column: TColumn);
begin
if Column.FieldName='luCustNo'
 then
  with (dbgrMain.DataSource.DataSet as TClientDataSet) do
   if IndexFieldNames=''
    then IndexFieldNames:='luCustNo'
    else IndexFieldNames:='';
end;
{--------------------------------------------------------}
procedure TSampleForm.btActiveClick(Sender: TObject);
begin
 with dbgrMain.DataSource.DataSet do
  begin
   Active:=not Active;
   if Active
    then btActive.Caption:='CLOSE'
    else btActive.Caption:='OPEN';
  end;
end;
{--------------------------------------------------------}
{--------------------------------------------------------}
END.

Sincerely,
Nestorua.

First I want to sort on the values of the Country field not on the CustNo field.
Second how do create the index file, just set the value of the IndexFieldName ?

It's all okay as long as the table is not too large, I'll accept that and I will keep my promise to increate the points to 600.