Set Key Command

[rgw]

Would appreciate a bit of help on the 'set key' command.

I have a networked table of approximately 75,000 customers and use a select statement to bring in the appropriate records for the selected zip. The user clicks a check box in the zip grid. I was experementing with the 'set key' command and found it to be much faster in retrieving the records than the select statement. All well and good but the set key command sometimes brings back no records from the customer file even though there may be hundreds of matching records. Exit the form try again and up they pop.

I use macro substitution to pass the value to the check box.


The table is indexed on zip+last(name)+first(name). The set exact on/off combinations don't seem to help too much.

Many thanks
rgw

 

[Scott24x7]

RGW,
Just a stupid question... are you refreshing your form after the SET KEY?

ThisForm.LockScreen = .T.
ThisForm.Refresh()
ThisForm.LockScreen = .F.

That would explain your "exit form, reenter form, records are there" issue, possible...

Otherwise, it sounds like what you are doing is reasonable, without being able to "see" your index expressions and corresponding code...


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[rgw]

Scott

Many thanks. Will take a look although I'm beginning to think part of the problem may be grid based as the customer grid seems to showing a zip record! I like to do the addobject bit of adding check boxes to allow people to select in grids and I wonder if that's more trouble than it's worth.

By the way, is the 'set key' command new or have I just missed it. Never see it mentioned.

many thanks
rgw

 

[Mike Lewis]

Rgw,

You say the problem is that sometimes the query fails to return the correct records.

But you also seem to be focusing your attention on the checkbox. It seems unlikely that the presence of the checkbox would affect how the query operates.

I suggest you set a breakpoint to suspend the program just after you have retrieved the records. Then, from the command window, do a browse. That will tell you whether it is the query that is failing, or the grid that is causing your problem.

As for the Set Key command, no, it is far from new. It was definitely present in Foxpro 2.x, and I probably in later versions of Foxbase.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[KenMurphy]

RGW,

You should be able to SELECT every bit as fast as you can SET KEY. The primary problem with SET KEY is that you have to have exactly the right index for every selection type. True it is simple to add indexes, but not when users have the table open. If you have the correct indexes on your tables, Rushmore should be able to optimize your query for you. There is another option that you might want to look at though. Use SEEK() and then SCAN WHILE. For example,

CREATE CURSOR curCust (id I, FirstName C(15) ...
USE MyCustomers IN 0 SHARED
SELECT MyCustomers
SET ORDER TO TAG Surname
IF SEEK(lcEnteredSurname)
SCAN WHILE MyCustomers.Surname = lcEnteredSurname
SCATTER ...
SELECT curCust
GATHER ...
SELECT MyCustomers
ENDSCAN
ENDIF

In testing that I did a while ago, this construct could in fact be faster than a fully optimized SELECT.

Ken

 

[rgw]

Mike/Ken

Many thanks. A task for after hours--I agree the grids shouldn't have anything to do with bringing back wrong answers so I expect I'm doing something silly.

One additional question if I may. To avoid having a query table joined to an open database--one uses the free command. If I've ever used it I can't seem to make it work
now. Where does the 'free' go in the select statement?

Hope this doesn't break site rules by sneaking in a second question.

Many thanks
rgw

 

[KenMurphy]

FREE doesn't go into a SELECT Statement - When you SELECT ... INTO MyTable, it creates a free table. If you SELECT ... INTO MyTable DATABASE MyDBC, it adds it to the database.

Ken