Detecting Duplicates by Reference Code
In some scenarios, you need to ensure that a reference code is completely unique among a record type.
To do this, use the following SQL script:
- --Look for duplicate records in the __M_Matters table
- SELECT
- *
- FROM
- --Change this to the table we want to identify duplicates in
- __M_Matters V1,
- (
- SELECT
- Final_ReferenceCode,
- COUNT(*) as Duplicates
- FROM
- __M_Matters --This needs to match the same table as above
- GROUP BY
- Final_ReferenceCode
- HAVING
- COUNT(*) > 1
- ) V2
- WHERE 1=1
- AND V1.Final_ReferenceCode = V2.Final_ReferenceCode
- ORDER BY
- V1.Final_ReferenceCode
Related Articles
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 ...
Audit Scripts: Row Counts and Progress Monitoring
This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...
Other: Creating Unique Display Numbers
The following script will create unique Final_ReferenceCode for all Contacts. It can be easily modified to apply to other record types. Please note that the Final_ReferenceCode that are generated are based on the data in the database. If you are ...
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 ...
Copy Calendar Entry Dates into Custom Field Values
Some systems let you create custom fields that reference a Calendar Entry. When moving custom fields into a system that does not support this, an easy workaround would be to turn these custom fields into DateTime custom fields by copying in the start ...