Matters
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. ...
Create Notes from Matter Descriptions
Some platforms do not support matter descriptions. The following will transform a matter description into a matter note. --Transform Matter Descriptions to Matter Notes INSERT INTO __M_Common_Notes (Id, Final_Parent_Id, Final_Parent_Type, ...
Delete unused Practice Areas
The following SQL script will delete unused practice areas. --Delete any practice areas that are not used UPDATE __M_Matters_PracticeAreas SET Final_Status = 'Deleted' WHERE 1=1 AND Id NOT IN( SELECT Final_PracticeArea_Id FROM __M_Matters WHERE ...
Synthesize Missing Practice Areas
The following SQL script will create practice areas where they do not exist in the Matters table. --Synthesize missing practice areas from Matters INSERT INTO __M_Matters_PracticeAreas( Id, Final_Subject ) SELECT DISTINCT Final_PracticeArea_Id, ...
Synthesize Missing Client Contacts for Matters
This script below will create dummy contacts where the Matter is assigned to a contact that no longer exists. --Some of the Matters are linked to contacts that no longer exist. --This will create dummy contacts from the missing links. INSERT INTO ...
Matters: Prepending Client Names to Matters
The following script will prepend Client Names to Matter Names. For Example: Divorce 2016 would become John Smith --- Divorce 2016 Here is the relevant script: --Prepend all matter descriptions with the client name. UPDATE __M_Matters SET ...
Matters: Trimming Client Numbers from Matter Numbers
The following script will peel off client numbers from matter numbers. For example, if a matter has a ReferenceCode like: ABC-0001 After the script runs, it will have a reference code of: 0001 Scenario 1: There are Client Reference Codes In some ...
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 ...