Jump to content

Database Hygiene


This topic is 5708 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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 by Guest
Corrected red.
Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5708 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.