SQL Spreadsheets: Cherry-Picking Custom Fields

SQL Spreadsheets: Cherry-Picking Custom Fields

The following SQL command will generate a Google Sheet that users can use to cherry-pick custom field definitions.

  1.   SELECT
  2.     '' as 'Keep? (Y)',
  3.     V1.Id, 
  4.     V1.Final_Parent_Type,
  5.     V1.Final_Kind, 
  6.     V1.Final_Subject, 
  7.     V1.Final_Description,    V1.Final_Status,
  8. 	COALESCE(V2.Count, 0) as 'TimesUsed',

  10.     Script = CONCAT(''
  11.       --IF cell A### = "Y"
  12.       , '=IF(INDIRECT("A"&ROW()) = "Y",'
  13.       --THEN: Generate the script using the ID in cell B###.
  14.       , '"'
  15. 	  , ' UPDATE'
  16. 	  , '   __M_CustomField_Definitions'
  17. 	  , ' SET'
  18. 	  , '   Final_Status = ''Active'', '
  19. 	  , '   Final_Subject = '
  20. 	  , '     ''" & '
  21. 	  , '       SUBSTITUTE(INDIRECT("E"&ROW()), "''", "''''") '
  22. 	  , '     & "'''
  23. 	  , ' WHERE 1=1'
  24. 	  , '   AND Id = '
  25. 	  , '     ''" & '
  26. 	  , '       INDIRECT("B"&ROW()) '
  27. 	  , '     & "'''
  28. 	  , '"'
  29. 	  --/THEN
  30.       , ', '
  31. 	  --ELSE
  32. 	  , '"'
  33. 	  , ' '
  34. 	  , '"'
  35. 	  --/ELSE
  36.       , ')'
  37. 	  )
  38. FROM
  39.     __M_CustomField_Definitions V1
  40. LEFT JOIN
  41. 	(
  42. 		SELECT
  43. 			Final_CustomFieldDefinition_Id as 'Id',
  44. 			Count(*) as 'Count'
  45. 		FROM
  46. 			__M_CustomField_Values
  47. 		GROUP BY
  48. 			Final_CustomFieldDefinition_Id
  49. 	) AS V2
  50. ON V1.Id = V2.Id
  51. ORDER BY
  52.     Final_Parent_Type, Final_Subject
  53.

 
Once you generate the Google Sheet, you should share it with your customer with instructions similar to the following:
Replace references to the "legacy environment" and "new system" to be the actual names of the systems.

---

Hi Customer,

In prep for restoring your data to the [new system], I need you to fill out the Google Sheet included in this email.

The spreadsheet lists all custom field definitions your firm has defined and how many times each one is used.

For each field that you would like to keep, place a Y in column A.

When choosing each field, you should only select the fields that are important and frequently used.

For example, if you have a field that you feel is important but is seldom used, use your own judgement, but we generally recommend not cluttering your new system with it.


If you have any questions, please feel free to reach out.