is it possible to sort a native table without index.

[ellehcsim]

im having a problem on showing my datas in a grid control. the scenario is this:

i have a grid control on a form, in which the recordsourcetype is 'alias' and buffermodeoverride = 5. I set it to alias because i want the editing of datas done on some of the columns. so i used a native table to temporarily store the data before i pass it to an sql server database. I also do not issue tableupdate() on the table cause like i said i only use the table for temporary storage, so after i pass it on to the sql server i issue tablerevert() instead. Now the problem arises with the sorting of data. example..

let say customer A, ordered the following items (Item A00001 has a 10+2 promo):

Item No Qty Net Price .....

(1) A00001 10 10.00 .....
(2) A00001 2 0.00 .....
(3) B00001 20 15.00 .....

now let say that salesman decided not to give the promo since customer A is not aware of it. so the data on the grid becomes:


Item No Qty Net Price .....

(1) A00001 10 10.00 .....
(2) B00001 20 15.00 .....

but salesman decided to give the promo again. so the data on the grid becomes:

Item No Qty Net Price .....

(1) A00001 10 10.00 .....
(2) B00001 20 15.00 .....
(3) A00001 2 0.00 .....



the thing is, i want to sort the data by item (ascending) and net price (descending)

now, i cant issue a SELECT .. ORDER BY INTO CURSOR .... since i am using a native table (the temporary table) with buffermodeoverride = 5. it always return an empty cursor (meaning no records).

i also cant issue a SET ORDER TO ... since my table dosent have an index, and if i were to include one, how can i specifically do sorting of data with two fields having 2 separate order on one index.

i also cant change the recordsourcetype of my grid to 4(sql statement) since if i do that, my grid would be on a readonly state, meaning i cant edit some records on the column interactively.

i also cant use a view since i dont have a local database. its a free table.

i hope this informations would help.
thanks a lot.!

 

[foxwizard]

ellehcsim,

If you don't want to use the cursor, try to use SELECT .. ORDER BY .. INTO TABLE MYTABLE.DBF and then use this table into your Grid.


Hope this may help.

Bren
VFP - Philippines

 

[BPeisch]

Hi ellehcsim,

There are a number of false assumptions you've stated in your message. I'll start by clearing these up, starting at the end.

1. i also cant use a view since i dont have a local database. its a free table.

You can use create a view against free tables. The only thing that has to be in a DBC is the view definition. You can create the DBC and the view definition on-the-fly, and delete them when you're done. I have a paper about view you can download from

http://www.peisch.com/downloads.html

It hasn't been updated for VFP 8, but most of the info is still relevent.

2. i also cant issue a SET ORDER TO ... since my table dosent have an index, and if i were to include one, how can i specifically do sorting of data with two fields having 2 separate order on one index.

I'm not sure what you mean here. What two fields and two separate orders are you talking about? I see one order with a change of filter in the example you posted.

3. [i. i cant issue a SELECT .. ORDER BY INTO CURSOR .... since i am using a native table (the temporary table) with buffermodeoverride = 5. it always return an empty cursor (meaning no records).[/i]

The Buffermodeoverride wouldn't cause an empty table, regardless of what you set it to. You do want it to be 5 for a grid. Data entry grids get weird if you use anything else. But you can't SELECT from an alis with buffered data, and see the data in the buffer. Is that your problem?

How are you creating this "temporary table"? I don't see why you couldn't create an index on it, or even rebuild it when you need to.



-BP

http://www.peisch.com

 

[ellehcsim]

Hi,

You can use create a view against free tables. The only thing that has to be in a DBC is the view definition. You can create the DBC and the view definition on-the-fly, and delete them when you're done.

- ok, thanks for the info. but i guess this is my last option cause it might cause a major revision on my program.


The Buffermodeoverride wouldn't cause an empty table, regardless of what you set it to. You do want it to be 5 for a grid. Data entry grids get weird if you use anything else. But you can't SELECT from an alis with buffered data, and see the data in the buffer. Is that your problem?

- ah, ok, now its clear. tnx!


How are you creating this "temporary table"? I don't see why you couldn't create an index on it, or even rebuild it when you need to.

- I tried to create an index on my free table and this is what i did.

NAME: item_no
EXPRESSION: item_no+str(netamount,20,2)
SORT: Ascending

the output is:

Item No Qty Net Price .....

(1) A00001 2 0.00 .....
(2) A00001 10 10.00 .....
(3) B00001 20 15.00 .....

when i set the SORT to Descending it becomes

Item No Qty Net Price .....

(1) B00001 20 15.00 .....
(2) A00001 10 10.00 .....
(3) A00001 2 0.00 .....


but the problem is i wanted to sort the <item_no> on ASCENDING order and at the same time <netamount> to DESCENDING order so the output should be:

Item No Qty Net Price .....

(1) A00001 10 10.00 .....
(2) A00001 2 0.00 .....
(3) B00001 20 15.00 .....


Please help.
tnx a lot!

 

[alvechurchdata]

I'd go for the SQL solution and create a cursor but to answer your original question, &quot;Yes&quot;. This command:

SORT TO MyNewTable ON ThisField, ThatField

will sort the current table.

But SQL is a much much better solution.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ellehcsim]

done.! thank you very much guys!

 

[BPeisch]

Just to add to your stash of knowledge, if you want to index on one field ascending and another descending, you need to do something like this:

index on Item_No+str(99999-NetAmount) tag whatever

Just make sure that the upper limit you use to subtract the descending field from is higher than any legitimate value for that field.



-BP

http://www.peisch.com