Create an "Employer" Contact from a Custom Field.

--Create Company Contacts
--out of all the distinct company names
--that are in the 'Employer Name' custom field.
INSERT INTO __M_Contacts(
    Id,
    Final_Kind,
    Final_FullName
)
SELECT DISTINCT
    V2.Final_Value, 
    'Company',
    V2.Final_Value
FROM
    __M_CustomField_Definitions V1,
    __M_CustomField_Values V2,
    __M_Contacts V3
WHERE 1=1
    AND V1.Final_Subject = 'Employer Name'
    AND V1.Id = V2.Final_CustomFieldDefinition_Id
    AND V2.Final_Parent_Type = '__M_Contacts'
    AND V2.Final_Parent_Id = V3.Id

--Create Employer <=> Employee relationships
--For all the values above
INSERT INTO __M_Contacts_Related_Contacts(
    Id,
    Final_ParentContact_Id, 
    Final_ParentContact_Kind, 
    Final_ChildContact_Id, 
    Final_ChildContact_Kind
)
SELECT DISTINCT 
    V2.Id,
    V2.Final_Value,
    'Employer',
    V2.Final_Parent_Id,
    'Employee'
FROM
    __M_CustomField_Definitions V1,
    __M_CustomField_Values V2,
    __M_Contacts V3
WHERE 1=1
    AND V1.Final_Subject = 'Employer Name'
    AND V1.Id = V2.Final_CustomFieldDefinition_Id
    AND V2.Final_Parent_Type = '__M_Contacts'
    AND V2.Final_Parent_Id = V3.Id  

• Related Articles

• Create a "Legacy Matter Number" Custom Field

  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 ...

• Custom Fields: Creating Custom Fields via Queries

  Sometimes you may need to create a new custom field to handle an unmapped column in a database. The queries below will help you do that. -- This is the ID we are giving our custom field definition. DECLARE @FieldId NVARCHAR(MAX) = 'ConNo --- CUSTOM' ...

• Copy a Custom Field into a Core Field

  Some systems don't have certain core fields and clients often create a custom field that they use instead. The following script will let you copy values from a custom field named 'Opened' into __M_Matters.Final_Date_Opened . You can adjust this ...

• 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 ...

• 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 ...