Prioritizing documents based on different criteria

Prioritizing documents based on different criteria

The following scripts provide examples that can be modified to apply different priorities to documents. 


Prioritize Documents by "Max Matter Date"

All the documents in a matter are prioritized based on the Created/Modified date of the most recently added/edited document.  In this way, all documents for matters that have been worked on recently transfer sooner and documents related to matters that haven't been touched in a while transfer later.
Info
For giant migrations, this is the best way to prioritize documents.

  1. ;WITH P1 AS (
  2.     --Get the date of the most recent document for each parent...
  3.     SELECT
  4.         Final_Parent_Type,
  5.         Final_Parent_Id,
  6.         MAX(COALESCE(Final_Updated_At, Final_Created_At, '1900-01-01')) MaxDocDate
  7.     FROM (
  8.         SELECT
  9.             Final_Parent_Type,
  10.             Final_Parent_Id,
  11.             Final_Created_At,
  12.             Final_Updated_At
  13.         FROM
  14.             __M_Documents_Digital_Files
  15.         UNION
  16.         SELECT
  17.             Files.Final_Parent_Type,
  18.             Files.Final_Parent_Id,
  19.             Versions.Final_Created_At,
  20.             Versions.Final_Updated_At
  21.         FROM
  22.             __M_Documents_Digital_Files Files,
  23.             __M_Documents_Digital_Files_Versions Versions
  24.         WHERE 1=1
  25.             AND Versions.Final_DocumentFile_Id = Files.Id
  26.     ) DateList
  27.         
  28.     GROUP BY
  29.         Final_Parent_Type,
  30.         Final_Parent_Id

  32. ), P2 AS (
  33.     --Calculate the Action_Priority for each Parent based on MaxDocDate
  34.     SELECT
  35.         P1.Final_Parent_Type,
  36.         P1.Final_Parent_Id,
  37.         Action_Priority = 5000
  38.             + (DATEPART(YEAR,  MaxDocDate) * 100) --Prioritize by the year of the most recent doc for each parent.
  39.             --+ (DATEPART(MONTH, MaxDocDate) * 001) --To get more granular, you can add MONTH by removing the comment ('--') at the start of this line.
  40.     FROM
  41.         P1
  42. )
  43. UPDATE --Set the action Priority
  44.     df
  45. SET
  46.     Action_Priority = P2.Action_Priority
  47. FROM
  48.     __M_Documents_Digital_Files df,
  49.     P2
  50. WHERE 1=1
  51.     AND P2.Final_Parent_Id = df.Final_Parent_Id
  52.     AND P2.Final_Parent_Type = df.Final_Parent_Type

Prioritize Documents for Active Matters

Info
For small migrations, this is the recommend way to prioritize documents,
  1. UPDATE
  2.     __M_Documents_Digital_Files
  3. SET
  4.     Action_Group = 6000
  5. WHERE 1=1
  6.     AND Final_Parent_Type = '__M_Matters'
  7.     AND Final_Parent_Id IN (
  8.         SELECT
  9.             Id
  10.         FROM
  11.             __M_Matters
  12.         WHERE 1=1
  13.             AND Final_Status = 'Active'
  14.         )

Prioritize Large/Small Documents

If you are restoring into a system that has different action limits at different times, this can help you optimize throughout.

Big documents should be prioritized when action limits are small and small documents should be prioritized when action limits are big.

  1. --Prioritize documents based on size.
  2. UPDATE
  3.     __M_Documents_Digital_Files
  4. SET
  5.     Action_Group = 6000
  6. WHERE 1=1
  7.     AND Id IN (
  8.         SELECT
  9.             Final_DocumentFile_Id
  10.         FROM
  11.             __M_Documents_Digital_Files_Versions
  12.         WHERE 1=1
  13.             --Change this to be < or > depending on what you want.
  14.             AND Final_Meta_ContentSize < 10 * 1024 * 1024 -- 10MB
  15.             AND Result_Id = ''
  16.     )

Prioritize Documents for Specific Matters

  1. --Prioritize documents for specific matters.
  2. UPDATE
  3.     __M_Documents_Digital_Files
  4. SET
  5.     Action_Group = 6000
  6. WHERE 1=1
  7.     AND Result_Id = ''
  8.     AND Final_Parent_Type = '__M_Matters'
  9.     AND Final_Parent_Id IN (
  10.         SELECT
  11.             Id
  12.         FROM
  13.             __M_Matters
  14.         WHERE 1=1
  15.             AND Final_ReferenceCode IN (
  16.                'ABC-001',
  17.                'ABC-002'
  18.             )
  19.         )

Prioritize Documents whose versions have not been fully uploaded

  1. UPDATE
  2.     __M_Documents_Digital_Files
  3. SET
  4.     Action_Group = 6000
  5. WHERE 1=1
  6.     AND Id IN (
  7.         SELECT
  8.             Final_DocumentFile_Id
  9.         FROM
  10.             __M_Documents_Digital_Files_Versions
  11.         WHERE 1=1
  12.             AND Result_Id = ''
  13.         )

Prioritize Documents that have not been started

  1. UPDATE
  2.     __M_Documents_Digital_Files
  3. SET
  4.     Action_Group = 6000
  5. WHERE 1=1
  6.     AND Result_Id = ''

    • Related Articles

    • Synthesize: Missing Document Categories for Documents

      The following script will synthesize document categories for invalid Final_DocumentCategory_Id values on documents. -- Some documents are linked to categories that no longer exist. -- Create dummy document categories based on these values. INSERT ...

    • Create an Author and Typist Custom Field for Documents

      --In Document Management systems that have Author and Typists, --Universal Migrator generally maps columns as follows: --Typist (the actual person who created the document) = Final_Created_By_Id --Author (the person the typist created the document ...

    • SQL Spreadsheets: Manually Prioritizing Matters

      --The following SQL will generate a list of matters and allow their priorities to easily be specified. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...

    • Synthesize Missing Matters for Documents

      Some systems only allow documents to be linked to matters. These scripts will allow you to restore data into these systems. When Matters No Longer Exist The following script with synthesize matters for document related to matters that no longer ...

    • Promoting Document Versions into Documents

      --The following playbook is a TEMPLATE that you can use to promote all document versions into actual documents. --When a document has a category, append the folder with the document category ID. UPDATE __M_Documents_Digital_Files SET --Prepend the ...