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 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, '') ...

    • SQL Spreadsheets: DocType Mappings

      The following SQL script will generate a Google Spreadsheet that will allow users to override document category names. This is extremely helpful when merging systems. SELECT V1.Id, V1.Final_ReferenceCode as 'Old Code', V1.Final_Subject as 'Old Name', ...

    • SQL Spreadsheets: User Mappings

      The following SQL script will generate a Google Spreadsheet that will allow users to override email addresses. This is extremely helpful when merging old user accounts together. SELECT V1.Id, V1.Final_FullName as 'Old User (FullName)', ...

    • SQL Spreadsheets: Reassigning Practice Areas

      Sometimes users want to reassign practice areas as part of the data restore into a new system. The following process makes it easy. Generate the SQL Spreadsheet Run the following command then copy the results and the headers into a Google Doc. ...