Changing the Extended Id Template Text does not update existing records

Version 7

    Verified Product Versions

    Service Desk 2016.x

    New to 2016.3, we have the Extended ID functionality.  In previous versions of Service Desk, you could implement the same type of functionality using a calculation.  If you are using an older version, see the following document: How to write a calculation to pick up value from Child Object. / How to populate Process Ref

     

    The Extended ID feature prefixes your process records with an indicator for which process it belongs to.  For example, incident #347 would be stored as "I:347."  Task #3 on Change #238 would be "C:238 - T:3."  The Extended ID attribute is separate from the original ID attribute (located under the main Process object via Console, or the pm_id column in the pm_process table via SQL).  The Extended ID column is called "pm_extented_id and is also located in the pm_process table.

     

    Upon upgrade to 2016.3, all existing records acquire an Extended ID.  However, if you'd like to change the Extended ID Template Text in Object Designer, this will only start prefixing newly created records with your modified template text.  For example, lets say I wanted my incidents prefixed with "Inc:" as opposed to "I:"

     

    Displayed below is the properties of the Incident object.  As you can see, I've modified the template text to display "Inc:{Id}" as opposed to "I:{Id}"

    ExtendedID_Properties.png

     

    If you're an existing customer, I'm sure you already have Incidents in your database.  Upon saving the change above, all existing records will still be prefixed with the original "I:{Id}" template text.  Only records created after this change will populate the Extended ID attribute with your new template text.

     

     

    Using the script below, we can modify all existing records to also reflect your new template text.  Be sure to edit the @newtext and @oldtext variables to define what you are changing and what to change it to.

     

    DECLARE @newtext NVARCHAR(255) 
    DECLARE @oldtext NVARCHAR(255)
    
    
    SET @newtext = 'New Template Text'
    SET @oldtext = 'Old Template Text'
    
    
    UPDATE pm_process
    SET pm_extended_id = REPLACE(pm_extended_id, @oldtext + ':', @newtext + ':')
    WHERE pm_extended_id like @oldtext + ':' + '%'
    

     

     

    The example below displays how I would modify the script to update all existing incidents (records created before changing the template text in Object Designer!) to have my new prefix of "Inc:{Id}" instead of "I:{Id}"

    DECLARE @newtext NVARCHAR(255) 
    DECLARE @oldtext NVARCHAR(255)
    
    
    SET @newtext = 'Inc'
    SET @oldtext = 'I'
    
    
    UPDATE pm_process
    SET pm_extended_id = REPLACE(pm_extended_id, @oldtext + ':', @newtext + ':')
    WHERE pm_extended_id like @oldtext + ':' + '%'
    

    The same concept can be applied to update prefixes for any record type (i.e. Requests, Changes, Problems, etc...)

     

     

    This becomes slightly different when updating the task objects.  For example, task #2 on incident #47 would be "I:47 - T:2."  If we wanted to display the "T" for incident tasks as "IncTask," you could use the script below to do so.  As you can see, there is a third variable called @recordtype, which will be the prefix for the parent process of the task.

    You still need to update the template text property in Object Designer for the corresponding task object (i.e. the Task Incident object, which is located under the main Process object) to reflect what prefix you want new tasks generated with.  This script is strictly to update your existing task extended IDs to match whatever you updated it to.

    DECLARE @newtext NVARCHAR(255) 
    DECLARE @oldtext NVARCHAR(255)
    DECLARE @recordtype NVARCHAR(255)
    
    
    SET @newtext = 'New Template Text'
    SET @oldtext = 'Old Template Text'
    SET @recordtype = 'Parent Process Template Text'
    
    
    UPDATE pm_process
    SET pm_extended_id = REPLACE(pm_extended_id, @oldtext, @newtext)
    WHERE pm_extended_id LIKE @recordtype + ':' + '%'
    

     

     

    The example below displays how I would modify the script to update all existing incident tasks to have my new prefix of "I:{Incident/Id} - IncTask:{SerialNumber}:"

    DECLARE @newtext NVARCHAR(255) 
    DECLARE @oldtext NVARCHAR(255)
    DECLARE @recordtype NVARCHAR(255)
    
    
    SET @newtext = 'IncTask'
    SET @oldtext = 'T'
    SET @recordtype = 'I'
    
    
    UPDATE pm_process
    SET pm_extended_id = REPLACE(pm_extended_id, @oldtext, @newtext)
    WHERE pm_extended_id LIKE @recordtype + ':' + '%'