Create an "Employer" Contact from a Custom Field.

Create an "Employer" Contact from a Custom Field.

In some systems, contacts do not have an Employer relation and only have a text field that represents the employer.  This script will help when moving firms into platforms that allow an actual contact to be specified for the employer. 
  1. --Create Company Contacts
  2. --out of all the distinct company names
  3. --that are in the 'Employer Name' custom field.
  4. INSERT INTO __M_Contacts(
  5.     Id,
  6.     Final_Kind,
  7.     Final_FullName
  8. )
SELECT DISTINCT
  9.     V2.Final_Value, 
  10.     'Company',
  11.     V2.Final_Value
  12. FROM
  13.     __M_CustomField_Definitions V1,
  14.     __M_CustomField_Values V2,
  15.     __M_Contacts V3
  16. WHERE 1=1
  17.     AND V1.Final_Subject = 'Employer Name'
  18.     AND V1.Id = V2.Final_CustomFieldDefinition_Id
  19.     AND V2.Final_Parent_Type = '__M_Contacts'
  20.     AND V2.Final_Parent_Id = V3.Id
  21. 
--Create Employer <=> Employee relationships
  22. --For all the values above
  23. INSERT INTO __M_Contacts_Related_Contacts(
  24.     Id,
  25.     Final_ParentContact_Id, 
  26.     Final_ParentContact_Kind, 
  27.     Final_ChildContact_Id, 
  28.     Final_ChildContact_Kind
  29. )
  30. SELECT DISTINCT 
  31.     V2.Id,
  32.     V2.Final_Value,
  33.     'Employer',
  34.     V2.Final_Parent_Id,
  35.     'Employee'
  36. FROM
  37.     __M_CustomField_Definitions V1,
  38.     __M_CustomField_Values V2,
  39.     __M_Contacts V3
  40. WHERE 1=1
  41.     AND V1.Final_Subject = 'Employer Name'
  42.     AND V1.Id = V2.Final_CustomFieldDefinition_Id
  43.     AND V2.Final_Parent_Type = '__M_Contacts'
  44.     AND V2.Final_Parent_Id = V3.Id