Document Migration for iManage sites

For sites using iManage for document management moving to FYI, there is no in product option available to export documents for migration. We use the Worksite Exporter tool by DocAuto that connects to the iManage database and exports the documents out into a configurable windows folder structure.

There are some iManage limitations, that can impact the success of an export using the tool, and as such it is necessary to perform some prechecks before FYI can agree to perform the export of the iManage documents for the client.

Note: This article is only relevant if your practice is migrating from iManage to FYI.

Before You Begin

  • If the Practice is running iManage Filesite/Desksite version 9x or later - Continue on to Prechecks
  • If the Practice is running iManage Filesite/Desksite version 8x or earlier - Prechecks are not required - Continue onto the Export Process Instructions
    (Optional for sites running version 8x or earlier refer to 3.0 Query to identify the total number of documents to obtain details on the number of documents in the iManage database)

Prechecks

For sites using iManage Filesite/Desksite version 9x or higher, it is necessary to run the precheck, in order to determine the suitability for FYI to manage the export, or whether the export will require additional specialist consulting.

What do the precheck scripts check for?

iManage version 9x and higher has a number of limitations that impact the ability of the Workspace Exporter tool to export documents without the need for custom scripting in SQL. The most common issues are:

  • Client Workspaces that contain more than 9999 documents
  • Folders within Client Workspaces that contain more than 9999
  • Are there any documents that are “flatspace” filed? (Documents filed in a folder that has been deleted)
  • Are there any orphaned documents?
  • Are there any documents filed at the root level of a Client’s Workspace?

What is required to run the precheck scripts?

  • Access to the iManage SQL Server or a workstation with access to the iManage SQL database
  • SQL Management Studio is installed and connected to the iManage SQL database
  • An understanding on how to run SQL scripts within SQL Server Management Studio. Refer to Connect and query a SQL Server instance using SQL Server Management Studio (SSMS)
  • An understanding of how to read and interpret the results in SQL Server Management Studio

Precheck Scripts

Step 1 - Check for folders with > 9999 documents

iManage have a limit to display 9999 documents in a single search from Worksite version 9x on.

  • We have been advised that this is something that is common knowledge in the iManage community and sites should be managing their documents to avoid this limit being exceeded.
  • If a folder within a Workspace exceeds the 9999 limit, the Worksite Exporter tool will not be able to export all of the documents for the folder without scripting.

The precheck scripts will identify the folders within iManage that contain more than 9999 documents.

Script 1.1 - Display workspaces with > 9999 documents - Grouped by Workspace

Assuming the table names and columns in the iManage database match, this script adjust column names and table names as necessary, and displays workspaces with more than 9999 documents.

select i.prj_id, count(i.ITEM_ID) from mhgroup.PROJECT_ITEMS i
where i.ITEMTYPE = 'd'
group by i.prj_id
having count(i.ITEM_ID) > 9999
order by count(i.ITEM_ID) desc

This script will return a list of workspaces that exceed 9999 documents listed by Workspace name.

  • If the script returns a list of workspaces - copy the results into a spreadsheet and proceed to Script 2.1.

  • If the script does not return a list of workspaces - move to Script 2.1.

Step 2 - Checks to identify any potential filing issues

The below scripts will assist in identifying any filing issues within the iManage database.

Three common issues that can occur with iManage sites are:

  • Documents are “Flatspace” filed. Flatspace filing is caused when folders within iManage are deleted when they contained documents that were active.
  • Documents are Orphaned. Orphaned documents are the result of documents being filed into iManage without being assigned to a workspace or client.
  • Documents are filed in an “Overflow” folder. Documents placed in the root directly of a workspace are stored within an Overflow folder in iManage. These folders cannot be searched for directly within iManage.

Script 2.1 - Documents Flatspace Filed by Year

This script will identify all flatspace filed documents.

select datepart(yy, d.entrywhen), count(datepart(yy, d.entrywhen))
from mhgroup.docmaster as d
left outer join mhgroup.project_items as p on (d.docnum = p.item_id)
where p.item_id is null and d.t_alias <> 'XML'
group by datepart(yy, d.entrywhen)
order by count(datepart(yy, d.entrywhen)) desc
  • If the script returns a list of documents, copy the results into a spreadsheet and reach out to your FYI Onboarding Specialist. Do not continue with the remaining scripts.

  • If the script does not return a list of documents - proceed to run Script 2.2.

Script - 2.2 All Orphaned Documents - Worksite Exporter Command Line Sample

This script will identify all orphaned documents (documents not filed in a workspace, client etc), and can be used to prepare the batch files to export via the command line exporter.

select distinct
d.AUTHOR, d.C_ALIAS, d.docnum, --For sorting, do not copy this column to batch file
from mhgroup.docmaster d
left outer join mhgroup.project_items i on i.ITEM_ID = d.DOCNUM
where i.PRJ_ID is null
and d.C_ALIAS <> 'DELETE'
and d.C_ALIAS <> 'WEBDOC'
order by d.AUTHOR, d.C_ALIAS, d.docnum
  • If the script returns a list of documents, copy the results into a spreadsheet and reach out to your FYI Onboarding Specialist. Do not continue with the remaining scripts.

  • If the script does not return a list of documents, proceed to run Script 2.3.

Script 2.3 - All Documents in Overflow Folders - Worksite Exporter Command Line Sample

This script will identify all documents stored in overflow (documents filed at the root of workspaces) and can be used to prepare the batch files to export via the command line exporter.

select distinct
w.PRJ_ID , f.PRJ_ID , d.DOCNUM, --For sorting, do not copy this column to batch file
from mhgroup.docmaster d
join mhgroup.PROJECT_ITEMS i on d.DOCNUM = i.ITEM_ID
join mhgroup.PROJECTS f on i.PRJ_ID = f.PRJ_ID
join mhgroup.PROJECTS w on f.TREE_ID = w.PRJ_ID
where f.PRJ_ID in
(
select f.prj_id
from mhgroup.PROJECTS f
join mhgroup.PROJECT_ITEMS i on i.PRJ_ID = f.PRJ_ID
group by f.prj_id
having count(i.ITEM_ID) > 9999
)
order by w.PRJ_ID, f.PRJ_ID, d.DOCNUM
  • If the script returns a list of documentscopy the results into a spreadsheet and reach out to your FYI Onboarding Specialist. Do not continue with the remaining scripts.

  • If the script does not return a list of documents, proceed to run Script 3.1.

Step 3 - Query to identify the total number of documents

The following query can be used to identify the total number of documents in the iManage Filesite/Desksite database.

This can be useful when estimating how long the export may take as well as providing a guide for the total number of files expected to be exported.

  • To estimate the export time assume each file exported will take 1 sec
  • This is dependent on environmental factors such as load on the system, background tasks running etc. so the actual time to export may differ

Script 3.1 - Aggregate of Documents by Client/Matter

This will create a table that groups the total number of documents by ClientID and Matter

select c1alias as ClientID, c2alias as MatterID, count(*) as Total
from mhgroup.DOCMASTER
group by C1ALIAS, C2ALIAS
Order by COUNT(*) Desc

This script will return a count of documents grouped by Client and Matter. 

Once you have completed the above steps, please contact your FYI Onboarding Specialist to continue with the next steps. 

Was this article helpful?
0 out of 0 found this helpful