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?