Record count for a filter

[jhall251]

I want to report the number of records found after setting a filter. I am counting to a variable to get the tally but that moves the record pointer and displays a non-matching record before the filter takes hold.

I'd like to get a count of the matching records without moving the pointer. Is there a way?

Joe Halloran

 

[DSummZZZ]

Try this:

STORE RECNO() TO nTmpRec
STORE SET("filter&quot TO cFilter
COUNT TO nHowMany FOR &cFilter
GOTO nTmpRec
?nHowMany




-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Steve Bowman]

if you are using SET FILTER TO (expr)
go top (set to first record)
? _TALLY (shows correct record count)


I never really liked the set filter because of several quirky things like this also needing to move record pointer to set the filter in play, I prefer using views for this type of operation then ? reccount('myViewName') returns correct value.

Steve Bowman
steve.bowman@ultraex.com

 

[Steve Bowman]

Not to mention I feel stuff like SET FILTER TO (expr) are only kept around for backward compatability.

REFRESHER ON SET FILTER (from foxhelp)
Once SET FILTER is issued, only the records that satisfy the condition specified by the logical expression lExpression are available in the table. All commands that access the table respect the SET FILTER condition. A separate filter can be set for every open table.

The condition specified by SET FILTER isn't evaluated until the record pointer is moved in the table.

Issuing SET FILTER TO without lExpression turns off the filter for the current table.



Steve Bowman
steve.bowman@ultraex.com

 

[agoeddeke]

Joe,

How about forgetting the filter:

SELECT COUNT(*)
FROM MyTable
WHERE <filtercondition>
INTO ARRAY MyArray
? MyArray[1]

A SQL Select will not move the record pointer either.

Andy

 

[Steve Bowman]

Issue does depend on several other facetts of the code, like will you be wanting to modify any of the data in the filtered records and so on.

I did test the _TALLY system variable after setting the filter and it indeed holds the correct recourd count for the filter so in short with out any other code modification

set filter
move pointer to (in any direction &quot;required by set filter&quot
_TALLY = filtered count

I also have used the

lcRecno=recno()
&quot;shake things up&quot;
go lcRecno

many times to return record pointer and it works like it should

Steve Bowman
steve.bowman@ultraex.com

 

[Steve Bowman]

Sorry dumb phat fingers

lnRecno=recno() && recno() return numeric
&quot;shake things up&quot;
go lnRecno


Steve Bowman
steve.bowman@ultraex.com

 

[jhall251]

Thanks to all.

I figured out that counting and then immediatley issuing GO TOP keeps the intervening record from showing so that works.

I didnt know that _Tally worked with set filter - that sounds like a better solution - will try it tomorrow.

Yes I dont like SET FILTER either and if I were more adept with updateable views that's probably what I would use - maybe when I have some time to experiement...

thanks again...

Joe Halloran

 

[alm1]

I can´t report the number of filtered records, in any way you suggested.
Can someone help ?
There is my code :

LOCAL lcField
Public cValue
cValue = ALLTRIM(thisform.Text1.Value)
lcField = ALLTRIM(thisform.DESCRITOR1.controlSource)
SET FILTER TO cValue $ &lcField
SKIP
Thisform.refresh()

apffal

 

[Mike Gagnon]

apffal

select myTable
SET FILTER TO cValue $ &lcField
Count to X

will give you a count of filtered records.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ChrisRChamberlain]

apffal

COUNT TO... will always put the reord pointer at EOF().

If you were certain, extremely unlikely, and not recommended as not good programming practice, that the current record would be within the scope of the the filter expression, you could :-

lnRecNo = RECN([MYTABLE])
COUNT TO TO lnCount FOR &lcExpression
GO lnRecNo

What is more likely is to use Mikes' code

select myTable
SET FILTER TO cValue $ &lcField
Count to X

but add :-

LOCATE

or

GO TOP

to the end of the code.

This will ensure the record pointer moves to within the scope of the filter expression.

FAQ184-2483 - the answer to getting answered.​

Chris

http://www.pdfcommander.com

http://www.pdfcommander.co.uk

 

[Steve Bowman]

Since moving the record pointer is required for SET FILTER

SET FILTER TO cValue $ &lcField IN MyTable
GO TOP
_TALLY && will hold filtered record count

SIDE NOTE: SET FILTER IS RUSHMORE Otimizable you may want to consider detirmining if there is anyway to build an index that would match the filter expression. This is not always possiable but well worth the exercise. In some cases with large tables it even speeds up the filtering by building a temporary index befor setting the filter, mind you I did say with large tables and not in all cases. RUSHMORE is a wonderful thing and I make use of it anytime it makes sense.

Steve Bowman
steve.bowman@ultraex.com

 

[alm1]

It works with &quot;Count to X&quot;... but doesn't display the filtered records.
When I try to use the navigation button, gives me the error message &quot;End of file encountered&quot;.
How to display the filtered records and its number count with the same command ?
Thanks.

apffal

 

[Mike Gagnon]

apffal

Please make an effort to read all the suggestions offered to you. Read ChrisRChamberlain suggested some additional code bring the cursor back to the top. You may also look at basic navigation techniques in the VFP Help files, these quesitons you are asking you can find all the answer there. You may also discover that using the filtered technique is not recommended by reading the help files.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[alm1]

As you can try yourself, the suggestion (for filtering and counting records with one single command) doesn't work.
Anyway, if I have found solutions in Help files, certainly I would not ask questions in this forum ...
Thanks for your kindness.

apffal

 

[Mike Gagnon]

apffal

As you can try yourself, the suggestion (for filtering and counting records with one single command) doesn't work.


I think I stated in many of your threads on this subject I would never use that style of coding.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.