3 Replies Latest reply on Mar 12, 2015 12:43 PM by dtawater

    How to reseed the starting ticket number

    dtawater

      My initial idea was to insert a ticket manually with the number just below what i want my seed to be.  The reasoning behind this was to have the old ticketing system run in tandem with the new ticketing system for a period of time, yet, also ensuring that no ticket number collides between the two systems during the cutover migration phase...  Any suggestions?

        • Re: How to reseed the starting ticket number
          apap

          We did this after our initial test prior to going live.   I had the below instructions copied from another post I found somewhere, not my own work -

           

           

          How to Delete All Tickets from Database to Reset Ticket Numbering to 1

          Resetting ticket numbering in Web Help Desk (WHD), such as when you need to reset ticket numbering to start at 1, may require ticket deletion (ticket deletion is required only if you want to move the ticket number backward; if you want to increase the ticket number, you don't need to delete tickets).

          Important: Always make sure you  perform a full database backup before deleting anything from the database!

          See Database Migration Options for information on backing up your WHD database. For PostgreSQL backup details, see Chapter 24. Backup and Restore in the PostgreSQL 9.2.6 Documentation. For SQL Server backup details, see Back Up and Restore of SQL Server Databases. For MySQL backup details, see Chapter 1. Backup and Recovery from MySQL Backup and Recovery documentation.

          After backing up your database, perform the following three basic steps:

          1. Delete all rows from database tables.
          2. Delete all tickets from database.
          3. Reset ticket numbering.

          The following subsections describe how to perform each step.

          1. Delete All Rows from Database Tables

          To delete all rows from a table, use the following SQL syntax:

          DELETE from [table name];

          WARNING: This destructive action cannot be undone! Make sure you have performed a full backup before deleting anything from the database.

          The tables from which to delete rows *before* removing tickets are:
          TKT_APP_STEP_CLIENT_VOTE
          TICKET_APPROVAL_STEP
          TICKET_APPROVAL
          TICKET_ATTACHMENT
          TICKET_TAX_RATE
          TICKET_STATUS_TIME
          TICKET_CUSTOM_FIELD
          TICKET_BULK_ACTION
          EMAIL_DATA_OBJECT
          HISTORY_ENTRY
          TECH_NOTE
          CLIENT_NOTE
          JOB_TICKET_PART
          JOB_TICKET_ASSET
          LVL_ASSIGNMENT_HIST_ENTRY

          You must delete these database table rows *before* performing any further steps, so ignore the warning and remove all rows for each table.

          2. Delete All Tickets from Database

          Once you have cleared out the above tables, use the following SQL syntax for deleting tickets:

          DELETE from JOB_TICKET;

          3. Reset Ticket Numbering

          Finally, reset the ticket counter to the desired value, which is shown below as . The counter can begin at any number you choose, using values going forwards or backwards. Use one of the following methods to reset ticket numbering:

          FrontBase

           

          Issue the following SQL:
          Set UNIQUE=<starting_number> for JOB_TICKET;

           

          MySQL, SQL Server, and OpenBase

          Edit the EO_PK_TABLE and find the row with NAME='JOB_TICKET';
          Set the PK value for this row to <starting_number>.

          PostgreSQL

          Use the following SQL syntax:
          ALTER SEQUENCE job_ticket_seq RESTART WITH<starting_number>;
          Where is the ticket number starts from .

            • Re: How to reseed the starting ticket number
              rodegard

              If you have any completed surveys in the database you need to run this statement first before the steps listed above

               

               

              UPDATE SURVEY_RESPONSE SET TICKET_ID=null

               

              1 of 1 people found this helpful
                • Re: How to reseed the starting ticket number
                  dtawater

                  Thank you rodegard, apap this helped me to correctly find where the PK fields are located and now i now how the desk reseeding system works.  I didn't delete any tickets, i just ran this query.

                   

                  UPDATE dbo.EO_PK_TABLE SET PK = 100000

                   

                  Since i was only looking to push the first ticket number out 100 thousand so i can properly import all of my active tickets from the legacy system, this solution is very much exactly what i needed.  Thank you again.