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_Participant_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