SQL Spreadsheets: DocType Mappings

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.

  1. SELECT
    V1.Id, 
    V1.Final_ReferenceCode as 'Old Code',
    V1.Final_Subject as 'Old Name',
	V1.Final_Status as 'Status',
	'' as 'New Reference Code - Fill In For ALL',
    '' as 'New Name - Fill In For ALL',
    Script = CONCAT(''
 --IF cell A### is not blank
      , '=IF(INDIRECT("F"&ROW()) <> "",'
      --THEN: Generate the script using the ID in cell B###.
      , '"'
	  , ' UPDATE'
	  , '    __M_Documents_Digital_Categories'
	  , ' SET'
	  , '  Final_ReferenceCode = '
	  , '     ''" & '
	  , '       SUBSTITUTE(INDIRECT("E"&ROW()), "''", "''''") '
	  , '     & "''',
	  , '  Final_Subject = '
	  , '     ''" & '
	  , '       SUBSTITUTE(INDIRECT("F"&ROW()), "''", "''''") '
	  , '     & "'''
	  , ' WHERE 1=1'
	  , '   AND Id = '
	  , '     ''" & '
	  , '       INDIRECT("A"&ROW()) '
	  , '     & "'''
	  , '"'
	  --/THEN
      , ', '
	  --ELSE
	  , '"'
	  , ' '
	  , '"'
	  --/ELSE
      , ')'
	)
FROM
    __M_Documents_Digital_Categories V1
ORDER BY
    Final_Subject

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.

A value should be provided in Colum E and Colum F which correlate to the document type's Code and Name.
In order to merge records together, the value you provide MUST match your legacy system exactly.  If there is no match, they will be created.

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

---


    • Related Articles

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

    • 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: Cherry-Picking Custom Fields

      The following SQL command will generate a Google Sheet that users can use to cherry-pick custom field definitions. SELECT '' as 'Keep? (Y)', V1.Id, V1.Final_Parent_Type, V1.Final_Kind, V1.Final_Subject, V1.Final_Description, V1.Final_Status, ...