/build/static/layout/Breadcrumb_cap_w.png

Custom Rule to Sequentially Number Tickets outside of standard ID's

So, I think we have something for this, nearly, but I'd like to ask the Brain trust on here first as you may have a better way of doing it or I may be going about it all wrong.

We're creating a Change Request System on the service desk for business-wide changes, and as part of the scope we need the Tickets to be identified separately to the 'standard' service desk, i.e. not by TICK: identifier. We're using processes for the CR system, so the TICK: numbers would be different between parent and child tickets which would overly confuse matters

The easiest way I could think of to do this is to create a custom rule that appends CR:# at the start of the Title upon ticket creation , with # being a sequential value to give each CR a 'plain' numerical identifier. The child tickets will be inheriting the Parent ticket's title.

I'm thinking of (roughly) the following SQL to do this;

UPDATE HD_TICKET

SET TITLE = 'CR# ' + CONVERT(VARCHAR(10),(SELECT COUNT(*) + 1 FROM HD_TICKET WHERE HD_QUEUE_ID = CRQUEUEIDNUMBER AND IS_PARENT = True))+ ' ' + TITLE


Good idea? Bad Idea? Better way of doing this? I'm all ears.

Asked 4 years ago 228 views
3 Comments   [ + ] Show comments
  • Have you considered using a different queue for your CRs? This would allow you to see all your CR tickets separately. You could name the queue "CR" and then pre-pend the queue name in ticket emails and reports. Just an idea... - kenwhite 4 years ago
    • The CR's are indeed in a different queue. In emails and reports that would work, but not on the ticket itself; the users need a unique identifier for these which aren't part of the TICK:ID system., and I'd rather have the system create these itself than trust users to +1 on the previous CR#. - Honkytonk 4 years ago
  • Oh, now I see. I should have read your post more carefully. Hopefully, someone else will have a better idea if your SQL statement will work. Good luck! - kenwhite 4 years ago
  • Ok, so as an update to this after a couple of months of Covid madness and me finally being able to pick this up again; I've got the following;

    SELECT HD_TICKET.ID
    from HD_TICKET
    WHERE HD_TICKET.HD_QUEUE_ID in (queueIDnumber)
    AND HD_TICKET.IS_PARENT=TRUE
    and HD_TICKET.TITLE not like "CR%"

    UPDATE HD_TICKET
    SET HD_TICKET.TITLE = CONCAT('CR', ' ', CAST(COUNT(*) + 1 AS CHAR),' ', '-',' ', HD_TICKET.TITLE)
    WHERE HD_TICKET.ID in (<TICKET_IDS>)

    This does everything I want it to when run the select, but as soon as I put anything into the <TICKET_ID> section in the custom rule (even replacing the in() with = a single ID), it throws up a "1111; Invalid use of group function" error in the Last Run log.

    Am I being a MySQL muppet? I can't see any reason I'd have to use HAVING instead of WHERE here (which was my initial thought with the limited MySQL experience I have) as it's basically an identical scenario to the example listed in the ? on the page. - Honkytonk 4 years ago
    • You have a COUNT(*) outside of a select statement, so it doesn't know what you are counting. You probably want something like the sub-select statement from the original post. - chucksteel 4 years ago
      • You're right! Muppetry confirmed, thanks Chuck. :D

        This was indeed the problem, I have added in what amounts to a repeat of the select statement into the update statement as one lump to choose the tickets I want to count. I can still use the TICKET_IDS field to filter those tickets I want affected though. - Honkytonk 4 years ago

Answers (2)

Answer Summary:
I was missing a Select statement in the COUNT. The Rule is now set to update on save with an update statement listed below.
Posted by: Honkytonk 4 years ago
Senior Yellow Belt
0

Top Answer

As ably pointed out by ChuckSteel, I was missing a select in my count.

So the Update script becomes; 

UPDATE HD_TICKET

SET HD_TICKET.TITLE = CONCAT('CR', ' ', (SELECT CAST(COUNT(*) + 1 AS CHAR) 

FROM HD_TICKET 

WHERE HD_TICKET.HD_QUEUE_ID in (queueidnumber)

AND HD_TICKET.IS_PARENT=TRUE

AND HD_TICKET.TITLE like "CR%"

),' ', ':',' ',HD_TICKET.TITLE)

WHERE HD_TICKET.ID IN (<TICKET_IDS>)

When set to update on Save it now iterates through the queue, finds anything without "CR" in the title, and after counting those with "CR" in the title, appends the 'next' number to the title along with CR so you get;

"CR 8 : Ticket Title"

I can then hide the ticket ID for users in the Console, and as far as they're concerned the title's the only identifier. All the email gubbins still works as those are still sent out with the TICK:# format attached.

Hopefully that helps someone in a similar situation to me!

Posted by: Hobbsy 4 years ago
Red Belt
-1

Bad idea, the Hd_ticket table is using the ID number as the ticket ID, messing with data in this table is basically asking for trouble. If your only reason behind wanting to prefix the id with a CR as you find the TICK confusing, the TICK text enables the SMA to identify in long emails as emails that should be actioned. I am assuming that you will still need email functionality for your Change Queue?



Comments:
  • I don't want to touch the ID, that is I believe the PK for the table, and would, as you say, be an apocalyptically bad idea to mess with. I'm looking to append the CR:# to the Title field.

    So the email identifier if it's in "ticket_id - ticket_title" format would be unchanged, as it'd be "TICK:#-CR:# Title", and wouldn't mess with the email routing. - Honkytonk 4 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ