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 that the new system CAN support.  However, they may also want to preserve the old matter numbers so they can search for matters using those numbers.

The script below will create a custom field named 'Legacy Matter Number' and populate it with the matter numbers from their source system.

INSERT INTO __M_CustomField_Definitions	(
    Id,
    Final_Kind,
    Final_Subject,
    Final_Parent_Type
) 
VALUES (
    'Legacy Matter Number',	
    'TextLine',
    'Legacy Matter Number',	
    '__M_Matters'	
)

INSERT INTO __M_CustomField_Values (
    Id,
    Final_Parent_Id,
    Final_Parent_Type,
    Final_CustomFieldDefinition_Id,
    Final_Value
)
SELECT	
    CONCAT('Legacy Matter Number --- ', Id),	
    Id,
    '__M_Matters',
    'Legacy Matter Number',
    Final_ReferenceCode	
FROM
    __M_Matters
WHERE 1=1
    AND LEN( Final_ReferenceCode ) > 0