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.         

    • Related Articles

    • Create a "Legacy Matter Number" Custom Field

      Some firms rely heavily on Matter Numbers to search for a specific matter. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...

    • Create a "Legacy Client Number" Custom Field

      Some firms rely heavily on Client Numbers to search for a specific client. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...

    • Create an "Employer" Contact from a Custom Field.

      In some systems, contacts do not have an Employer relation and only have a text field that represents the employer. This script will help when moving firms into platforms that allow an actual contact to be specified for the employer. --Create Company ...

    • Copy a Custom Field into a Core Field

      Some systems don't have certain core fields and clients often create a custom field that they use instead. The following script will let you copy values from a custom field named 'Opened' into __M_Matters.Final_Date_Opened . You can adjust this ...

    • Custom Field Kinds

      The following is a list of the valid Kinds of custom fields. Kind Notes TextLine TextArea HtmlArea Number Represents any kind of number. Integer Represents only whole numbers Money Date Time TimeSpan Represents durations. DateTime EmailAddress ...