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 field, only one will be used.
- --Update the custom field definitions to link to contacts/matters.
- UPDATE
- __M_CustomField_Definitions
- SET
- Final_Parent_Type = V4.Final_RelatedTo_Type
- FROM
- __M_CustomField_Definitions V1,
- __M_CustomObject_Definitions V2,
- __M_CustomObject_Instances V3,
- __M_CustomObject_Instances_Related_Entities V4
- WHERE 1=1
- AND V1.Original_Parent_Type = V2.Id
- AND V2.Id = V3.Original_CustomObjectDefinition_Id
- AND V3.Id = V4.Original_CustomObjectInstance_Id
- --Update the values to have new parent types.
- UPDATE
- __M_CustomField_Values
- SET
- Final_Parent_Type = V3.Final_RelatedTo_Type,
- Final_Parent_Id = V3.Final_RelatedTo_Id
- FROM
- __M_CustomField_Values V1,
- __M_CustomObject_Instances V2,
- __M_CustomObject_Instances_Related_Entities V3
- WHERE 1=1
- AND V1.Original_Parent_Id = V2.Id
- AND V1.Original_Parent_Type = V2.Original_CustomObjectDefinition_Id
- AND V2.Id = V3.Original_CustomObjectInstance_Id
- a
Related Articles
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. -- Transform Custom Fields on ...
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' ...
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 ...
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 ...
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 ...