Some firms rely heavily on Client Numbers to search for a specific client.  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 client numbers so they can search for clients using those numbers.

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

INSERT INTO __M_CustomField_Definitions	(
    Id,
    Final_Kind,
    Final_Subject,
    Final_Parent_Type
) 
VALUES (
    'Legacy Client Number',	
    'TextLine',
    'Legacy Client Number',	
    '__M_Contacts'	
)

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