SQL Spreadsheets: User Mappings

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. 

  1. SELECT
  2.     V1.Id, 
  3.     V1.Final_FullName as 'Old User (FullName)',
  4.     V1.Final_FirstName as 'Old User (First)',
  5.     V1.Final_MiddleName as 'Old User (Middle)',
  6.     V1.Final_LastName as 'Old User (Last)',
  7.     V1.Final_Status as 'Old User (Status)',
  8.     V1.Final_Email  as 'Old User (Email)',
  9.     '' as 'New User (Email) - Fill In For EVERYONE',
  10.     Script = CONCAT(''
  11.  --IF cell A### is not blank
  12.       , '=IF(INDIRECT("H"&ROW()) <> "",'
  13.       --THEN: Generate the script using the ID in cell B###.
  14.       , '"'
  15. 	  , ' UPDATE'
  16. 	  , '    __M_Users'
  17. 	  , ' SET'
  18. 	  , '  Final_Email = '
  19. 	  , '     ''" & '
  20. 	  , '       SUBSTITUTE(INDIRECT("H"&ROW()), "''", "''''") '
  21. 	  , '     & "'''
  22. 	  , ' WHERE 1=1'
  23. 	  , '   AND Id = '
  24. 	  , '     ''" & '
  25. 	  , '       INDIRECT("A"&ROW()) '
  26. 	  , '     & "'''
  27. 	  , '"'
  28. 	  --/THEN
  29.       , ', '
  30. 	  --ELSE
  31. 	  , '"'
  32. 	  , ' '
  33. 	  , '"'
  34. 	  --/ELSE
  35.       , ')'
  36. 	)
  37. FROM
  38.     __M_Users V1
  39. ORDER BY
  40.     Final_Status,
  41.     Final_FullName,
  42.     Final_FirstName,
  43.     Final_LastName,
  44.     Final_Email

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.

An email address should be provided in Column H for each record listed.
The email address you provide MUST match the email address for a user listed in your [new APP] account.

Some of the users on the spreadsheet might be disabled or "machine" users.  For these users, you should provide an email address of someone in your firm who exists in your [new APP] account.  If something were mapped to one of these users, it will be linked to the new user post-migration.

When mapping former users, we generally recommend mapping to users of similar roles.

For example, if Jane Paralegal is a former employee, then Jill Paralegal might be a good substitute.

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

---


    • Related Articles

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