Log in Register FAQ Memberlist Search ContactReview Forum Index
A GoldMine Discussion and Support Community

ContactReview Forum Index » GoldMine 6 » Duplicate record account numbers found in database Goto page 1, 2  Next
Post new topic  Reply to topic View previous topic :: View next topic 
Duplicate record account numbers found in database
PostPosted: Thu Jan 08, 2009 4:53 pm Points: 0 Reply with quote
GoldMineRealEstate
GoldMine Journeyman
Joined: 26 Jul 2006
Posts: 40
Location: Anaheim Hills, CA




GM 6.70.70226

Have found several records with duplicate account numbers (two records with same account number) during random inspection. Was having problems with manual merge-purge on duplicate records (that had duplicate CONTACT field entry) and GM was freezing up. Could this (account no. duplicate) be the cause and if so, how best to locate and remove or merge the dupilcate account no. records?
View user's profile Send private message Visit poster's website

PostPosted: Thu Jan 08, 2009 6:14 pm Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




GoldBox's Contact File Verifier. It tests the database for over 2 dozen possible anomalies, and more. It advises what to do on each anomaly found. In the case of duped C1 or C2 accountnos, that would be running GoldBox's Dedupe on each table. Optionally, you can save the content of each record that will be Deduped out of existence.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Thu Jan 08, 2009 6:53 pm Points: 0 Reply with quote
bgannett
GoldMine Guru
Joined: 07 Sep 2007
Posts: 1820
Location: Pacific NorthWest




I agree with Bob Taylor that GoldBox has some great utilities for dealing with this type of problem.

If all you want to do is to identify contacts that have duplicate accountno in Contact1 table, you can run the following SQL query from within GoldMine:

Code:
SELECT count(*), AccountNo FROM Contact1 GROUP BY AccountNo HAVING Count(*) > 1


This will identify the contacts. You can then look at the contacts and delete one of them.

_________________
Bob Gannett
253-627-2244
goldsupport@harbornet.com
*** Providing AFFORDABLE GoldMine Supportsince 1990
View user's profile Send private message

PostPosted: Wed Jan 21, 2009 4:09 am Points: 0 Reply with quote
GoldMineRealEstate
GoldMine Journeyman
Joined: 26 Jul 2006
Posts: 40
Location: Anaheim Hills, CA




BobTaylor wrote:
GoldBox's Contact File Verifier. It tests the database for over 2 dozen possible anomalies, and more. It advises what to do on each anomaly found. In the case of duped C1 or C2 accountnos, that would be running GoldBox's Dedupe on each table. Optionally, you can save the content of each record that will be Deduped out of existence.


OK, the Contact File Viewer asks to Create or Select a BDE alias for GMBASE BDE and CONTACT BDE. What values are needed here. I checked the Help files and couldn't find an explanation of this. You cannot proceed to START wiithout filling those in.
View user's profile Send private message Visit poster's website

PostPosted: Wed Jan 21, 2009 4:37 am Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




That's right; GoldMine has never required BDE Aliases for the dBase versions, so you'll need to create them. With GoldBox, a BDE Alias is required whenever a query is required, and queries are used in the background with Contact Verifier.

Assuming you have the latest version, just press the CREATE BDE ALIAS button on the Contact File Verifier.

Then, on the set of buttons that appears, click the blue Make BDE Alias for GoldMine Contact File to create one for the Contact database. This will present you with a list of GM databases; choose the one you want. Then it will present you with a field for an Alias name that you make up. I recommend using something short and to the point, like COMMON_GM_CONTACTS. No special characters or spaces (underscore is OK). Click the green checkmark to save.

For GMBase, just click the navy Make BDE Alias for GoldMine Global Table button. Enter an Alias (no list of folders is needed); keep it simple, like GMBASE, and save.

That's all there is to it. If you ever use GoldBox Views, you'll use these Aliases a lot.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Wed Jan 21, 2009 8:33 am Points: 0 Reply with quote
bgannett
GoldMine Guru
Joined: 07 Sep 2007
Posts: 1820
Location: Pacific NorthWest




With every update, GoldBox becomes easier and easier to use. Thanks Bob Taylor for the explanation.

_________________
Bob Gannett
253-627-2244
goldsupport@harbornet.com
*** Providing AFFORDABLE GoldMine Supportsince 1990
View user's profile Send private message

PostPosted: Wed Jan 21, 2009 9:24 am Points: 0 Reply with quote
GoldMineRealEstate
GoldMine Journeyman
Joined: 26 Jul 2006
Posts: 40
Location: Anaheim Hills, CA




BobTaylor wrote:
That's right; GoldMine has never required BDE Aliases for the dBase versions, so you'll need to create them. With GoldBox, a BDE Alias is required whenever a query is required, and queries are used in the background with Contact Verifier. ...


Thanks that was VERY helpful. Personally I think it's too bad that such a great tool like Goldbox has never had better documentation or tutorials. I think Dave would have sold so many more. Even though it's a somewhat techhie tool, and end-user find value if it was more clearly expained (my $.02 worth. Not a complaint just a constructive criticism I've mentioned to him before. I help wrtie documentation such as this speciifically for end-users so I know it's possible - but sometimes you need an end-user to help a techhie do that.)

BTW, this is the report that was returned after completing the Verify File process. It appears that #4 and #5 are the issues here. Not quite sure how to proceed from the results:

GOLDBOX-5 Contact File VERIFIER (Best viewed with FIXED Font)

TABLE-NAME UN-LINKED BLANK DUPED CORRUPT T O T A L
---------- --------- ----- ----- ------- ---------
CONTACT1 3 0 888 0 4620

CONTACT2 553 0 888 0 5170
-----------------------------------------------------------------
| CRITICAL: BEFORE RUNNING a MAIN CONTACT MERGE-PURGE OPERATION |
-----------------------------------------------------------------
Normalize ALL discrepancies for Contact1 & Contact2 tables! With
one exception, you should see ZEROES in all columns except TOTAL.
Non-zero at Contact1's UN-LINKED indicates PARTIAL Main Contacts.

The Contact2 table must show ZERO in each of the first 4 columns.
Its TOTAL cannot exceed the Contact1 value above it. See below.

1. IF YOU SELECTED: FIELDS5 and CONTUDEF were cleansed of possible
corruption. Backups were written to the GXBACKUP sub-folder.

2. UN-LINKED for CONTACT1 typically indicates PARTIAL Main Contacts.

3. BLANK AccountNo records in CONTACT1 can be deleted with BR7.

4. UN-LINKED and/or BLANKs for CONTACT2 indicate Orphaned records.
These can be eliminated in GoldBox's Global DELETE Records.

5. DUPED AccountNo records can be first exported to GOLDDUPE.DBF, and
can then be permanently DELETED via GoldBox's Single-Table DEDUPE.
To SAVE Dupes, re-export from GOLDDUPE.DBF table to a new name.

6. CORRUPTED AccountNos indicate possible CONTROL CHARACTERS in the
field. Risky, but you can try GoldBox's ACCOUNTNO REGENERATOR
in {Global REPLACE Records} for each individual RECID value.

7. TOTAL Contact2's should be Equal To or Less Than Contact1's.

(END OF LISTING) Created 01/21/2009 07:48:15 AM


Last edited by GoldMineRealEstate on Wed Jan 21, 2009 9:59 am; edited 2 times in total
View user's profile Send private message Visit poster's website

PostPosted: Wed Jan 21, 2009 9:32 am Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




Wow, those are very scary numbers. I wouldn't touch it in this venue (i.e., would do it only as consulting work). You may want to run that by Dave, though.

As for better documentation, I understand what you are saying. It would be a much easier task these days (because the interface is much improved), but I did write on the subject about 5 years ago. One of my big mistakes as an enterprise, although I did learn a lot in the process, of course. Produced 2 books of nearly 1000 pages each, and there would have been another one, but I squelched it. It may be possible, but there are so many details that I doubt that it's practical. Dave's documentation is constantly getting improvements, and I think that's the best way to go.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Wed Jan 21, 2009 9:58 am Points: 0 Reply with quote
GoldMineRealEstate
GoldMine Journeyman
Joined: 26 Jul 2006
Posts: 40
Location: Anaheim Hills, CA




BobTaylor wrote:
Wow, those are very scary numbers. I wouldn't touch it in this venue (i.e., would do it only as consulting work). You may want to run that by Dave, though.

As for better documentation, I understand what you are saying. It would be a much easier task these days (because the interface is much improved), but I did write on the subject about 5 years ago. One of my big mistakes as an enterprise, although I did learn a lot in the process, of course. Produced 2 books of nearly 1000 pages each, and there would have been another one, but I squelched it. It may be possible, but there are so many details that I doubt that it's practical. Dave's documentation is constantly getting improvements, and I think that's the best way to go.


I appreciate your response and perspective. Documentation like this is my passion and a project that's do-able, IMO. Recognizing that the average end-user may only use/need about 10-20% of what Gbox could do and a well documented (with screen shots) manual (or video tutorials) could be a real boost and purchse motivator. I think it would also lead to more bu$iness for Partners to assist end-users wtih the remaining 80% of the program that would be handy at some point and the Partner could use the Gbox tools already installed at end-user site if the documentation encouraged them to purchase for the 20%.
View user's profile Send private message Visit poster's website

PostPosted: Wed Jan 21, 2009 11:01 am Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




Mark, my recommendation is that you talk with Dave about the possibilities of some vidieos; he and I have discussed them, so he may be open to it. I will continue to write about GoldBox in the form of free papers that I post on the "Getting" GoldBox page of my web site.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Wed Jan 21, 2009 11:42 am Points: 0 Reply with quote
DJ
GoldMine Guru
Joined: 29 Jun 2006
Posts: 2055
Location: Fitchburg, MA, USA




Does GoldBox change to frequently for you to write a book against it?

_________________
DJ Hunt

Phone: (978)342-3333
Email: DJ@DJHunt.US

GoldMine Premium - The Defintive Guide
One-on-One GoldMine Technical Support ( Fee Based )
www.DJHunt.US
View user's profile Send private message Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

PostPosted: Wed Jan 21, 2009 11:47 am Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




At the time I wrote those books, yes. It turned out that I had chosen the worst possible time to do it. Now, I could manage it; GoldBox is much more mature now. I just choose not to do so.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

PostPosted: Wed Jan 21, 2009 5:32 pm Points: 0 Reply with quote
bgannett
GoldMine Guru
Joined: 07 Sep 2007
Posts: 1820
Location: Pacific NorthWest




I remember many years ago when a certain individual (Bob Taylor) attempted to write books to help us use GoldBox but he ran into a problem. GoldBox Dave kept improving and making changes to the product, that Bob did not have a chance to keep up. Last year, Dave made over 20 upgrade releases to GoldBox (and there are three version of GoldBox out there for DOS, SQL, and GM 7-Cool. This year he has already released a new update.

On the plus side, there is a lot of help in GoldBox's help system. Also, a lot of help can be found under Tips on Dave's web site (www.redstonesoftbase.com).

_________________
Bob Gannett
253-627-2244
goldsupport@harbornet.com
*** Providing AFFORDABLE GoldMine Supportsince 1990
View user's profile Send private message

PostPosted: Wed Jan 21, 2009 5:47 pm Points: 0 Reply with quote
DJ
GoldMine Guru
Joined: 29 Jun 2006
Posts: 2055
Location: Fitchburg, MA, USA




I have always found the Help in most Help systems to be not very Helpful.

_________________
DJ Hunt

Phone: (978)342-3333
Email: DJ@DJHunt.US

GoldMine Premium - The Defintive Guide
One-on-One GoldMine Technical Support ( Fee Based )
www.DJHunt.US
View user's profile Send private message Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

PostPosted: Wed Jan 21, 2009 5:49 pm Points: 0 Reply with quote
BobTaylor
GoldBox Guru
Joined: 07 Sep 2007
Posts: 1216
Location: Jacksonville, FL




And of course, there is my own web site, which contains a wealth of info about GoldBox on its 18 primary pages, with many links to images and documents. No, it's not a tutorial; but if you are wondering whether you might be under-estimating GoldBox, it can help you break out of that particular box.

_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997.
View user's profile Send private message Send e-mail Visit poster's website

Duplicate record account numbers found in database
  ContactReview Forum Index » GoldMine 6
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
All times are GMT - 7 Hours  
Page 1 of 2  
Goto page 1, 2  Next
  
  
 Post new topic  Reply to topic  


Brought to you by Castell Computers, Doug Castell, Admin
RSS Feed
Powered by phpBB © 2001-2004 phpBB Group
Theme created by Vjacheslav Trushkin