ProConnect HelpIntuit HelpIntuit

DMS creates duplicate clients when importing or printing from tax program

SOLVEDby IntuitDocument Management SystemUpdated September 08, 2022

This issue can occur if one of the duplicate clients doesn't have an SSN/EIN (Tax ID) entered in the Client Information tab of Document Management System (DMS).

Before you start:

  • Clients created within DMS may not synchronize with tax information sent by the tax software and could result in duplicate clients when printing from the tax software to DMS. The best advice is to always send client information to DMS from the tax software first. This will create a database record in DMS where future printing and scanning will save into.

Follow these steps to verify the DMS client has an SSN/EIN:

  1. Select the client.
  2. Go to the Client Information tab.

The SSN/EIN will appear in the Tax ID field. If the DMS client doesn't have an SSN/EIN, the tax program won't be able to recognize that the client already exists, and a new client will be created.

Solution 1 - Follow these steps if the client has not been duplicated:

Make sure the client has not been duplicated yet. If it has been duplicated, skip to Solution 2.

  1. Select the client in DMS.
  2. Click Client Information tab and select Edit Information, or right-click the client and choose Client Properties.
  3. Enter the SSN/EIN in the Tax ID field.
  4. Print the return for this client to DMS.

The file will go into this client and a new client won't be created.

Solution 2 - Follow these steps if the client has already been duplicated:

  1. Select the duplicate client you want to keep.
    • This will most likely be the one with the most info or documents in it.
  2. Drag/drop or copy/paste all the files from the one you want to discard, to the one you want to keep.
  3. Delete the duplicate you don't want.
  4. Make sure the remaining client has the Tax ID, as seen in Solution 1.

Solution 3 - Follow these steps to send your database files to technical support:

If there are too many clients to do the previous solutions, you'll want to send your database files to technical support.

  1. Inside DMS, note the path listed at the bottom under Current Database Path.
  2. Close the program on all workstations and open the folder noted.
  3. Select each file ending in .DAT and .BLB.
  4. With all those files highlighted, right-click any one of them and click Send To and choose Compressed (zipped) Folder.
    • This will create a new singular file that you will send to tech support.
  5. Right-click and Rename the new file to your customer number.
  6. Call ProSeries or Lacerte tech-systems support for the best method to send that file.

If you receive a call with this issue, Contact ​PCG Customer Service Tier 2 through Amazon Chat​ for permission to escalate it.

  • Note: Be sure to attach the zipped database files to the Case / Service Request (SR).

Technical details for escalation agent:

You can use the following query, created by the one and only Jason Arends Esq, to fix duplicate clients in DMS;

This query will search for client folders that have the same folder name and combine their contents into one folder.  It will attempt to locate and keep the ones that have an SSN found in data11. It will delete the duplicate folders from data01, data02, and data11. After it has merged them, cleanup will need to be performed afterward in order to remove duplicate folders from within each client (best bet here is to copy all the folders in folder templates, delete them then paste them back in).

Copy and paste the following to run as one query:

select data01a.folder_id, data01a.folder_name, data11a.tax_id into root_folders
from data01a
left outer join data11a
on data01a.folder_id = data11a.folder_id
where Folder_Type = 2
order by folder_name asc, data11a.tax_id desc
;
select folder_id, folder_name, tax_id into unique_folders
from root_folders
group by Folder_Name
order by folder_id
;
select root_folders.folder_id, root_folders.folder_name, unique_folders.folder_id as unique_folder_id
into translation_table
from root_folders
left outer join unique_folders
on root_folders.folder_name = unique_folders.folder_name
;
select translation_table.folder_id, translation_table.folder_name, root_folders.folder_id as fixed_id
into ssn_fix_table
from root_folders
left outer join translation_table
on root_folders.folder_name = translation_table.folder_name
where tax_id is not null
group by folder_id
;
update translation_table
set unique_folder_id = coalesce(ssn_fix_table.fixed_id, translation_table.unique_folder_id)
from translation_table
left outer join ssn_fix_table
on translation_table.folder_id = ssn_fix_table.folder_id
;
update data01a a
set a.pidpath = replace(cast(a.rootfolder_id as varchar(8)) with cast(t.unique_folder_id as varchar(8)) in a.pidpath),
a.rootfolder_id = t.unique_folder_id
from data01a a left outer join translation_table t
on a.rootfolder_id = t.folder_id
;
update data01a a
set a.parent_id = COALESCE(t.unique_folder_id,a.parent_id)
from data01a a left outer join translation_table t
on a.parent_id = t.folder_id
;
delete from data01a a
where a.folder_type = 2
and (a.folder_id not in (select unique_folder_id from translation_table))
;
delete from data11a a
where a.folder_id not in (select unique_folder_id from translation_table)
;
delete from data02a a
where a.folder_id not in (select unique_folder_id from translation_table)
;
drop table if exists root_folders; drop table if exists ssn_fix_table; drop table  if exists translation_table; drop table if exists unique_folders;

Was this helpful?

You must sign in to vote, reply, or post
Lacerte TaxLacerte Tax PlannerProSeries BasicProSeries Fixed Asset ManagerProSeries ProfessionalWorkflow Add-On Document Management System

Sign in for the best experience

Ask questions, get answers, and join our large community of Intuit Accountants users.