Find and delete

[Johnny Boy]

Does anyone have a good clean script to find empty records and then delete them?

Thanks!

John

[CobaltSky]

Hi John,

The most demanding aspect about deleting empty records is establishing that they are empty. The rest is easy.

 

If your database is not too large, then I suggest that you set up your script by first entering Find mode and placing an equals sign (on its own) into each field and then perform the find. Afterwards, create the following script:

 

Set Error Capture [On}

Perform Find [Restore, Replace Found Set]

If ["not Status(CurrentError) = 401"]

Delete All Records [No Dialog]

End If

 

However if your record count is large, the search for blank fields will be inefficient, in which case you might likie to consider creating a stored calculation which flags that records are empty. The formula would be along the lines of:

 

Case(

IsEmpty(Field1) and

IsEmpty(Field2) and

IsEmpty(Field3) and

IsEmpty(Field4),

1)

 

- where you add an IsEmpty test for every field in the record. Because the resulting value in this calculation can be indexed, a search for a 1 in the calc field will be quicker than searchin g for null values directly (null values can't be indexed even if the field they reside in is indexable).

 

With this in place, you'd be able to use a script along similar lines (but the find criteria stored with the script and restored by the Perform Find [ ] step would be different).

[Johnny Boy]

Ray

Thanks! My intent was to use a script as part of the exit routine for my customers and products files. It would help to keep those files clean. Maybe my thinking is wrong. If you understand what I mean, would you please comment on my logic if you get a chance?

John