This is a highly technical process

Experience with SQL queries is necessary to perform Join Migrations.

Sometimes you may have customers who have data that needs to be restored into an existing system that is already set up and active.  This type of a migration is called a Merge Migration because you are merging the data you want to restore into an existing system.  Because multiple systems are involved, there are additional complexities that must be navigated. 

Example Process Overview

• A Clio account that has already had contacts and matters imported into it.
• A Time Matters server that has documents you want to restore into Clio.

1. Back up Contacts, Matters & Document Categories

Using the Universal Migrator, back up the Contacts, Matters, and Document Categories from Clio into a Universal Database named UniversalDatabase1. 

2. Indicate items Should Not Be Re-Uploaded

Once the contacts, matters, and document categories have been backed-up from Clio, the Universal Database should be updated to indicate these items have already been created in Clio and do not need to be recreated.  This is done by updating the Result_Id column to contain the Id of the existing contact/matter/document category in the live Clio system.  Because the items were just backed-up from the exact same Clio database, the Id and the Result_Id need to be the same.

UPDATE __M_Contacts           SET Result_Id = Id
UPDATE __M_Matters            SET Result_Id = Id
UPDATE __M_DocumentCategories SET Result_Id = Id

3. Back up Documents from Time Matters

Using the Universal Migrator, back up the documents from Time Matters into UniversalDatabase1. 

4. Back up Matters and DocumentCategories from Time Matters

Using the Universal Migrator, back up the matters and Document Categories from Time Matters into UniversalDatabase2.  You are backing up the matters into a separate database that we'll use to query against. 

5. Link documents from Time Matters to Clio

SELECT
    Clio_Id        = Clio_Data.Id, Clio_Data.Final_DisplayNumber,
    TimeMatters_Id =   TM_Data.Id,   TM_Data.Final_DisplayNumber,
FROM
    UniversalDatabase1.dbo.__M_Matters Clio_Data,
    UniversalDatabase2.dbo.__M_Matters   TM_Data
WHERE 1=1
    AND Clio_Data.Final_DisplayNumber = TM_Data.Final_DisplayNumber

Once you have confirmed your query is correct, you should modify it to update all documents with the new Final_Matter_Id from Clio.  To do this, you will use a query like the following.  This query matches matters between the two systems based on their Matter Display Numbers (ie. ABC-001)

UPDATE
    UniversalDatabase1.dbo.__M_DocumentFiles
SET
    Final_Matter_Id = (
        SELECT
            Clio_Id = Clio_Data.Id
        FROM
            UniversalDatabase1.dbo.__M_Matters Clio_Data,
            UniversalDatabase2.dbo.__M_Matters   TM_Data
        WHERE 1=1
            AND Clio_Data.Final_DisplayNumber = TM_Data.Final_DisplayNumber
            AND TM_Data.Id = Original_Matter_Id
    )

6.  Verify Your Work

Once you have done that, you should double check your work to make sure that all documents have had their Final_Matter_Id updated.  If the Final_Matter_Id and Original_Matter_Id are different, then this indicates the document now references a Clio Matter Id and not a Time Matters Id, but, if they are the same, then that means there was not a match between the two systems and there something that needs to change.

If this query returns no results, then everything is done correctly:

SELECT
    *
FROM
    UniversalDatabase1.dbo.__M_DocumentFiles
WHERE 0=1
   OR Final_Matter_Id = Original_Matter_Id
   OR Final_Matter_Id = ''

7.  Apply Any Corrections

More than likely, the first time you run the above steps, there will be at least one item that does not match between the systems.  This is to be expected but must be corrected before doing the restore into Clio.

There are three ways to resolve this issue:

1. Use the Clio application to edit the unmatched matters in Clio to have a DisplayNumber that matches the Time Matters DisplayNumber.  (Recommended)
       OR
2. Use the Time Matters application to edit the unmatched matters in Time Matters to have a DisplayNumber that matches the Clio DisplayNumber.  (Recommended)
       OR
3. Write a series of SQL Scripts that update the Universal Database to point documents from specific matters to specific matters.  (Not Recommended)

If you have done either of the recommended actions, you should delete UniversalDatabase1 and UniversalDatabase2 and repeat this entire process.

8.  Perform the Data Restore

 Now that everything is properly linked between systems, you are now safe to use the Universal Migrator to restore the documents from UniversalDatabase1 into Clio.