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

    • Create a "Legacy Client Number" Custom Field

      Some firms rely heavily on Client Numbers to search for a specific client. 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 ...