Bob's Notes - July 2009
7/12/2009 - Although it was just added to GoldBox this year, the Contact File Verifier is
catching on among my clients and others. It is a many-faceted utility; some of the things it
can do for you are easily overlooked. So, over the next few weeks, I'm going to list and
explain these aspects, to help you get the most out of it.
What is the Contact File Verifier? In simplest terms, it is a utility that performs specific
tests on your database, and recommends actions that can improve your database's
health. In some cases, it will even perform the fixes for you, as it runs the test. So, how do
we use it?
In GoldBox 7, it is found at the bottom of the Service menu. Click on the thumbnail below
to open a screenshot of the utility.
When the utility opens, you'll be required to select the
database that will be evaluated. As long as you have a
valid BDE Alias for your GoldMine database, you can
make that selection.
Note that there are Launch and GO buttons. Launch is
only used if you want to run the Verifier from a Q-file, a
shortcut or Start/Run. We'll leave that discussion for later.
GO is the usual means of running the utility. When pressed, it will execute whatever tests
have been configured. Then, when the configured tests have all been run, it will present
a report (using Notepad) that summarizes what was found (and, if appropriate, done).
The default configuration (i.e. run as soon as the database is selected) tests the
Contact1 and Contact 2 tables for a variety of things. Below are thumbnails of this basic
report:
Note that the database has 2257 Contact1 records, but only 64 Contact2 records. The
difference means there are 2193 "partial" main Contacts. This database was used for
testing; you'd probably never see that many partial records, which are listed in the report
as UN-LINKED Contact1 records. Although the report does not specifically say so, partial
records can be made whole simply by editing any Contact2 field in the record. So, a
simple Global Replace of those records will fix the problem (which isn't a serious one,
anyway). The query SELECT ACCOUNTNO FROM CONTACT1 WHERE ACCOUNTNO
NOT IN (SELECT ACCOUNTNO FROM CONTACT2) will locate the records that need
editing.
Also note that the utility found one record with an Accountno containing a single-quote.
Because SQL queries use the single quote as a delimiter, GoldBox gives you a way to
convert Accountnos for all records with an Accountno containing a single quote to an
Accountno without a single quote. The report tells you that you can do that with
GoldBox's Accountno Regenerator, and even gives you the exact query to use in that
procedure, so you can change only the Accountnos that need to be changed.
______________________
Now, I want you to pay special attention to the following warning in the report:
--------------------------------------------------------------------------------------------------------
| CRITICAL: BEFORE RUNNING a MAIN CONTACT MERGE-PURGE OPERATION |
---------------------------------------------------------------------------------------------------------
It isn't quite as explicit as I'd like to see it. Here is a more detailed explanation: if you run a
Merge-Purge while the Contact2 table contains any duplicate records (i.e. the same
Accountno), YOU WILL LOSE DATA. So, never, ever run a Merge-Purge without first
running the Contact File Verifier, and--if any Contact2 dupes exist--following the
procedure discussed in the report to eliminate them. You should, of course, backup your
data before running the Single Table Dedupe procedure; and also backup again before
running the Merge-Purge.
______________________
Now, let's look at the numbered items in the report:
1. The Fields5 option is not part of the default configuration, so we'll discuss it next time.
2. We discussed the Partial Contact records above.
3. The report tells you what to do if Contact1 records with BLANK Accountnos were
found.
4. Directions for eliminating Contact2 records that are UN-LINKED or have BLANK
Accountnos are included.
5. Duped Accountnos. You'll probably never find duped Contact1 records, but duped
Contact2 records are not unusual. Duped Contact2 records are, as noted above,
extremely dangerous, if you will be doing a Merge-Purge.
6. Corrupted Accountnos include, but are not limited to, Accountnos containing the
single quote characters. As noted above, GoldBox offers a way to regenerate these
Accountnos, if needed.
______________________
So that's what you get just for running the default configuration. This is a data health
checkup that you can run any time, in just a couple of minutes. Next time, we'll look at
some more features of the Contact File Verifier.
catching on among my clients and others. It is a many-faceted utility; some of the things it
can do for you are easily overlooked. So, over the next few weeks, I'm going to list and
explain these aspects, to help you get the most out of it.
What is the Contact File Verifier? In simplest terms, it is a utility that performs specific
tests on your database, and recommends actions that can improve your database's
health. In some cases, it will even perform the fixes for you, as it runs the test. So, how do
we use it?
In GoldBox 7, it is found at the bottom of the Service menu. Click on the thumbnail below
to open a screenshot of the utility.
When the utility opens, you'll be required to select the
database that will be evaluated. As long as you have a
valid BDE Alias for your GoldMine database, you can
make that selection.
Note that there are Launch and GO buttons. Launch is
only used if you want to run the Verifier from a Q-file, a
shortcut or Start/Run. We'll leave that discussion for later.
GO is the usual means of running the utility. When pressed, it will execute whatever tests
have been configured. Then, when the configured tests have all been run, it will present
a report (using Notepad) that summarizes what was found (and, if appropriate, done).
The default configuration (i.e. run as soon as the database is selected) tests the
Contact1 and Contact 2 tables for a variety of things. Below are thumbnails of this basic
report:
Note that the database has 2257 Contact1 records, but only 64 Contact2 records. The
difference means there are 2193 "partial" main Contacts. This database was used for
testing; you'd probably never see that many partial records, which are listed in the report
as UN-LINKED Contact1 records. Although the report does not specifically say so, partial
records can be made whole simply by editing any Contact2 field in the record. So, a
simple Global Replace of those records will fix the problem (which isn't a serious one,
anyway). The query SELECT ACCOUNTNO FROM CONTACT1 WHERE ACCOUNTNO
NOT IN (SELECT ACCOUNTNO FROM CONTACT2) will locate the records that need
editing.
Also note that the utility found one record with an Accountno containing a single-quote.
Because SQL queries use the single quote as a delimiter, GoldBox gives you a way to
convert Accountnos for all records with an Accountno containing a single quote to an
Accountno without a single quote. The report tells you that you can do that with
GoldBox's Accountno Regenerator, and even gives you the exact query to use in that
procedure, so you can change only the Accountnos that need to be changed.
______________________
Now, I want you to pay special attention to the following warning in the report:
--------------------------------------------------------------------------------------------------------
| CRITICAL: BEFORE RUNNING a MAIN CONTACT MERGE-PURGE OPERATION |
---------------------------------------------------------------------------------------------------------
It isn't quite as explicit as I'd like to see it. Here is a more detailed explanation: if you run a
Merge-Purge while the Contact2 table contains any duplicate records (i.e. the same
Accountno), YOU WILL LOSE DATA. So, never, ever run a Merge-Purge without first
running the Contact File Verifier, and--if any Contact2 dupes exist--following the
procedure discussed in the report to eliminate them. You should, of course, backup your
data before running the Single Table Dedupe procedure; and also backup again before
running the Merge-Purge.
______________________
Now, let's look at the numbered items in the report:
1. The Fields5 option is not part of the default configuration, so we'll discuss it next time.
2. We discussed the Partial Contact records above.
3. The report tells you what to do if Contact1 records with BLANK Accountnos were
found.
4. Directions for eliminating Contact2 records that are UN-LINKED or have BLANK
Accountnos are included.
5. Duped Accountnos. You'll probably never find duped Contact1 records, but duped
Contact2 records are not unusual. Duped Contact2 records are, as noted above,
extremely dangerous, if you will be doing a Merge-Purge.
6. Corrupted Accountnos include, but are not limited to, Accountnos containing the
single quote characters. As noted above, GoldBox offers a way to regenerate these
Accountnos, if needed.
______________________
So that's what you get just for running the default configuration. This is a data health
checkup that you can run any time, in just a couple of minutes. Next time, we'll look at
some more features of the Contact File Verifier.
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
