Create an Author and Typist Custom Field for Documents

Create an Author and Typist Custom Field for Documents

  1. --In Document Management systems that have Author and Typists,
  2. --Universal Migrator generally maps columns as follows:
  3. --Typist (the actual person who created the document) = Final_Created_By_Id
  4. --Author (the person the typist created the document for) = Final_Created_OnBehalfOf_Id
  5. --
  6. --It is expected that Author and Typist reference values from the __M_Users although some systems
  7. --Do allow these to have their own custom picklist values.
  8. --
  9. --These scripts will do the following:
  10. -- * Create Author and Typist custom field definitions
  11. -- * Create __M_CustomField_Presets (Picklist items) for each based on the __M_Users
  12. -- * Create Custom Field Values for each document that represent the Author and Typist.
  13. --
  14. -- Please note that this script is a TEMPLATE that is designed to be 
  15. -- Customized per your scenario.


  18. --Create the Author Field
  19. INSERT INTO __M_CustomField_Definitions (
  20.     Id,
  21.     Final_Kind,
  22.     Final_Subject,
  23.     Final_Parent_Type
  24. ) 
  25. VALUES (
  26.     'Author',
  27.     '__M_CustomField_Presets',
  28.     'Author',
  29.     '__M_Documents_Digital_Files'
  30. )

  32. --Create Author picklist items based on users
  33. INSERT INTO __M_CustomField_Presets (
  34.     Id,
  35.     Final_CustomFieldDefinition_Id,
  36.     Final_ReferenceCode,
  37.     Final_Subject
  38. )
  39. SELECT
  40.     CONCAT('Author --- ', Id),
  41.     'Author',
  42.     Final_UserName, --We're using the UserName as the reference code.  Maybe use Final_ReferenceCode
  43.     Final_FullName  --The picklist item is the user's full name.
  44. FROM
  45.     __M_Users

  47. --Now create the author's custom field value
  48. INSERT INTO __M_CustomField_Values (
  49.     Id,
  50.     Final_CustomFieldDefinition_Id,
  51.     Final_Parent_Id,
  52.     Final_Parent_Type,
  53.     Final_Value
  54. )
  55. SELECT
  56.     CONCAT(Id, ' --- ', 'Author'),
  57.     'Author',
  58.     Id,
  59.     '__M_Document_Digital_Files',
  60.     CONCAT('Author --- ', Final_Created_OnBehalfOf_Id)
  61. FROM
  62.     __M_Documents_Digital_Files
  63. WHERE 1=1
  64.     --Ignore blank items so we don't create garbage
  65.     AND Final_Created_OnBehalfOf_Id != ''
  66.     --Ignore items that don't map to a valid user so we don't create errors.
  67.     AND Final_Created_OnBehalfOf_Id IN (SELECT Id FROM __M_Users)
  68.     
  69.         
  70. --Create the Typist Field
  71. INSERT INTO __M_CustomField_Definitions (
  72.     Id,
  73.     Final_Kind,
  74.     Final_Subject,
  75.     Final_Parent_Type
  76. ) 
  77. VALUES (
  78.     'Typist',
  79.     '__M_CustomField_Presets',
  80.     'Typist',
  81.     '__M_Documents_Digital_Files'
  82. )

  84. --Create Typist picklist items based on users
  85. INSERT INTO __M_CustomField_Presets (
  86.     Id,
  87.     Final_CustomFieldDefinition_Id,
  88.     Final_ReferenceCode,
  89.     Final_Subject
  90. )
  91. SELECT
  92.     CONCAT('Typist --- ', Id),
  93.     'Typist',
  94.     Final_UserName, --We're using the UserName as the reference code.  Maybe use Final_ReferenceCode
  95.     Final_FullName  --The picklist item is the user's full name.
  96. FROM
  97.     __M_Users

  99. --Now create the Typist's custom field value
  100. INSERT INTO __M_CustomField_Values (
  101.     Id,
  102.     Final_CustomFieldDefinition_Id,
  103.     Final_Parent_Id,
  104.     Final_Parent_Type,
  105.     Final_Value
  106. )
  107. SELECT
  108.     CONCAT(Id, ' --- ', 'Typist'),
  109.     'Typist',
  110.     Id,
  111.     '__M_Document_Digital_Files',
  112.     CONCAT('Typist --- ', Final_Created_By_Id)
  113. FROM
  114.     __M_Documents_Digital_Files
  115. WHERE 1=1
  116.     --Ignore blank items so we don't create garbage
  117.     AND Final_Created_By_Id != ''
  118.     --Ignore items that don't map to a valid user so we don't create errors.
  119.     AND Final_Created_By_Id IN (SELECT Id FROM __M_Users)
  120.     
  121.