Promoting Document Versions into Documents

Promoting Document Versions into Documents


  2. --The following playbook is a TEMPLATE that you can use to promote all document versions into actual documents.

  4. --When a document has a category, append the folder with the document category ID.
  5. UPDATE 
  6.     __M_Documents_Digital_Files
  7. SET
  8.     --Prepend the Category
  9.     Final_Folder_RelativePath = CONCAT(Final_Folder_RelativePath, Final_DocumentCategory_Id, '\')
  10. FROM
  11.     __M_Documents_Digital_Files V1
  12. WHERE 1=1


  15. --Replace \\ with \
  16. UPDATE
  17.     __M_Documents_Digital_Files
  18. SET
  19.     Final_Folder_RelativePath = REPLACE(Final_Folder_RelativePath, '\\', '\')

  21. select top 100 * from __M_Documents_Digital_Files_Versions

  23. --Update all versions to have a 3-digit version number
  24. UPDATE __M_Documents_Digital_Files_Versions SET Final_ReferenceCode = CONCAT('0', Final_ReferenceCode) WHERE LEN(Final_ReferenceCode) < 3
  25. UPDATE __M_Documents_Digital_Files_Versions SET Final_ReferenceCode = CONCAT('0', Final_ReferenceCode) WHERE LEN(Final_ReferenceCode) < 3
  26. UPDATE __M_Documents_Digital_Files_Versions SET Final_ReferenceCode = CONCAT('0', Final_ReferenceCode) WHERE LEN(Final_ReferenceCode) < 3


  29. --Create a document entry for each version
  30. INSERT INTO __M_Documents_Digital_Files (
  31. Id, 
  32. Final_Status, 
  33. Final_Parent_Id, 
  34. Final_Parent_Type, 
  35. Final_Folder_RelativePath, 
  36. Final_File_Name, 
  37. Final_File_Extension, 
  38. Final_Created_At, 
  39. Final_Updated_At,

  41. Original_Status, 
  42. Original_Parent_Id, 
  43. Original_Parent_Type, 
  44. Original_Folder_RelativePath, 
  45. Original_File_Name, 
  46. Original_File_Extension, 
  47. Original_Created_At, 
  48. Original_Updated_At
  49. )
  50. SELECT
  51. CONCAT('PROMOTED --- ', Versions.Id),

  53. Files.Final_Status,
  54. Files.Final_Parent_Id, 
  55. Files.Final_Parent_Type, 
  56. Files.Final_Folder_RelativePath, 
  57. CONCAT(Files.Final_File_Name,  '_v', Versions.Final_ReferenceCode),
  58. COALESCE( NULLIF(Versions.Final_File_Extension,''), NULLIF(Files.Final_File_Extension,''), ''),
  59. COALESCE( Versions.Final_Created_At, Files.Final_Created_At),
  60. COALESCE( Versions.Final_Updated_At, Files.Final_Updated_At),

  62. Files.Original_Status,
  63. Files.Original_Parent_Id, 
  64. Files.Original_Parent_Type, 
  65. Files.Original_Folder_RelativePath, 
  66. CONCAT(Files.Original_File_Name,  '_v', Versions.Original_ReferenceCode),
  67. COALESCE( NULLIF(Versions.Original_File_Extension,''), NULLIF(Files.Original_File_Extension,''), ''),
  68. COALESCE( Versions.Original_Created_At, Files.Original_Created_At),
  69. COALESCE( Versions.Original_Updated_At, Files.Original_Updated_At)

  71. FROM
  72. __M_Documents_Digital_Files Files,
  73. __M_Documents_Digital_Files_Versions Versions
  74. WHERE 1=1
  75. AND Files.Id = Versions.Final_DocumentFile_Id
  76. AND Versions.Id NOT LIKE 'PROMOTED --- %'
  77. AND Files.Id NOT LIKE 'PROMOTED --- %'



  81. INSERT INTO __M_Documents_Digital_Files_Versions(
  82. Id, 

  84. Final_Status, 
  85. Final_ReferenceCode, 
  86. Final_DisplayOrder, 
  87. Final_Content_FromPath, 
  88. Final_File_Name, 
  89. Final_File_Extension, 
  90. Final_Created_At, 
  91. Final_Updated_At,
  92. Final_DocumentFile_Id,

  94. Original_Status, 
  95. Original_ReferenceCode, 
  96. Original_DisplayOrder, 
  97. Original_Content_FromPath, 
  98. Original_File_Name, 
  99. Original_File_Extension, 
  100. Original_Created_At, 
  101. Original_Updated_At,
  102. Original_DocumentFile_Id
  103. )
  104. SELECT
  105. CONCAT('PROMOTED --- ', Id),

  107. Final_Status,
  108. Final_ReferenceCode,
  109. Final_DisplayOrder,
  110. Final_Content_FromPath,
  111. Final_File_Name,
  112. Final_File_Extension,
  113. Final_Created_At,
  114. Final_Updated_At,
  115. CONCAT('PROMOTED --- ', Id),

  117. Original_Status,
  118. Original_ReferenceCode,
  119. Original_DisplayOrder,
  120. Original_Content_FromPath,
  121. Original_File_Name,
  122. Original_File_Extension,
  123. Original_Created_At,
  124. Original_Updated_At,
  125. CONCAT('PROMOTED --- ', Id)
  126. FROM
  127. __M_Documents_Digital_Files_Versions
  128. WHERE 1=1
  129. AND Id NOT LIKE 'PROMOTED --- %'



  133. -- Mark all files to be not produced
  134. UPDATE __M_Documents_Digital_Files SET Final_Status = 'Deleted'

  136. -- Mark these clients (6digit) to produce their files (note, original status is used so we  do not produce files that were deleted in netdocs)
  137. -- We can add as many clients to the production as we would like, just replicate the line.
  138. UPDATE __M_Documents_Digital_Files SET Final_Status = Original_Status WHERE Id like 'PROMOTED --- %' AND Final_Parent_Id LIKE '030487 --- %'
  139. UPDATE __M_Documents_Digital_Files SET Final_Status = Original_Status WHERE Id like 'PROMOTED --- %' AND Final_Parent_Id LIKE '064263 --- %'
  140. UPDATE __M_Documents_Digital_Files SET Final_Status = Original_Status WHERE Id like 'PROMOTED --- %' AND Final_Parent_Id LIKE '063990 --- %'

  142. UPDATE __M_Documents_Digital_Files_Versions SET Final_DocumentFile_Id = Id, Original_DocumentFile_Id = Id where Id like 'PROMOTED%'

  144. UPDATE __M_Documents_Digital_Files SET Batch_Number = 0, Result_Id = ''
  145. UPDATE __M_Documents_Digital_Files_Versions SET Batch_Number = 0, Result_Id = ''

  147. --RESTORE SETTINGS:
  148. --RESTORE TO: \\samds\netdocs\FOR HUMANS
  149. --RESTORE METHOD: By Copying Files
  150. --MATTER PATH TEMPLATE: {Contact_ReferenceCode} --- {Contact_FullName}\{Matter_ReferenceCode} --- {Matter_Subject}

    • Related Articles

    • Promote Document Version Notes to Document Notes

      --The following script will promote document version notes --into document notes. This is useful when a destination system --supports notes on documents but does not support notes on --specific document versions. -- --If you are using this script, ...

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

    • Document Register: Moving digital documents into a Document Register Matter

      /* This script is a template that will help you move library documents (document register / safe custody records) into a general 'Document Register' matter. You should consider this a TEMPLATE that you may want to tweak and adjust for each client's ...

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

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