Deduplicate Matter Participating Entities

Some systems will have the same entity (contact, user, etc...) associated with a matter multiple times with exactly the same role. If the signature of the relationship is exactly the same, the following script will eliminate the duplicates.

--Deduplicate Matter Participating Entites
;WITH P AS
(
SELECT
Id,
ROW_NUMBER() OVER
(PARTITION BY Final_Matter_Id, Final_Participant_Type, Final_Participant_Kind, Final_Participant_Role, Final_Participant_Id ORDER BY [Row] DESC) DupNumber
FROM
__M_Matters_Participating_Entities
)
UPDATE
mpe
SET
Final_Status = 'Deleted',
Comment = CONCAT('Duplicate: ', P.DupNumber)
FROM
__M_Matters_Participating_Entities mpe
INNER JOIN
P
ON mpe.Id = P.Id
WHERE 1=1
AND DupNumber>1

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 ...
Create a "Legacy Originating Attorney" field (from __M_Matters_Participating_Entities)
Most destination systems will only let you set the Originating Attorney and Responsible Attorney to an active user. This is why user mapping is used to map inactive users from the legacy system to an active user in the new system. But the firm may ...
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 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 ...
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 ...

Deduplicate Matter Participating Entities

Deduplicate Matter Participating Entities

Related Articles

Copy User and Contact Picklists from __M_CustomField_Definitions to __M_Matters_Participating_Entities

Create a "Legacy Originating Attorney" field (from __M_Matters_Participating_Entities)

Matter Participants: Data Cleanup

Create Contact Categories from Matter Participants

Flatten Custom Object fields into Contact/Matter Fields