"Empty" tables

[Scott24x7]

All,
I have recently encountered a problem that seems rather straight forward, and in the past has not been an issue because deleted records were "PACKED" as soon as a table had the records removed, but I have a new requirment that does not allow for that.
As a result, I recently encountered a problem where I have a button on a form that I was "Ignoring" in the WHEN clause if the table's RECCOUNT() returned 0... but even with SET DELETED ON, if there are no "Undeleted" records in the table, RECCOUNT() returns the value of all records... so when I delete the last record from my table, the "WHEN" condition is not met.
What is the best method of determining if your table is actually "Empty" in this case???



Best Regards,
Scott

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

 

[bborissov]

LOCAL lnRecNo
lnRecNo = RecNo([MyTable])
SET DELETED ON
GO TOP IN MyTable
IF EOF([MyTable]) && No more records
   ....
ELSE
   GOTO m.lnRecNo IN MyTable
ENDIF

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Scott24x7]

Borislav,
Yeah, have done that in some places... but am really looking for a 1-liner... this should not be so "complicated" to simply tell if I have records... is this my only option?


Best Regards,
Scott

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

 

[bborissov]

Make this as a Function and pass Alias to it. Something like:

USE MyTable
? IsEmpty([MyTable])


FUNCTION IsEmpty(lcAlias)
   LOCAL lnRecNo, lcSetDele, lbRetVal
   IF VARTYPE(lcAlias) # [C]
      lcAlias = ALIAS()
   ENDIF
   lnRecNo = RecNo(lcAlias)
   lcSetDele = SET([DELETED])
   SET DELETED ON
   GO TOP IN MyTable
   lbRetVal = EOF(lcAlias) && No more records
   IF NOT m.lbRetVal
      GOTO m.lnRecNo IN (lcAlias)
   ENDIF
RETURN lbRetVal

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Mike Lewis]

Scott,

I wish I knew the answer to this one.

In my generic navigator class, I would like to grey out the First, Prev, Next and Last buttons when the table is empty. But how do you define "empty"?

As you've found, the table might contain only deleted records. Or it might contain records that are not visible because of a filter. Either way, you want to consider it as an empty table. But RECCOUNT() doesn't let you do that.

You can check for those by doing a COUNT .. TO, or a SELECT COUNT(*) ... WHERE, but that might involve quite a lot of overhead.

My own solution is similar to Borislav's, but even that's not ideal, as it involves record-pointer movement (bad if you are doing row buffering), and still a little overhead.

Sorry I can't suggest anything better.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Scott24x7]

Well... I hate when I come up with answers to my own questions.... this is a clever trick that through some careful "Playing" I have discovered, which actually does what you need Mike, and works every time...

Use this test:

IF RECNO(<tablename>) > RECCOUNT(<tablename>)
	MESSAGEBOX('Nothing Exists for This Item.',64,'Navigation Message')
	RETURN
ENDIF

This works because the record number of a "empty" table is 1 greater than the number of records in the table, and does NOT cause an error, because it is the "natural state". And even if the number of records in the table is 0, RECNO() still returns 1... so it will always be greater than the number of records returned by RECCOUNT() by 1 even if it has deleted recods in it...



Best Regards,
Scott

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

 

[Mike Lewis]

Scott,

That certainly work for tables that physically empty. But suppose you have a table containing some records. You then set a filter which hides all the records in the table. The table will be "logically" empty, but RECNO() won't necessarily exceed RECCOUNT().

The same is true if all the records have been deleted.

Even if RECNO() is greater than RECCOUNT() at the time you apply the filter or delete the last record, there's no guarantee that the record pointer won't subsequenly move backwards (this won't generate an error), in which case it may well become less than RECCOUNT().

Sorry to cast doubts. Correct me if I'm wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Scott24x7]

Mike,
Just did a full testing of this... in both cases, whether the table has records with SET DELETED ON, RECNO() returns a value 1 greater than RECCOUNT(). I then undeleted a record, and set a filter condition that it could not meet, and again, even with a filter AND SET DELETED ON on the table, it RECNO() returns a value 1 greater then RECCOUNT().

When you think about it, this makes complete sense... the way that FOX effectively gives you a "BLANK" table, is by positioning the record pointer just past the last record, effectivly 1 row higher than the table actually exists on... and so this simple test WILL meet all conditions.

You were actually the trigger to that... as I mentioned, I have not developed in VFP for over 2 years now, but when you said you wanted something for your navigation buttons, I got to thinking, "Hey, my nav buttons work that way... what the heck did I do back then". It was after I looked at the code I was using there, and remember the HOURS I spent finding that solution when I started in VFP back at version 6, that the reasons for WHY this worked came flooding back... must have been why I had that "nagging feeling" in the back of my mind that this was doable... LOL

So there you have it... the 1 lest test that makes all that tedious mucking about in code to have an elegant navigation and table solution.



Best Regards,
Scott

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

 

[bborissov]

Scott,
Try this with your method:

CLEAR
SET DELETED ON
CREATE CURSOR Test (fld1 I)
INSERT INTO Test VALUES (1)
DELETE
? [Empty Table?],RecNo() > RecCount()
GO TOP
? [Empty Table?],RecNo() > RecCount()
GO 1
? [Empty Table?],RecNo() > RecCount()

To be absolutely sure that you have empty table, try this:
(this required to have index on DELETED() just to be sure the query is optimizable:

CREATE CURSOR Test (fld1 I)
INSERT INTO Test VALUES (1)
DELETE
SELECT 1, COUNT(*) FROM Test WHERE NOT DELETED() GROUP BY 1 INTO ARRAY aTest 
? [Empty Table?],_TALLY = 0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Mike Lewis]

Scott,

This is not what I am seeing.

Consider this:

SET DELETED ON
USE AnyTable
DELETE ALL
GO 1
? RECCOUNT()  && returns a large number
? RECNO() && returns 1

RECALL ALL
SET FILTER TO .f.
GO 1
? RECCOUNT()  && returns a large number
? RECNO() && returns 1

OK, I accept that you might not move the record pointer after deleting the last undeleted record or setting a filter -- but you might.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Scott24x7]

Mike,
Yes, your moving of the record pointer does "work"... but this is an issue of program "flow"... I don't know why you would reposition, other than to get a record that is "visible" after the condition, in which case, you still satisfy what you want. I have used this in my navigation buttons for years now, without any issues... Remeber this is something you use on click events, which have have narrow focus, and tight control over what is happening right at that moment... In any case, its working for to stop things happening with buttons when there is nothing there, or allow me to display a message like "Nothing exists for this item"... Since all of the record function flow going on is a "downward" (i.e. Skip) with the exception of my "Up" and "Top" buttons, which have their own special handling to account for that... Anyway, give it some consideration, it is working beautifuly in my app now.



Best Regards,
Scott

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

 

[Mike Lewis]

Scott,

I accept your points completely, especially that this is an issue of program flow. It's just that I have a lurking fear that one day I'll do something that causes the record pointer to move. When that happens, I'll have forgotten this conversation, and will spend a day trying to find out why my navigation buttons don't behave properly.

Still, it's a very useful technique, and I'll consider adopting it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Scott24x7]

Mike,
I understand that fear... and its only because of having 4 years of "report free" application in my navigation class that I am comfortable with it... The reality is, as you pointed out, there is simply NO reason to move a record pointer on a filtered table or DELETED ON table to a record that is not visible... For its solution, I love it... but thanks for considereing the possibility at least, and have a little faith in your fundamental principle that you would not do this either, and if you did, you ahve to question why you would do it, and fix THAT as the bug anyway...


Cheers.



Best Regards,
Scott

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

 

[dbMark]

USE myTable  && a table with 5 records
GO 3
SET FILTER TO RECNO() > RECCOUNT()  && simulate empty filtered table
? RECNO() > RECCOUNT()  && displays .F. (not desired result)
GO BOTTOM
? RECNO() > RECCOUNT()  && displays .T. (desired result)