How can I remove all records from a dbf VFP9 and just keep what was entered today 8/7/17 1

[Luiz Eduh]

Hello,

I know this is simple, but would like to double check with the experts. I need to remove records from a dbf and only keep what those that were entered on a specific date.

Thank you

 

[Tore Bleken]

You MUST have a date field for doing this.

[pre]ldDate = date()
Delete from yourtable where yourdatefield<ldDate[/pre]

This will not remove the records, only mark them as deleted. To remove the records physically, you must Use the table Exclusive after my code, and then Pack it.

Also you must Set Deleted On, which in my opinion always should be the default.

NB! Please change your user id into something which hints about your real name, it will make your presence here look more professional. Only trolls use a fake id.

 

[Mike Lewis]

Only trolls use a fake id.

Really? First I've heard of it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

As Tore rightly said, you need a field in your table that holds a date. But you will also need to update that field each time you append a new record.

So, in the code that appends the record, do this:

[tt]REPLACE YourDateField WITH DATE()[/tt]

Then use the DELETE command as per Tore's post.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, and if you follow every thread here you could have seen webuxer several times recently (since September 2016).

It's true the norm at least in the Foxpro section is to have a user handle with the real name. There are some exceptions from the rule and it is surely not a site rule. Even in some private invite only forums, several members don't have a real name handle.

Back on topic: As Tore Bleken already said, your DBF needs a date column to be able to filter this, there is no hidden data about the creation or update times of records in DBF files, there is no automatic transaction log, so you will have to have such a column. To let it contain the date/datetime of creation automatically, it needs to be a DBC table asa that allows default values. You then simply define a date column with default value DATE() or a datetime column with default DATETIME(). You need to do as Mike says for DBFs without default values as feature, i.e. for free and fox3x tables.

And then you have a basis not only for querying data in a given time span, but also deleting data from a time span or except a timespan, that's merely the negated condition.

I repeat what's already said, albeit going a bit into detail about prerequisites and reasons. Everything Tore said technically already remains true, too.

Bye, Olaf.

 

[Luiz Eduh]

Thank you all for the help, I really appreciate it. By the way, I will change my username to display my real name.

Thank you

 

[Mike Lewis]

I will change my username to display my real name.

That's always helpful, Luiz. Thanks.

And I see that Tore has done the same (which is particularly appropriate).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I just also did add the space, the "Display Name" in your profile section might already exist for years, but as far as I remember didn't exist when I joined in 2004. To me it's sufficient to know a name even just by the signature.

Bye, Olaf.

 

[Tore Bleken]

Same here. When I joined in 2004 the login name had to be without spaces, and the login name was the same as the display name.

 

[Olaf Doschke]

To prevent the need for PACK, you could also change your way to create new records this way:

SET DELETED OFF
LOCATE FOR DELETED()
IF FOUND()
   RECALL
   BLANK DEFAULT AUTOTINC
ELSE
   APPEND BLANK
ENDIF
SET DELETED ON

That intentionally does not reset DELETED to what it was before but forces the default of deleted being ON after the record recycling. It's generally a setting you only want OFF, when you need to detect and locate deleted rows. The need for that is - without trying to find a more diplomatic euphemism - ridiculous, as any normal database will not keep deleted records, your code shouldn't need access to them in any place but for recycling these DBF file bytes.

A benefit of a PACK once in a while remains. It is useful to get rid of memo bloat and recreate the index trees cleanly (notice index nodes on deleted rows also remain in the CDX file), the RECALL only reuses the RecSize() bytes of the deleted row (of course it also reactivates any referenced memo file section before you BLANK, but that's an unimportant fact). This way of recycling can reduce the frequency of PACKs necessary.

Bye, Olaf.

 

[Olaf Doschke]

All that said, a simple way to clean a table for daily data is to ZAP it at start of a day, so it only collects data from today. Eg in initialisation locate a record of a previous date, and if you find one, ZAP the table.

Bye, Olaf.

 

[Mike Lewis]

I've also now added a space: a tiny change that makes a useful difference.

It's so much more satisfactory when you can address people by their name, rather than having to use an arbitrary sequence of letters and digits.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Koen Piller]

Hi Tore and or Mike,

Where is the option to change your displayname in this forum?
Mind you I only want to change the displayname, not my login+password.
Thanks for replying.
Jockey2 (Koen)

 

[Mike Lewis]

Hi Koen,

Look for the My Stuff dropdown at the top of the page. Select My Profile. You will see a form with various fields that you ca edit.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Koen Piller]

Mike,

Thanks, so I donot have to hide behind a horse anymore

 

[Olaf Doschke]

As far as I can see this thread now is China Ready™, as recent news reports say. (

https://www.ft.com/content/1c18614e-eab6-3571-a5fb-c9d7259d3a73)

Bye, Olaf.