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



    • Related Articles

    • Convert Any Field into a Picklist

      The following script lets you convert any field into a picklist. --This script will convert any field (usually text fields) into Picklists --and generate Picklist Options from the values. GO DECLARE @Ids Table(Id NVARCHAR(MAX)) INSERT INTO @Ids ...

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

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

    • Create a "Legacy Client Number" Custom Field

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

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