Eliminating SET FILTER

[tonypcg]

I'm currently eliminating SET FILTER code because I believe its causing an application to HANG UP(I previously sent a thread about my Foxpro app Hanging Up). The only problem I'm coming against is displaying a specific persons records on a screen. In other words, lets say i have 5 records (out of 12000) I want to display one at a time on a screen so someone can Inquire, Next, Previous or Edit the record(s). I did use set filter to display only those 5 records, but now how do I do it. I don't want to use a select and display from the temp table, because if they edit i have to do replaces etc. Any ideas???

 

[foxdev]

Let's say you want to filter on EmpID, and that the memvar nEmpID contains the desired EmpID on which to filter, and there is an index tag named EmpID:

[tt]set order to EmpID
seek nEmpID[/tt]

Now put a bit of code on your Next/Previous buttons so that they don't go past the desired EmpID, such as:

[tt]* -- Next button
skip
if EmpID <> nEmpID
skip -1[/tt]

Also, make sure you have an index on DELETED() if you normally have SET DELETED ON.

Not the only approach, but one that is not too difficult.

Robert Bradley

http://www.foxdev.com/

 

[tonypcg]

Thank you for getting back so quickly. I understand your method, but could I use indexes created specifically for id#'s. Example: building a index where sid=m.sid, this will create a IDX and the index will only display records for that specific id#. Do you see any problem with this instead of changing all the button codes. I don't know if this would create furter problems. thank you

 

[foxdev]

Yes, you could create a filtering index (INDEX ON ID FOR ID=123), and that would work, but...

[tab]Bad news in a multiuser environment, as the file will need to be locked in order to create the index.

[tab]A bit on the time-consuming side; with 12000 records it probably won't take a really long time, but certainly much longer than the SEEK approach.

I assume that you already had suitable indexes in place when you were using the SET FILTER approach...yes?

Robert Bradley

http://www.foxdev.com/

 

[tonypcg]

I used the HELP for INDEX and create a index like below. I can create these indexes in a batch file and only run it once or delete and recreate it in the programs. The dbf doesn't have to be exclusive, but this creates a IDX index (not CDX). It works but I'm not familiar with them, and i'm concrned it might cause a problem later on(like SET FILTER)????
Yes i did have allmy indexes built and in place when i was using the SET FILTER...but something causes the app to HANG UP????
Below is the HELP for creating a index in a program:
Here, a filtered index file is created. INVOICES.DBF is opened. The table/.DBF is indexed on the IDATE field, but only records that have an amount greater than 500 are available for processing and display.

CLOSE DATABASES
USE invoices
INDEX ON idate FOR itotal > 500 TO bigbalnc

Thanks again, I'm alway open for new help. The application is quite large and I just hate removing all the Set filters, but I guess i have no other choice. Tony

 

[dgrewe]

Robert, This statement you made is incorrect
[red]&quot;Bad news in a multiuser environment, as the file will need to be locked in order to create the index.&quot;[/red]
You can create an IDX from a shared DBF provided the IDX does not already exist, however, You have to have the file exclusive to make/change a CDX.

Tonypcg,
Since the IDX is a temp thing, use the local computers hard drive, the program will run faster. Change this line from
INDEX ON idate FOR itotal > 500 TO bigbalnc
to
INDEX ON idate FOR itotal > 500 TO [red]c:\bigbalnc[/red]
remember to delete the IDX later.
Like Set Filter This will slow down as more records are added to the DBF.
I highly recommend you go with [red] FoxDev's first suggestion with the next and previous button [/red] or in a year or so you be back here asking questions on speeding up the IDX system. A little more code now will save you a lot of problems later.
David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644

 

[tonypcg]

Thanks to all who responded to my problem. I guess I'll go with Foxdev's suggestion. I'm just hoping eliminating SET FILTER and adding the button code will eliminate the APP from &quot;Hanging Up&quot;??? Thanks again. Tony

 

[foxdev]

Just to satisfy my curiousity, could you post the SET FILTER statement and the list of index tag expressions being used?

Robert Bradley

http://www.foxdev.com/

 

[tonypcg]

This all started when people where going into the app and it just HUNG, see my posting on 11/3/00 under FOXPRO2.6 NOT RESPONDING. There is some code in there. But the filter where standard...SET FILTER TO Student_id==&quot;abc&quot;, or SET FILTER to student_id=&quot;abc&quot; and program=&quot;def&quot;.... and most of my indexes also where simple, setting index to student_id. The code that is in 11/3 thread, was using a browse window to display records, and then after they pick a record I set a filter and displayed the record on a screen (do screen.spr). I eliminated the SET FILTER in the 11/3 code and it seems to not hang up....now I have to do the same in the rest of the app (not small a lot of screens and a lot of code with SET FILTER). Tony

 

[ChrisCarroll]

If you're feeling brave you can automate most of the corrections with something like this not-entirely-tested-prog:

=aDir(aScreens, "*.scx")

* Process all the .scx files in the current directory
	For i= 1 to aLen(aScreens,1)
		Use (aScreens[i,1])	&&Open as a table
		Do SearchValid
		Do SearchWhen
	Next

Use
Return

*----------------------------

Procedure SearchValid

	Private ValidMemo, SFor1, RWith1, SFor2, RWith2

	* Search & replace the initial set filter to <expr>
	SFor1 = 	&quot;Set Filter to &&quot;
	RWith1= 	&quot;oOrder= set('order') &quot; + chr(13) + &quot;Index to c:\temp on &&quot;

	* Search & replace the final set filter to
	SFor2 = 	&quot;Set Filter to&quot;
	RWith2= 	&quot;Set Order to oOrder&quot;

	Scan for Atc(SFor2, Valid) >0
		m.ValidMemo= Valid
		
		Do while Atc(SFor1, ValidMemo) >0
			ValidMemo= Stuff(ValidMemo, AtC(SFor1,ValidMemo), Len(SFor1), RWith1) 
		Enddo
		Do while Atc(SFor2, ValidMemo) >0
			ValidMemo= Stuff(ValidMemo, AtC(SFor1,ValidMemo), Len(SFor1), RWith1) 
		Enddo
	Replace Valid with ValidMemo
	EndScan

Return

*---
Procedure SearchWhen
	* Same as Search Valid
Return

(but do take a backup first!)

 

[shl]

Hi, I have a very strange problem which happened to one of my multi-user system. It has been up and running for more than 6 years. This system is written in Foxpro Dos 2.6. I have just added a checking function into the setting criterias for a report. The function is running well. It is able to execute but I encountered system hang after I have exited from the option and a few more levels. What can cause system hang.
I have no problem if I do not execute the option.

 

[MBSCSI]

Another approach is to use the browse for command

open files
set relations
select customer or allow user to inpust cistomer id
Create a window
kdt=.t.
do while kdt
open wind
browse fields (list fields) in window (window name) for custid=123
user selects record
display info
if you want to see more records
clea display
else
clea display
kdt=.f.
endif
enddo
close file
clean up
If you want exact code let me know and i'll post it. With this method, the usewr gets to see all of the records in the subset and only displays the ones he/she wants. With the other method if there are 30 records and you want to see the 25th you have to view 24 records as opposed to using the slider bar in a window and clicking on the record you want.

 

[dbMark]

SHL,

I wonder if the new option you've added has code that changes one of the variables in a higher program. That might explain why it fails after you go finish and return to a higher level. Do you get an error message such as variable does not exist, type mismatch, or illegal value? Be sure that if you've used any variable names within your new code that already exist, you must first declare them PRIVATE. Actually, it's a good habit to always declare your new variables so you know what you are working with.

Also, make sure you don't have 2 procedures or functions named identically even if in different PRGs. Once you go into a PRG, it can search for a program first among its own procedures, then in the rest of the project. Depending on where you are, you could be going to the wrong code.

Both of those are issues I've encountered in the past.

dbMark

 

[henrym56]

If you have an index on the expression you are filtering on, try 'set key to' instead. It will be much faster on large record sets

 

[dbMark]

ALso, if you use BROWSE on an indexed table and you change an indexed field, when you go to the next record, it will first update the displayed order of the records, then proceed to the record that is now next and in that way it could "skip" some records. If that behavior arises, try using NOFOLLOW in the browse statement to avoid having the records changing their order after leaving edited records.

 

[Philmap]

To Tonypcq,

The program probably do not hang, it is just too slow to filter the information and it seems like freezing. I have seen that quite often on large databases.

If you ever read this, here is very good trick to eliminate SET FILTER and have super speed performance even on very large databases - I've used it for years.

Here is the idea.
Let say you want to Filter on a customer category A2.
Create an index that is part of the database (not on the fly) like this;
INDEX ON CAT TAG CAT_A2 FOR CAT=A2 && the FOR is the key

Then, in the program, say;
USE CLT ORDER CAT_A2

...and bingo, only these will be seen. And no speed penality at all - super fast.

That is if you do not have too many different categories, of course.