sorting prior to a report 3

[Bryan - Gendev]

In my app I am offering a number of sort options for the order of records in the report based on certain fields in the underlying table.

I am creating an idx index on the fly.

One of the fields is numeric (10).

What expression should I use to create the index if the field name is mynumber and the index is myindex?

Thanks
GenDev

 

[MrDataGuy]

1) IMO in the strongest possible polite way put: DO NOT USE IDXs in a visual Foxpro App!. Yes it is possible, but there are much better less issue prone ways of doing what you want. (Since you posted in a VFP forum I am assuming you are using at least VFP 5.0, if this is a FPW or FD app then we might use IDXs. )

2) Use a SQL command to gather your data and use the ORDER BY clause to sort the output in any way you want. Example:

Select * from myTable order by mynumber into cursor qReport_Info

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Dan Freeman]

I'll agree with Anthony. There hasn't been a good (or even bad) reason to use an IDX index since sometime around 1990. (Or 1991?)

Either SET ORDER to an existing index tag (which your application should already have if it's something needed for reporting), or SELECT the data into an ordered cursor before running your report.

And if you're considering the SELECT INTO CURSOR route, performance will benefit from having a pre-existing index tag so you may as well just use that in the first place.

 

[JRB-Bldr]

"I am creating an idx index on the fly."

I thoroughly agree with those above about not using IDX indexes within VFP. Use CDX indexes instead.

However that does not answer your question about how to build an index.

Yes, using a SQL SELECT is indeed one very good way to accumulate the report specific data with the records in a specific order/sequence.

However if you really want to use an index (CDX, NOT IDX please), then just build the index like usual.

* --- Build all needed indicies into CDX index file ---
USE MyTable in 0 EXCLUSIVE
SELECT MyTable
INDEX ON CharFld1 TAG CharFld1
INDEX ON CharFld2 TAG CharFld2
INDEX ON MyNumber TAG MyNumber
* < and so on as needed >

* --- Then sometime later when needed ---
SELECT MyTable
SET ORDER TO <whichever index tag needed>
GO TOP
REPORT FORM MyReport NOCONSOLE TO PRINT

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

The advantage of a cdx over an idx index is it's updated with the data automatically. I also agree a permanent index tag in the table's cdx would be good here.

Otherwise you can create secondary cdx files and also an idx can be compact to be shorter. For a temp index you don't need permanent it's ok.

compact idx:
INDEX ON expression TO idxfile COMPACT

secondary cdx:
INDEX ON expression TAG tagname OF cdxfile

If these files are local the index can even be faster, but the devastating disadvantage is of course, it's only visible to the user creating it and so only for temp indexes.

Bye, Olaf.