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.


    • Related Articles

    • SQL Spreadsheets: Cherry-Picking Records

      As a general rule, we recommend that SQL scripts be used to mass-select/prune records; however, sometimes that is not possible. In these cases, we recommend having a member of the law firm complete a Google spreadsheet that clearly indicates what ...

    • SQL Spreadsheets: Cherry Picking Contacts

      The following SQL command will generate a spreadsheet that can be used to cherry-pick specific contacts. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...

    • SQL Spreadsheets: Cherry Picking Matters

      The following SQL command will generate a spreadsheet that can be used to cherry-pick specific matters. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...

    • Flatten Custom Object fields into Contact/Matter Fields

      WARNING: Many systems that support custom objects allow multiple rows in custom objects. Many systems that allow custom fields only allow one single value for each field. When you use this script, if a matter has two values set for the same custom ...

    • Custom Fields: Creating Custom Fields via Queries

      Sometimes you may need to create a new custom field to handle an unmapped column in a database. The queries below will help you do that. -- This is the ID we are giving our custom field definition. DECLARE @FieldId NVARCHAR(MAX) = 'ConNo --- CUSTOM' ...