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