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