Convert a Custom Field into a Practice Area

Convert a Custom Field into a Practice Area

  

  1. --The system we are coming from does not have practice areas
  2. --however, the client has created a picklist custom field that mimics this.
  3. --We are going to convert this custom field into a practice area.

  5. --First, populate the practice areas table with our new values.
  6. INSERT INTO __M_Matters_PracticeAreas(
  7.     Id,
  8.     Final_Subject
  9. )
  10. SELECT
  11.     V2.Id,
  12.     V1.Final_Subject
  13. FROM
  14.     __M_CustomField_Definitions V1,
  15.     __M_CustomField_Definitions_Options V2
  16. WHERE 1=1
  17.     --Change this to be the name of the custom field we're converting into a practice area
  18.     AND V1.Final_Subject = 'Case Type'
  19.     AND V1.Final_Parent_Type = '__M_Matters'
  20.     AND V1.Final_Kind = 'PickList'
  21.     AND V2.Final_CustomFieldDefinition_Id = V1.Id

  23. --Now, update our matters to use these practice areas.
  24. UPDATE
  25.     __M_Matters
  26. SET
  27.     Final_PracticeArea_Id = V2.Id
  28. FROM
  29.     __M_CustomField_Definitions V1,
  30.     __M_CustomField_Definitions_Options V2,
  31.     __M_CustomField_Values V3,
  32.     __M_Matters V4
  33. WHERE 1=1
  34.     --Change this to be the name of the custom field we're converting into a practice area
  35.     AND V1.Final_Subject = 'Case Type'
  36.     AND V1.Final_Parent_Type = '__M_Matters'
  37.     AND V1.Final_Kind = 'PickList'
  38.     AND V2.Final_CustomFieldDefinition_Id = V1.Id
  39.     AND V2.Id = V3.Final_Value
  40.     AND V3.Final_CustomFieldDefinition_Id = V1.Id
  41.     AND V3.Final_Parent_Type = V1.Final_Parent_Type
  42.     AND V3.Final_Parent_Id = V4.Id