Document Register: Moving digital documents into a Document Register Matter

Document Register: Moving digital documents into a Document Register Matter

  1. /*
  2. This script is a template that will help you move library documents
  3. (document register / safe custody records) into a general 'Document Register' matter.

  5. You should consider this a TEMPLATE that you may
  6. want to tweak and adjust for each client's specific needs.

  8. */

  10. --Create Subfolders for Matter packets
  11. UPDATE
                   __M_Documents_Digital_Files
  12. SET
  13.                Final_FolderName = CONCAT('\', Contact.Final_FullName, '\', Matter.Final_ReferenceCode, ' --- ', Matter.Final_Description, Final_FolderName),
  14.                Final_Parent_Id = '__DOCUMENT_REGISTER',
                   Final_Parent_Type = '__M_Matters'
  15. FROM
  16.                 __M_Documents_Digital_Files V0,
                   __M_Documents_Physical_Files LD,
                   __M_Documents_Physical_Folders LF,
                   __M_Matters Matter,
                   __M_Contacts Contact
  17. WHERE 1=1
  18.                 AND V0.Final_Parent_Type = '__M_Library_Documents'
  19.                 AND V0.Final_Parent_Id = LD.Id
  20.                 AND LD.Final_LibraryFolder_Id = LF.Id
  21.                 AND LF.Final_Parent_Type = '__M_Matters'
  22.                 AND LF.Final_Parent_Id = Matter.Id
  23.                 AND Matter.Final_ClientContact_Id = Contact.Id
  24.                 
  25. --Create subfolders for contact packets
  26. UPDATE
  27.                 __M_Documents_Digital_Files
  28. SET
  29.                 Final_FolderName = CONCAT('\', Contact.Final_FullName, '\', 'Contact Register', Final_FolderName),
  30.                 Final_Parent_Id = '__DOCUMENT_REGISTER',
  31.                 Final_Parent_Type = '__M_Matters'
  32. FROM
  33.                  __M_DocumentFiles V0,
  34.                 __M_Documents_Physical_Files LD,
  35.                 __M_Documents_Physical_Folders LF,
  36.                 __M_Contacts Contact
  37. WHERE 1=1
  38.                  AND V0.Final_Parent_Type = '__M_Library_Documents'
  39.                 AND V0.Final_Parent_Id = LD.Id
  40.                 AND LD.Final_LibraryFolder_Id = LF.Id
  41.                 AND LF.Final_Parent_Type = '__M_Contacts'
  42.                 AND LF.Final_Parent_Id = Contact.Id
  43.                 
  44. --Now create the document register matter
  45. INSERT INTO __M_Matters(
  46.                 Id, Final_ClientContact_Id, Final_Subject
  47. ) VALUES (
  48.                 '__DOCUMENT_REGISTER', '__INTERNAL', 'Document Register'
  49.                   )

  51. --(OPTIONAL) Rename the digital documents to have names that match the physical documents
  52. UPDATE
  53.                 __M_Documents_Digital_Files
  54. SET
  55.                 Final_FileName = CONCAT(V1.Final_Type, ' --- ', V1.Final_Subject, ' --- ', FORMAT(V1.Final_Date, 'yyyy-MM-dd') )
  56. FROM
  57.                 __M_Documents_Digital_Files V0,
  58.                 __M_Documents_Physical_Files V1
  59. WHERE 1=1
  60.                 AND V0.Final_Parent_Type = '__M_Library_Documents'
  61.                 AND V0.Final_Parent_Id = V1.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 ...

    • 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 ...

    • Prepending the Document Category to the Path

      Some systems support Document Categories and others support Folders. The SQL Script below will prepend the document category into the folder path. --When a document has a category, prepend the folder with the document category. UPDATE ...

    • Prioritize documents for Active (Open) Matters

      --Prioritize documents for Active (Open) Matters UPDATE __M_Documents_Digital_Files SET Batch_Group = -100 FROM __M_Documents_Digital_Files V1, __M_Matters V2 WHERE 1=1 AND V1.Final_Parent_Id = V2.Id AND V1.Final_Parent_Type = '__M_Matters' AND ...

    • Prioritizing documents based on different criteria

      The following scripts provide examples that can be modified to apply different priorities to documents. Prioritize Large/Small Documents If you are restoring into a system that has different action limits at different times, this can help you ...