Convert User-Select/Contact-Select Custom Fields => Participants

Convert User-Select/Contact-Select Custom Fields => Participants

Some systems do not support User-Select or Contact Select custom fields. 

The following query will transform User-Select fields into Matter Participants.

This script can easily be adapted for Contact-Select fields.

  1. -- Transform Custom Fields on Matters that reference Users
  2. -- into Matter Participating Users.
  3. INSERT INTO __M_Matters_Participating_Entities (
  4.     Id, 
  5.     Final_Matter_Id, 
  6.     Final_Participant_Id,
  7.     Final_Participant_Type,
  8.     Final_Role
  9. )
  10. SELECT
  11.     CONCAT('TRANSFORM --- ', V2.Id),
  12.     V2.Final_Parent_Id,
  13.     V2.Final_Value,
  14.     V1.Final_Kind,
  15.     V1.Final_Subject
  16. FROM
  17.   __M_CustomField_Definitions V1,
  18.   __M_CustomField_Values V2
  19. WHERE 1=1
  20.   --Change this to '__M_Contacts' if you want to do this for contact custom fields
  21.   AND V1.Final_Kind = '__M_Users'
  22.   AND V1.Final_Parent_Type = '__M_Matters'
  23.   AND V1.Id = V2.Final_CustomFieldDefinition_Id

    • Related Articles

    • 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. --We need to copy some records from custom fields ...

    • Matter Participants: Data Cleanup

      Table Overview Matter Participants are contacts that are related to Matters. They exist in the table: __M_Matters_Participating_Entities They have three primary fields: Field Description Final_Matter_Id The Id of the Matter. Final_Participant_Type ...

    • Custom Fields: Creating Custom Fields via Queries

      Sometimes you may need to create a new custom field to handle an unmapped column in a database. The queries below will help you do that. -- This is the ID we are giving our custom field definition. DECLARE @FieldId NVARCHAR(MAX) = 'ConNo --- CUSTOM' ...

    • Flatten Custom Object fields into Contact/Matter Fields

      WARNING: Many systems that support custom objects allow multiple rows in custom objects. Many systems that allow custom fields only allow one single value for each field. When you use this script, if a matter has two values set for the same custom ...

    • Create Contact Categories from Matter Participants

      --The following script will create Contact Categories from the roles --that Contacts are linked to on Matters and then assign the contacts to those categories. GO INSERT INTO __M_Contacts_Categories ( Id, Final_Subject ) SELECT DISTINCT ...