Moving Documents into Matters that are Nested under Other Entries

Moving Documents into Matters that are Nested under Other Entries

Some systems allow documents to be nested under virtually any other kind of record.  This can be challenging when moving documents into systems that have a limited number of parent types.



  2. --The following script creates a "MISC DOCUMENTS" matter
  3. --And then moves any documents that do not have a valid Parent Type into this matter.

  5. GO

  7. DECLARE @Id NVARCHAR(MAX) = 'MISC DOCUMENTS'
  8. DELETE FROM __M_Matters WHERE Id = @Id
  9. INSERT INTO __M_Matters (Id, Final_Subject)
  10. VALUES (@Id, @Id)

  12. UPDATE
  13. __M_Documents_Digital_Files
  14. SET
  15. Final_Parent_Id = @Id,
  16. Final_Parent_Type = '__M_Matters',
  17. Final_Folder_RelativePath = CASE 
  18. WHEN COALESCE(Final_Created_At, Final_Updated_At) IS NULL THEN CONCAT('\', Final_Parent_Type, Final_Folder_RelativePath)
  19. ELSE CONCAT('\', Final_Parent_Type, '\', FORMAT( COALESCE(Final_Created_At, Final_Updated_At), 'yyyy-MM'), Final_Folder_RelativePath)
  20. END
  21. WHERE 1=1
  22. AND Final_Parent_Type NOT IN (
  23. --Update this to have the list of valid document Parent Types for your destination.
  24. '__M_Contacts',
  25. '__M_Matters'
  26. )