Other: Creating Unique Display Numbers

Other: Creating Unique Display Numbers

The following script will create unique Final_ReferenceCode for all Contacts.  It can be easily modified to apply to other record types.


Please note that the Final_ReferenceCode that are generated are based on the data in the database.

If you are doing a delta migration, you will likely want to run a script to copy display numbers from the alpha to the delta (much like Result_Ids are) otherwise records may have different display numbers between the alpha and delta.

  1. --This is the pattern you want DisplayNumbers to follow.
  2. DECLARE @FORMAT NVARCHAR(MAX) = 'C0000#'
  3. --This pattern means:
  4. -- * Use a 'C' followed by 5 numbers.
  5. -- * If we have a number that is less than 5 digits, prefix with 0.
  6.  
  7. --This is the lowest number we want our series to use
  8. DECLARE @SERIES_START INT = 1000
  9.  
  10. --ANYTHING BELOW THIS LINE WILL NOT GENERALLY NEED TO BE MODIFIED
  11.  
  12. /*
  13.             In the following code we will essentially work with three tables.
  14.             __M_Contacts is the table we are assigning display numbers to.
  15.             @DisplayNumbers is a [Row_Number(), DisplayNumber]
  16.             @Rows is a [Row_Number(), __M_Contacts.Row]
  17.  
  18.             The code works by doing the following:
  19.             1.0  Generate a list of display numbers (@DisplayNumbers)
  20.             1.1  Remove any from the list that are already in use
  21.             1.2  Assign a Row_Number() [Temp_Row] to anything in the list.  This will result in the Row_Numbers starting at 1 and there being no holes.
  22.            
  23.             2.0  Generate the list of rows we need to update (@Rows)
  24.             2.1  Get a list of all __M_Contacts that dont have a display number
  25.             2.2  Assign a Row_Number() [Temp_Row] to anything in the list.  This will result in the Row_Numbers starting at 1 and there being no holes.
  26.  
  27.             3.0  Because @Rows and @DisplayNumbers now both have a [Temp_Row] with no holes, we will match using that value
  28.  
  29.            
  30. */
  31.  
  32. --1.0  Generate a list of display numbers (@DisplayNumbers)
  33. --We will generate 2n display numbers because some might already be used.
  34. DECLARE @SERIES_COUNT INT = (SELECT 2 * COUNT(*) FROM __M_Contacts)
  35.  
  36. --Generate the list
  37. DECLARE @DisplayNumbers TABLE(Temp_Row INT INDEX IX1, New_DisplayNumber NVARCHAR(MAX))
  38. INSERT INTO @DisplayNumbers
  39. SELECT
  40.             0, FORMAT(V1.value, @FORMAT)
  41. FROM
  42.             generate_series(@SERIES_START, @SERIES_START + @SERIES_COUNT) V1
  43.  
  44. --1.1  Remove any from the list that are already in use
  45. DELETE FROM
  46.             @DisplayNumbers
  47. WHERE New_DisplayNumber IN (
  48.             SELECT
  49.                         Final_ReferenceCode
  50.             FROM
  51.                         __M_Contacts
  52. )
  53.  
  54. --1.2  Assign a Row_Number() [Temp_Row] to anything in the list.  This will result in the Row_Numbers starting at 1 and there being no holes.
  55. UPDATE
  56.             @DisplayNumbers
  57. SET
  58.             Temp_Row = V2.Temp_Row
  59. FROM
  60.             @DisplayNumbers V1,
  61.             (
  62.                         SELECT
  63.                                     ROW_NUMBER() over(order by New_DisplayNumber) as Temp_Row,
  64.                                     New_DisplayNumber
  65.                         FROM
  66.                                     @DisplayNumbers
  67.             ) V2
  68. WHERE 1=1
  69.             AND V1.New_DisplayNumber = V2.New_DisplayNumber
  70.  
  71.  
  72.  
  73. --2.0  Generate the list of rows we need to update (@Rows)
  74. --2.1  Get a list of all __M_Contacts that dont have a display number
  75. --2.2  Assign a Row_Number() [Temp_Row] to anything in the list.  This will result in the Row_Numbers starting at 1 and there being no holes.
  76. DECLARE @Rows TABLE(Temp_Row INT INDEX IX1, Row INT INDEX IX2)
  77. INSERT INTO @Rows
  78. SELECT
  79.             ROW_NUMBER() OVER(ORDER BY Final_Created_At, Id),
  80.             Row
  81. FROM
  82.             __M_Contacts
  83. WHERE 1=1
  84.              AND Final_ReferenceCode = ''
  85.  
  86.  
  87. --3.0  Because @Rows and @DisplayNumbers now both have a [Temp_Row] with no holes, we will match using that value
  88. UPDATE
  89.             __M_Contacts
  90. SET
  91.              Final_ReferenceCode = V3.New_DisplayNumber
  92. FROM
  93.             __M_Contacts V1,
  94.             @Rows V2,
  95.             @DisplayNumbers V3
  96. WHERE 1=1
  97.             AND V1.Row = V2.Row
  98.             And V2.Temp_Row = V3.Temp_Row


    • Related Articles

    • Matters: Trimming Client Numbers from Matter Numbers

      The following script will peel off client numbers from matter numbers. For example, if a matter has a ReferenceCode like: ABC-0001 After the script runs, it will have a reference code of: 0001 Scenario 1: There are Client Reference Codes In some ...

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

    • Create a "Legacy Matter Number" Custom Field

      Some firms rely heavily on Matter Numbers to search for a specific matter. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...

    • Detecting Duplicates by Reference Code

      In some scenarios, you need to ensure that a reference code is completely unique among a record type. To do this, use the following SQL script: --Look for duplicate records in the __M_Matters table SELECT * FROM --Change this to the table we want to ...

    • Create a "Legacy Originating Attorney" field (from __M_Matters_Participating_Entities)

      Most destination systems will only let you set the Originating Attorney and Responsible Attorney to an active user. This is why user mapping is used to map inactive users from the legacy system to an active user in the new system. But the firm may ...