Count Records/Data of a Table

[TheLazyPig]

Hi, I wanted to know the count of records or data in my Grid View. I'm using Label to view the count. Where will I start?

 

[GriffMG]

There are probably a million ways to do this, you seem to be very new to VFP so I'll suggest one of the most visible.

Put a button on your form with a caption of Recalc. Leave it visible to start, while you debug it.

For the click method of that button put a bit of code like this:

PRIVATE m.COUNTER,m.OLDRECNO
m.COUNTER = 0
SELECT MYTABLE
m.OLDRECNO = RECNO()
GO TOP
DO WHILE .NOT. EOF()
	m.COUNTER = m.COUNTER +1
	SKIP
ENDDO

IF m.OLDRECNO > 0 .AND. m.OLDRECNO <= RECCOUNT()
	GOTO m.OLDRECNO
ENDIF
THISFORM.MYLABEL.CAPTION = "There are "+ALLTRIM(STR(m.COUNTER,8,0))+" Records"

Initially get your form up with the grid on it and click on your new button, the caption should change to display the answer you are looking for.

When you have that working, you could make the button not visible and put this code in the grid's AfterRowColChange method:

LPARAMETERS nColIndex
thisform.MyButton.Click()

You could expand on this technique to get the total for a column, to show just the number of items that meet a criteria anything like that.

Be aware, I'm not suggesting this is the best, fastest, most OOP way to do it, just a good way to familiarise yourself with the form, the grid and the other controls.

Good luck and enjoy yourself





Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

LazyPig,

Griff has given you a good answer. I'll suggest a somewhat simpler solution.

Place a label on the form. Name it, say, lblCount. Set its Visible property to .F.

You presumably know how to populate the grid and make it visible. Immediately after you have done that, do this:

SELECT TheTable  && this is the table that you used to populate the grid
COUNT TO lnCount
THISFORM.lblCount.Caption = TRANSFORM(lnCount)
THISFORM.lblCount.Visible = .T.

The main point to understand is that you can't count the rows in a grid. But you can count the records in the table that populates the grid - and that amounts to the same thing.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Griff, I'm not sure why you are calling a button's Click event in the grid's AfterRowColChange. If you want to display the count automatically, I don't think you need the button. Just set label's caption (as you are doing) and make it visible.

(Sorry if I have misunderstood your code.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Watch out, Count to lnCount will move your record pointer to the last record!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

I was trying to give the OP something to play with really Mike, using a button and putting it in the rowcolchange means the number of rows can be updated whenever something changes...

The OP strikes me a needing to have a way to experiment with the grid, the table etc to see what can be achieved. Clearly the approach I've suggested is not 'finessed' or ideal for a large table, but if he is working with say a dozen or so records for a timesheet application, then it's a good enough way to get the number of records and the total hours and many other things at the same time - and keep it up to date if he adds a record, or changes a number or whatever B-)



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[mjcmkrsr]

Hi,

Depending on how you populate your grid. e.g. with SQL Select ..., you may want to have a look at the _TALLY variable

hth
MarK

 

[Scott24x7]

Hi TLP,

Have seen the responses, we do exactly this same thing in our main screen. Here is how we do it:

[/code]
lnGridCount = 0
*
IF RECCOUNT() > RECNO()
GO TOP
DO WHILE NOT EOF()
lnGridCount = lnGridCount +1
SKIP
ENDDO
ENDIF
*
SELECT <Your Table>
SET ORDER TO <Your Index>
SET ORDER TO <Your preferred order>
*
IF lnGridCount = 1
This.Parent.lblLabelName.Caption = ALLTRIM(STR(lnGridCount))+" <Label Caption Plural>"
ELSE
This.Parent.lbllblLabelName.Caption = ALLTRIM(STR(lnGridCount))+" <Label Caption Singular>"
ENDIF
[/code]

We do the second part to keep English context aligned. 1 record only results in singular form: 1 Record
Or the ELSE: 0 or records in plural form)
It's very dynamically functional and cool.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Mike Lewis]

Point taken, Griff. I understand your approach.

And a good point re moving the record pointer. I see you took that into account in your own suggestion. If I was doing this for myself, I would probably have done something like [tt]SELECT COUNT(*) FROM MyTable INTO ARRAY laCount[/tt], which doesn't move the record pointer, but I thought I would keep it simple.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Scott, I think you have your captions backwards, plural for the singular case.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Scott24x7]

Griff,
Ah, yes, I was writing in <> and got it reversed, but the point is the same.
And I do this on DOZENS of captions on multiple tabs.

One thing I left out, this is in the REFRESH clause of the GRID.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Mike Lewis]

Scott, I hope you won't mind if I make a couple of comments on your code.

First, you can simplify your main loop by using [tt]SCAN / ENDSCAN[/tt] rather than [tt]DO WHILE NOT EOF()[/tt]. And I'm not sure why you need to test for [tt]IF RECCOUNT() > RECNO()[/tt]. Going further, you could replace the whole of that chunk of code with a [tt]COUNT[/tt] or a [tt]SELELT COUNT(*)[/tt] - always noting Griff's point re the record pointer.

Less importantly, you could replace [tt]ALLTRIM(STR(lnGridCount))[/tt] wuth a (slightly) simpler [tt]TRANSFORM(lnGridCount)[/tt].

Finally, I don't see why you need two [tt]SET ORDER[/tt]s.

None of the above is vital. It is just aimed to making the code more conciser. And I do like the way you are handling singluar vs. plural.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Hi Mike,
The SCAN/ENDSCAN vs DO WHILE I feel is just a style preference. SCAN is one of those commands I've just never felt comfortable with. I find the DO WHILE more intuitive, though I could see how others might find SCAN/ENDSCAN equally intuitive. For me, that's just not the case. Also, I'm working against the cursor I created for my grid, so it's always going to be a tiny set, and I doubt either method would result in a speed difference that is noticable. (Instant is instant... why do something "more instant"?)

Like the SCAN/ENDSCAN, I find the ALLTRIM<text manipulation> more intuitive than TRANSFORM. I've had issues with strings and partial matches not yielding what I expected. Maybe that's just the FPD2.6 in me.

I think you mentioned at the start of this thread theat there are thousands of ways to implement this... I would say this is just one of those thousands of way.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Mike Lewis]

Scott, of course you are right to go with what you feel comfortable with. I prefer to make my code as concise as possible, but that's my personal taste as well.

That said, there some constructs in VFP that I should be using but which I don't, for reasons of "comfort". For example, I have an aversion to using an exclamation mark as a shortcut for NOT, as in IF [tt]!EOF()[/tt] rather than [tt]IF NOT EOF()[/tt]. I know the exclamation mark is more concise, and there it has no performance issues. But like your avoidance of [tt]SCAN / ENDSCAN[/tt], I just prefer not to use it.

I could think of several more examples.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

We should start a whole new thread on that!

I tend to use .not. eof() rather than !eof(), but for some reason I'm not averse to if !m.flag
I too, don't use scan/endscan, preferring do while skip enddo - I did test it once (a long time ago, in response to something Olaf wrote I think) on a mega file, couldn't see a difference in speed.
I do use buttons to manage code for forms, where I know full well that I could as easily use a form method - just never picked up the habit.

I also tend to use PRIVATE more than LOCAL, so long as you remember to initialise them they're just as good.
I'm not averse to global variables, I know that's the Devil talking but...

B-)


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

I agree with any strategy counting the rows in the grid alias, though. RECCOUNT() will not make a difference about DELETED rows, and SET FILTER plus SET DELETED can influence what you see.

The bad side is you're always needing to go through all data, even just skipping from row to row. COUNT does optimize some FOR conditions about DELETED() rows, but if you populate the grid with data from a query the RECCOUNT(grid.recordsource) will be good, as all data in the cursor (or view) will not be deleted (at least not right after the query, you can of course delete rows from a cursor or view) and you usually use WHERE in your sql, then not a mix of query and SET FILTER, so RECCOUNT() is your count.

With small tables, nothing matters much. And in the end, I decided against a record count display in base forms my ex boss wanted to have this, simply as Access also has it. It does give no real good informative value to a user. If the count is low it's seen, either the grid shows all or the scrollbar is large, so you see there are only a few more rows to scroll through.

And really, any other situation you have thousands of rows to go through, you don't do that anyway, you or your users realize your data has grown so much you better add a filter form, perhaps previous to the grid form, or show data relevant to the user, to the day or week or any other aspect usable for automatic filtering. Paging also is a strategy not rarely used.

Counts could be something I'd like to see in a dashboard start form of an application about the relevant areas of data for today's application session. So all your counting efforts could be initialization and you don't determine that all the time.

Of course, that differs pretty much with whatever the application is about, but record counts are the least important things to manage. It's getting quite nasty with bigger tables anyway, filters can get sluggish, we got Grid.Optimize for minimize that effect on filtered data, but you'd also still depend on indexing appropriate for the grid to find the next row fast and a total count on filtered data only is achieved when you go top and count to bottom, no matter if you literally do that yourself or let a COUNT command do so. Once you get the count you can keepü track of course and decrement the counter with any deletion or increment it with new rows, but in multi-user apps you don't get an easy always current count when you explicitly want to circumvent the middle tier and directly bind a grid to backend data. And that will also be limited to DBFs anyway.

Any other client/server architecture will not read full data into a form, then you can work with RECCOUNT() of the alias the grid shows. It'll be the partial data you fetch from the backend server, hopefully and not just all data of a server table, that's always bad design and only simple to do with DBFs, at the same time a reason for quite a lot problems with networks today in multi-user environments. So even in VFP database scenarios you best use a three-tier architecture and bite the bullet of going to the DBFs through views, at least. Which on the one hand will leave you with the data as last queries or requeried not being a live view on the DBF, on the other hand enables simply to rely on RECCOUNT() being the count of listed data, because as I already said you won't combine queries of all data with SET FILTER but apply the filter to get the view cursor fast.

Just a sidenote, @Scott: ALLTRIM and TRANSFORM do completely different things. You might think of something else.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Griff,

My preference for SCAN/ENDSCAN was not for performance reasons. It is partly because it is more concise, and partly because you don't have to keep track of work areas. SCAN/ENDSCAN remembers the work area of the table being scanned, so you can switch work areas within the loop without having to remember to switch back again.

But performance is an issue as well. Here is what Hackfox says on the subject:

We tested empty loops, just going through a table from top to bottom, and found SCAN to be about twice as fast as the equivalent DO WHILE. Your mileage may vary

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,
Why not simply use the _TALLY system variable?

PUBLIC go_Form

go_Form = CREATEOBJECT("frmForm")
go_Form.Show

READ Events
CLEAR ALL


DEFINE CLASS frmForm As Form
	Width = 420
	Height = 360
	MinWidth = 420
	MinHeight = 360
	MaxWidth = 420
	MaxHeight = 360
	AutoCenter = .T.


*!*	Add a grid to the form

	Add Object grdNames as Grid with;
		Visible = .F., ;
		Top = 48, Left = 18, Width = 390, Height = 264, DeleteMark = .F.
		
*!*	Add object Label

	ADD OBJECT lblInfo as Label WITH ;
		Top = 12, Left = 280, Autosize = .T., FontSize = 8, FontItalic = .T., ;
		Caption = "Number of Records: " 
		
*!*	Add object Label

	ADD OBJECT lblSearch as Label WITH ;
		Top = 12, Left = 12, Autosize = .T., FontSize = 8, FontItalic = .T., ;
		Caption = "Enter string to search for in Name" 
		
*!*	Add object Textbox

	ADD OBJECT txtSearch as Textbox WITH ;
		Top = 12, Left = 192, Width = 60, Height = 24

*!*	ADD a Browse button - allows you to filter the underlying tables

	ADD OBJECT cmdBrowse As CommandButton WITH ;
		Width=60, Height=30, Left=84, Top=318, Caption="Browse"

		PROCEDURE cmdBrowse.Click()
			LOCAL lcAlias
			
			lcAlias = ALIAS()
			
			SELECT cName, nMeters, nSquare, nVolume ;
				FROM tblNames ;
				WHERE IIF(EMPTY(ALLTRIM(ThisForm.txtSearch.Value)), .T., AT(ALLTRIM(ThisForm.txtSearch.Value), cName) != 0) ;
				INTO CURSOR csrResults 

[highlight #FCE94F]			Thisform.lblInfo.Caption = "Number of Records: " + TRANSFORM(_Tally, "999,999")
[/highlight]			
			WITH ThisForm.grdNames
				.ColumnCount = -1
				.RecordSource = "csrResults"
				.Visible = .T.
				.ReadOnly = .T.
				.SetAll("Sparse", .F., "Column")

				.Column1.Width = 72
				.Column1.Header1.Caption = "Name"
				.Column1.Text1.FontBold = .T.
				.Column1.Text1.FontItalic = .T.

				.Column2.Width = 72
				.Column2.Header1.Caption = "Me"
				.Column2.Text1.InputMask = "9,999.99"
				
				.Column3.Width = 90
				.Column3.Header1.Caption = "Sq"
				.Column3.Text1.InputMask = "999,999.99"
				
				.Column4.Width = 90
				.Column4.Header1.Caption = "Vo"
				.Column4.Text1.InputMask = "999,999,999.99"

				.Refresh()

			ENDWITH
			
			SELECT (lcAlias)
			
		ENDPROC 
		

*!*	Add exitbutton to the form
  
	ADD OBJECT cmdExit As CommandButton WITH ;
    	Width=60, Height=30, Left=18, Top=318, Caption="Exit"
    	
		PROCEDURE cmdExit.Click()
			CLOSE ALL 
			CLEAR Events
			ThisForm.Release
		
		ENDPROC
	  
*!*	ADD code to form's events

	PROCEDURE Destroy()
		ThisForm.cmdExit.Click()
		
	ENDPROC
    
	PROCEDURE Load()
		IF !FILE("tblNames.dbf")
		
			Create Table tblNames (cName C(10), nMeters I, nSquare I, nVolume I )
			
				For li_I = 1 to 180
					INSERT INTO tblNames (cName, nMeters, nSquare, nVolume) ;
							VALUES ("Name" + PADL(li_I,3,"0"), li_I, li_I ^ 2, li_I ^ 3)
				Next li_I   
			
		ELSE

			USE tblNames
		ENDIF 
					

	ENDPROC
ENDDEFINE

hth
MarK

 

[Olaf Doschke]

MarK,

_TALLY is very volatile. It's okay to use after a query. But it will only give you the correct number after a query ran. If you simply USE a table, have a SET FILTER, SET KEY on the workarea, _TALLY won't tell you that. If you use queries into cursor, RECCOIUNT("alias") will give you the same as _TALLY, but it'll stay that way and even better, when records are added to the cursor - ie when it's queried as updatable view or READWRITE cursor and you can add records and do so, RECCOUNT() also increments. _TALLY is only good for the moment. The way you use it it's ok, but not all grid use must be via queries, so it's your solution for your way of populating it.

Using queries, better yet using a view or cursoradapter, you can use _TALLY, still, you don't get a _TALLY change when data is edited. What's still not working with a workarea RECCOUNT() is that deleting rows does not decrement RECCOUJNT(). So the most general solution is actually counting what you get from top to bottom. Index order slows things down, SELECT COUNT(*) will yield wrong results, as it goes to DBF and not buffer, so when you'd use that you also need to SET SQLBUFFERING to count the buffered version. Also, this count will only be what others will see after you commit the buffered changes.

It's a bit too simplified to use _TALLY, if you're after more than just the count of initially queried rows but an actual live/current information about the data row count in your grid. And the grid itself also doesn't have that info, as it only has one control per column, does only draw the rest and move those real controls to the activated row, when you click into it, but mostly also, because when it has drawn enough rows in it's visible portion it doesn't care for the rest of the workarea until you scroll down further. It only cares for the position and percentage of the whole data to display the scrollbar in the right size and position. But also that has its limits, there is a minimum size of the scrollbar handle you can pull and drag, and the size and position are estimated on RECCOUNT(), even if SET FILTER shrinks it down to less. Which also means when you USE a table SET KEY or SET FILTER in a way only say 5 records are visible and the grid shows them all, the scrollbar still will show smaller and mid position, as if you could scroll.

The positive side is that this makes the grid that fast with its display, it doesn't really instantiate 8000 controls in a grid listing 1000 records with 8 columns and lets you scroll the canvas used for that many controls. It only has 8 controls. Draws what's in the visible area and stops there. Grids actually become slowest with SET FILTER, especially in VFP6 or earlier, when the grid can't stop earlier than reaching EOF during its paint phase, because when the grid rows will not fill up earlier the painting process can only end when it detects no further records to paint, so it might need to go all thousands of rows the filter or key suppresses before reaching EOF. Grid.optimize makes that better.

Last word (and edit of this post): The way you query therefore is a good strategy for grids you want to look and behave good, as it will simply display the full query result and has no mismatch due to filtered rows, reordering by index vs physical order, etc. So this wasn't said to discourage your solution, it won't be a general solution for any case how the grid could be used, though.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

This is an interesting discussion, and I hope it will continue. But I wonder if we haven't thoroughly confused the OP. He asked a simple question, but he has received vastly more information than he could possibly have expected. I hope it has not discouraged him.

Perhaps he could come back here to give us his reaction to all this information - or, at least, to let us know if any of it has been useful to him.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads