Database Hygiene

[fishbone]

Greetings, its been months since i have been on here, but i have a new task and need to clean up an old database. The first and foremost thing i need to do is to delete old records but i cant think of a good way to do it.

My Scenario:

I have about 30,000 records and only 10,000 are useful (therefore it would be tedious to go in one by one and delete the unused ones). My criteria for deleting the records is that if they dont have a call history of any kind i may as well delete them. The call history is actually a portal "Contact Calls" which is related to the "business info" table.

I want to delete all records that have zero information in the "contact calls" portal. I tried doing a blank find in the portal to see what came it but it returned a "null criteria" error.

Can anyone help please?

[Genx]

Add a calc field result number: Count( YourCallHistoryPK )

Then just search for >0 to return all records with history or <1 to return all those with none.

Edited February 14, 2007 by Guest

[fishbone]

Great! Its the small things i waste my time on. So i did what you said and perfect.

[LaRetta]

Why require a calculation (adding to field clutter) and a Find (which is slower, particularly on an aggregate calc) when you already have a relationship from BusinessInfo to Contact Calls? Script would be simple (from your [color:red]Contact Calls layout)

Show All Records

Go To Related [ Related Records from Business Info; Match all Records; layout based upon Business Info]

Show Omitted

LaRetta :wink2:

Edited February 14, 2007 by Guest
Corrected red.

[Genx]

Because it's a once only thing?

... and i'm not as smart as you?

Edited February 14, 2007 by Guest

[LaRetta]

It's just that, purging Contacts without calls every few years probably WON'T be a one-time thing at all. Even if it was only once, why create a calculation for it?

I've been using relationships more and more (the major highways) to isolate the groups of records and then Constraining down or showing omitted. That doesn't mean I'm smarter than you, Genx, only that I might - repeat might - have a better approach. If I think I might, should I remain silent? Would you? Do you? Nope. We are here to offer suggestions and that's all I did, as well as offering why I felt my suggestion might be an improvement. :wink2:

[Genx]

Lol, i didn't say anything -- I really do think you're smarter than me, I wasn't trying to bring you down.

I just offered an answer to your question of why. Your method seems very good and I'll probably be utilizing it myself -- I've never seen it before sooo... Thanks :)

[LaRetta]

I'm a tad sensitive. It seems a few people think I'm picking on them when I respond after they've posted. I BEG to be picked on - reading different approaches, being corrected, and being pushed to do my best not only improves MY skills but it helps others as well. It's a win-win.

Using GTRR then Show Omitted is lightening fast but I have to catch myself because I'll start to script a Find. I have to repeat, "When the highway is already there, use it!" It's been such a wonderful discovery for me that I thought others would appreciate the reminder as well. :wink2:

[Genx]

Well like i said, it's new for me and sounds great.

I might not necessarily like being openly corrected, but i admit when i'm wrong (eventually) and as soon as someone shows me a new method that's possibly better, I jump at it. In this particular case, like i said, i'm happy you brought it up, i can think of 5 scripts right now that can benefit from this method.