Solved

Can I sort on the values of the Lookup field?

Posted on 2002-05-26
15
1,195 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now