Solved

Can I sort on the values of the Lookup field?

Posted on 2002-05-26
15
1,244 Views
Last Modified: 2012-06-27
How I can sort(index)on the values(result)of a lookup field or a calculated field, maybe using BDE API?

I create my lookup field in a table, the lookup dataset is also a table, I want to show it in a DBGrid.
0
Comment
Question by:Seashore
  • 6
  • 5
  • 4
15 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7036803
use a query a lookupdataset and sort there the entries with an order by clause

meikl ;-)
0
 
LVL 4

Expert Comment

by:nestorua
ID: 7037435
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.
0
 

Author Comment

by:Seashore
ID: 7037960
I create my lookup field in a table, I want to show it in a DBGrid.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 7038431
>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 ;-)
0
 

Author Comment

by:Seashore
ID: 7038624
The lookup dataset is also a table, not a query.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7038655
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 ;-)
0
 
LVL 4

Expert Comment

by:nestorua
ID: 7039036
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.
0
 

Author Comment

by:Seashore
ID: 7040696
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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7040890
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 ;-)
0
 

Author Comment

by:Seashore
ID: 7040937
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.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7040995
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 ;-)
0
 
LVL 4

Expert Comment

by:nestorua
ID: 7043017
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.
0
 

Author Comment

by:Seashore
ID: 7052515
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 ?

0
 
LVL 4

Accepted Solution

by:
nestorua earned 200 total points
ID: 7054306
HI, Seashore,
Did you try my code? It does sort on the Country field.
And in this code I wrote what you have to do to solve your problem.
Sincerely,
Nestorua.
0
 

Author Comment

by:Seashore
ID: 7061489
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question