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

    • Custom Field Kinds

      The following is a list of the valid Kinds of custom fields. Kind Notes TextLine TextArea HtmlArea Number Represents any kind of number. Integer Represents only whole numbers Money Date Time TimeSpan Represents durations. DateTime EmailAddress ...

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