Sorting records according to two or more fields

I have TTable component linked to FoxPro 2.6 table containing information about students. Some of the fields are indexed (single field, ascending order, no compound indexes). To sort the records in the table according to any indexed field (for example "FIRSTNAME") I simply assign its name to IndexName property of TTable. But how to do it when user of my database need to sort records according to two or more fields, for example in the following order: Surname->FirstName->BirthDate. Please help me.
Thanks, Ivo.
LVL 2
ivobauerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
hi ivo,
is it possible for you to work with a tquery instead with a ttable ?
meikl
0
manjushanCommented:
Hi,
  Use TQuery and give order by both the fields or how many ever fields u want to.It will work faster and better.
                        Manju
0
geobulCommented:
Hi,

As FoxPro 2.6 is not a SQL based system (I think), you have two possibilities:
1. Create new index at runtime and work with it or
2. Replace TTable with TQuery as Meikl said.

I recommend the second way too.

Regards, Geo
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ivobauerAuthor Commented:
I cannot use TQuery because I need the possibility to filter records in the table (Tquery has not this ability). Also it is impossible to add each time new index because order of fields can vary (it is user-defined).
0
kretzschmarCommented:
hi ivo,
tquery has a filter property, which works like the filter-property in the ttable!!
meikl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ivobauerAuthor Commented:
So I have looked to Delphi help and tquery really has Filter property (inherited from TDataset, therefore it's placed at the end of list) so I tried al the things and they worked well. Here are your points.
Thanks, Ivo.
0
kretzschmarCommented:
thanks for the points, ivo ;-)
glad to helped you
good luck again

meikl
0
ivobauerAuthor Commented:
Hi Kretzchmar. Since I have evaluated your question prematurely (was my fault) I am asking you (*please*) to help me again. I realized that TQuery component will be more suitable for me than TTable. But even I ask for live query (updatable) bde returns a readonly set to me due to ORDER BY clause present in the SQL and I still need to add, delete or modify existing record. Please help me again how to implement this feature...
Thanks, Ivo.
0
kretzschmarCommented:
yup, no problem,

you need additional a TUpDateSQL-Component as UpdateObject for your query.

this also needs that the query has cachedUpdates enabled.

well, i will try a StepByStep introduction, because there is no much coding than more property-adjustments.

lets start a sample:
(i do so, that the query is always there and is properly configured, so that opening is possible)
at designmode:
-set the cachedUpdate-Property of the query to true
-drop a TUpdateSQL on the form
-select on theUpDateObject-property of the query now the TUpdateSQL
-doubleclick on the TUpdateSQL
-on the appearing dialog select
  -the keyfield (normally the field of the primary key)
  -the fields (normally all fields), which should be updated
- press now the button [generate sql]
- in the tab sql of this dialog you can now see and edit (if needed) the sql-statements for insert,update and delete
- if all ok press[ok]

now the bit code:
assign to the query-events afterpost and afterdelete a procedure, which contains:
queryname.ApplyUpdates;
queryname.CommitUpdates;

thats all, run your app

if you have problems to implement this, then leave your eMail-address here and i send you a sample.

good luck

meikl
0
ivobauerAuthor Commented:
Hi again Meikl.
I have set up the things as you said (added TUpdateSQL and assigned calls to ApplyUpdates and CommitUpdates to AfterPost and AfterDelete events of the query). Modification of records via db-aware controls is ok, only when I insert new record to the query via dbnavigator, the new record is not placed according to field order specified in ORDER BY clause in TQuery's SQL. May I close query and reopen to get correct order of records? And second thing: I tried to update (insert new record) query by Apply method of tupdatesql (after assigning parameters for the Query[ukInsert]) and the new record will not appear in query until I close it and reopen. Then I discover that new record contains completely different values than I specified.
Maybe some samples will help (send them please to: Ivo.Bauer@upce.cz).
Many thanks, Ivo.
0
kretzschmarCommented:
hi ivo,

yup, i send you the sample this evening.

to dbnavigator:
 yes you have to refresh the query (close/open), because the dbnavigator allways use the insert-method at current-record.

the second thing:
you cannot do this with a TUpdateSQL, when it is assigned as an UpdateObject, because your given parameters may be overwritten with the current record of the query.

meikl
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.