Phantom Duplicate Names

by

Have you ever encountered ‘phantom duplicate names'?  Perhaps you are running a fully verified Back-up of QuickBooks and you get a warning that QuickBooks Can Not Continue because your file did not Verify fully.  Or maybe you were running the Verify for some other reason, perhaps an out-of-balance situation.  Regardless when you inspect the QBWin.log  you find an a LVL_ERROR in the section of the log after the “Testing for Duplicate IDs”

The LVL_Error may say something like, Name: Embassy Suites (rest of the name withheld for confidentiality purposes), it doesn’t really provide you any more information.  You may know that this name is associated with a particular Customer or a particular Vendor.  You check the appropriate Center (Customer or Vendor) and find no signs that the name is listed more than one time.  Since QuickBooks can sometimes make mistakes and permit a duplicate to be entered in the opposite category you also check that Center, but the name is not listed in it at all.  

In this case we are going to simply say that Embassy Suites appears in the Customer Center list only once, and that it does not appear in the Vendor Center at all.  But to be even more of a ‘data detective’ you decide to check the ‘Master Names’ list. So you open a check, and put the cursor in the Pay to the order of field, you then press the Ctrl (Control) key and the letter L (Ctrl + L = List). QuickBooks displays the master names list that contains the names of every Customer, Vendor, Employee and ‘Other Names’ as well.  Checking this list carefully you find that Embassy Suites only appears one time in the Master Names list as a Customer; in other words it isn’t listed twice anywhere.

Scratching your head you are asking yourself, ‘what the heck is going on? QuickBooks is telling me that name is duplicated, but I can’t find any duplicate.’  You decide to run the Rebuild Utility, after it completes you check the QBWin.log and QuickBooks appears to have tried to do something, but when you run the Verify utility again there is the same old LVL_ERROR and our old friend Embassy Suites is listed again under the duplicates section.

Perhaps you come to the conclusion that this is the perfect time to call in ‘the Doctor’, the QuickBooks File Doctor that is.  Running File Doctor on this file you might even see it give you a message that it’s repairing your file, but when you run the Verify afterwards, the same old results.  “Where or where can our 'phantom duplicate' be hiding?”

The Phantom ‘Unmasked’

Well it is indeed hidden, this is why I refer to it as a ‘Phantom Duplicate’.  You see the duplication that Verify finds is a name that exists in both the list and your data.  “You say wait a minute Murph, you have lost your mind, all the names in the data appear in the list and the data in the list typically appears somewhere in the data.”  So true, but I said the duplication exists in the list and your data…actually the Phantom exists in the data and the real name appears in the list.

Here is how this happens.  Sometimes if you use some 3rd party program that imports data, some of the name might be ‘truncated’ during import. If the customer name in your import data is too long for the field in QuickBooks your import program may ‘cut off’ the portion that is too long (this is know as 'truncating'). In some cases truncation may cause a new name to be created in the affected list if the imported name doesn’t match the name already in the list.  But sometimes a ‘phantom’ can be produced.

If the name which already exists in QuickBooks ends with a letter and one more place exists in the field length, then QuickBooks saves this name just as it is written.  The Customer and Vendor fields permit 41 characters in the field. If you created your customer using only 40 characters in QuickBooks the final place in the Customer field for that customer is null (the field placeholder is blank, notice I said blank and not a ‘space’.)

If however the Customer name in the data you are attempting to import is actually 45 characters, with the 41st character a space (like “name LLC.”) and your import program truncates the data at 41 characters the last four characters (the LLC.) are all that gets cut-off.  The space between the name and the LLC is actually included in the imported data. So the data recorded in the ‘transaction’ is posted as 41 characters, but the name in the list is only 40 characters. During import QuickBooks doesn’t identify this as a new name and in fact it matches the name on the transaction with the existing Customer’s name in the list.

But we have seen that some QuickBooks versions (QB Versions and releases) of the Verify utility will read the 41 character name in the transactions as a duplicate of the 40 character name in the list.  When this occurs it would seem to be almost impossible to resolve the problem using the tools given, but fortunately it is not.

The Solution (aka: 'Capturing the Phantom')

Follow these steps to resolve ‘phantom name duplicates’:

  1. Make a back-up or windows’ copy of your QuickBooks Company file. (You may need to set the back-up option to ‘Quicker’ or ‘No’ Verification.)
  2. Open the affected name list, in our example we would open the Customer list since ‘Embassy Suites’ was a customer.
  3. ‘Edit’ the affected customer in the customer list. Write down all information from each of the tabs for this customer, you need this information to make a new customer record in a few minutes.
  4. After you have collected all of the customer’s information edit the customer name field so as to change it to something temporary.  I happen to always use the name ‘temporary’.  In our example I have just made Embassy Suites ‘Temporary’.  Then save the record.  It may take a few minutes to save this record if the name has a lot of associated transactions.
  5.   Now ‘add’ a new customer to the customer list. In this case my new customer is Embassy Suites (I have withheld the rest of the name for confidentiality purposes).  Then continue to add the details you wrote down so that all of the information in the new file matches the information in the older record for this customer.  When you have completed all of the information, be sure to save the record.
  6. Return to the original record, in this case we are going to ‘edit’ the customer record that is now called “Temporary”. Change the name for this record to match the name of the new record, it must be identical.  Then ‘save’ the record.
  7. QuickBooks will display the message telling you that this name already exists in the list and asking you if you wish to merge them.  Click “Yes” to confirm that you want to merge the two Customer names together.  You are merging your old original name with the newly created record for that customer.   Again this may take some time if the original record contains a significant number of transactions. When the merge is complete there will only be one name (again) in the Customer list, but guess what, you will find that the ‘phantom’ has been ‘captured’.
  8. Resort the Customer Center list, then run the Verify Utility.  Verify should come back with the message that ‘QuickBooks found no problems with your data’, unless there is some other problem that we didn’t already resolve.  When you check the QBWin.log file you should find that your ‘phantom’ duplicate name is no more.

In this story the ‘phantom’ was not only unmasked, but captured, he didn’t get away like the one at ‘the Paris Opera.’