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

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

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