fishbone Posted February 14, 2007 Posted February 14, 2007 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 Posted February 14, 2007 Posted February 14, 2007 (edited) 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 Posted February 14, 2007 Author Posted February 14, 2007 Great! Its the small things i waste my time on. So i did what you said and perfect.
LaRetta Posted February 14, 2007 Posted February 14, 2007 (edited) 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 Posted February 14, 2007 Posted February 14, 2007 (edited) Because it's a once only thing? ... and i'm not as smart as you? Edited February 14, 2007 by Guest
LaRetta Posted February 14, 2007 Posted February 14, 2007 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 Posted February 14, 2007 Posted February 14, 2007 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 Posted February 14, 2007 Posted February 14, 2007 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 Posted February 14, 2007 Posted February 14, 2007 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.
Recommended Posts
This topic is 6553 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 accountSign in
Already have an account? Sign in here.
Sign In Now