Convert Any Field into a Picklist

Convert Any Field into a Picklist

The following script lets you convert any field into a picklist.


  2. --This script will convert any field (usually text fields) into Picklists
  3. --and generate Picklist Options from the values.
  4. GO


  7. DECLARE @Ids Table(Id NVARCHAR(MAX))
  8. INSERT INTO
  9. @Ids
  10. VALUES
  11. --Enter all the IDs of Custom Field Definitions we want to Convert
  12. (''),
  13. (''),
  14. ('')

  16. --Look at the Options table and the Values table and generate any items that dont already exist
  17. INSERT INTO
  18. __M_CustomField_Definitions_Options
  19. (
  20. Id, Final_Subject
  21. )
  22. SELECT DISTINCT
  23. CONCAT(V2.Id, ' --- ', V1.Final_Value),
  24. V1.Final_Value
  25. FROM
  26. __M_CustomField_Values V1,
  27. __M_CustomField_Definitions V2
  29. WHERE 1=1
  30. AND V2.Id = V1.Final_CustomFieldDefinition_Id
  31. AND V2.Id IN (
  32. SELECT Id from @Ids
  33. )
  34. AND CONCAT(V2.Id, ' --- ', V1.Final_Value) NOT IN (
  35. SELECT
  36. Id
  37. FROM
  38. __M_CustomField_Definitions_Options
  39. )

  41. --If we want to convert a field into a Picklist, Run the below script.
  42. UPDATE
  43. __M_CustomField_Definitions 
  44. SET
  45. Final_Kind = 'PickList'
  46. WHERE 1=1
  47. AND Id in (
  48. SELECT Id from @Ids
  49. )

  51. --Then update the Options table to reference the new items.
  52. UPDATE
  53. __M_CustomField_Values
  54. SET
  55. Final_Value = CONCAT(V2.Id, ' --- ', V1.Final_Value)
  56. FROM
  57. __M_CustomField_Values V1,
  58. __M_CustomField_Definitions V2
  59. WHERE 1=1
  60. AND V2.Id = V1.Final_CustomFieldDefinition_Id
  61. AND Final_Value NOT IN(
  62. SELECT
  63. Id
  64. FROM
  65. __M_CustomField_Definitions_Options
  66. )
  67. AND V2.Id In (
  68. SELECT Id from @Ids
  69. )


  72. GO


    • Related Articles

    • Convert a Custom Field into a Practice Area

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

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