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;
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.
I was missing a Select statement in the COUNT. The Rule is now set to update on save with an update statement listed below.
As ably pointed out by ChuckSteel, I was missing a select in my count.
So the Update script becomes;
SET HD_TICKET.TITLE = CONCAT('CR', ' ', (SELECT CAST(COUNT(*) + 1 AS CHAR)
WHERE HD_TICKET.HD_QUEUE_ID in (queueidnumber)
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!
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?
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 2 years ago
WHERE HD_TICKET.HD_QUEUE_ID in (queueIDnumber)
and HD_TICKET.TITLE not like "CR%"
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 2 years ago
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 2 years ago