Copy User and Contact Picklists from __M_CustomField_Definitions to __M_Matters_Participating_Entities

Copy User and Contact Picklists from __M_CustomField_Definitions to __M_Matters_Participating_Entities

Some systems do not support custom fields that are User/Contact Picklist but they do support participants.

Use this script to copy custom field values/definitions into the Matter Participants table.

  1. --We need to copy some records from custom fields into matter participants.
  2. INSERT INTO __M_Matters_Participating_Entities (
  3.     Id,
  4.     Final_Matter_Id,
  5.     Final_Participant_Type,
  6.     Final_Participant_Id,
  7.     Final_Role
  8. )

  9. SELECT
  10.     CONCAT(V1.Id, V2.Id),
  11.     V2.Final_Parent_Id,
  12.     V1.Final_Kind,
  13.     V2.Final_Value,
  14.     V1.Final_Subject
  15. FROM
  16.     __M_CustomField_Definitions V1,
  17.     __M_CustomField_Values V2
  18. WHERE 1=1
  19.     --Do the conversion for custom fields that are Contact or User picklists
  20. --If you only want Users, then adjust the following.
  21.     AND V1.Final_Kind IN ('__M_Contacts', '__M_Users')
  22.     --And extend the Matters table.
  23.     AND V1.Final_Parent_Type IN ('__M_Matters')
  24.     AND V2.Final_CustomFieldDefinition_Id = V1.Id