Custom rule help, move from one queue to another
Hi there.
I've got staff submitting things to general queue 16 right now and every time we want something to go into the IT queue we have to manually move it. It's silly. Hoping someone can provide some guidance on how to fix this query so that it works. Basically as long as it doesn't include the word "Facilities" in the title, I want to move it to queue 10. Here is what I have so far (lifted from another question here):
Select Query:
SELECT HD_TICKET.ID, HD_TICKET.TITLE,
HD_TICKET.HD_CATEGORY_ID, HD_TICKET.HD_QUEUE_ID,
HD_CATEGORY.ID, HD_CATEGORY.NAME
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE HD_TICKET.TITLE rlike 'Test'
AND HD_TICKET.HD_QUEUE_ID = 16
ORDER BY HD_TICKET.ID
Update Query:
UPDATE HD_TICKET, HD_CATEGORY
SET HD_TICKET.HD_QUEUE_ID = 10
WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
AND HD_TICKET.ID = <TICKET_IDS>
I'm testing with anything in ticket title that says "Test". I'd love to keep the category from one queue to another if possible. Here is the fun error I get back:
29/07/2019 10:59:43> Starting: 29/07/2019 10:59:43 29/07/2019 10:59:43> Executing Select Query... 29/07/2019 10:59:43> selected 8 rows 29/07/2019 10:59:43> Executing Update Query... 29/07/2019 10:59:43> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '384,387,324,324,387,395,389' at line 4] in EXECUTE("UPDATE HD_TICKET SET HD_TICKET.HD_QUEUE_ID = 10 WHERE HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID AND HD_TICKET.ID = 327,384,387,324,324,387,395,389") 29/07/2019 10:59:43> Ending: 29/07/2019 10:59:43
Any insight appreciated :) I'm brutal with this stuff. Thx.
Answers (1)
I think you don't need the first part of the where statement in the update statement, if you have defined in the select well enough so only ticket ID's are selected when you have the word test, then all you need to do is to set the QUEUE_ID for those tickets.
That being said you still have a syntax error in the update statement, maybe try this (untested sorry)
UPDATE HD_TICKET
SET HD_TICKET.HD_QUEUE_ID = 10
WHERE HD_TICKET.ID in (<TICKET_IDS>)
Comments:
-
Thx Hobbsy. I will try it. I've changed the select statements to include only new tickets but I'm afraid it's made things more complicated. I thought if I created a script via the wizard and then only select the bits I needed it would work, but no :(
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
from (HD_TICKET, HD_STATUS)
where ((( HD_STATUS.NAME = 'New') AND HD_TICKET.TITLE not like '%Facilities%') and HD_TICKET.HD_QUEUE_ID = 16 )
UPDATE HD_TICKET
SET HD_TICKET.HD_QUEUE_ID = 10
WHERE HD_TICKET.ID in (<TICKET_IDS>)
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from (HD_TICKET, HD_STATUS) where ((( HD_STATUS.NAME = 'New') AND HD_TICKET.TI' at line 5] in EXECUTE("select HD_TICKET.*, HD_STATUS.NAME AS STATUS_NAME, from (HD_TICKET, HD_STATUS) where ((( HD_STATUS.NAME = 'New') AND HD_TICKET.TITLE not like '%Facilities%') and HD_TICKET.HD_QUEUE_ID = 16 ) and (HD_TICKET.ID = 27396) ") - gfacchini 5 years ago-
Try it without the comma at the end of the first line. Also you are not joining the status table in any way to get the Status name, which you do not need to do, if you know the id of the status that is "new" replace the x with the id
select HD_TICKET.*
from HD_TICKET
where ((( HD_TICKET.STATUS_ID = 'x') AND HD_TICKET.TITLE not like '%Facilities%') and HD_TICKET.HD_QUEUE_ID = 16 ) - Hobbsy 5 years ago-
Edit - here is the code that works in case anyone wants to use it. Status 75 and 48 are "New" in both queues.
SELECT STATEMENT:
select HD_TICKET.*
from HD_TICKET
where ((( HD_TICKET.HD_STATUS_ID = '75') AND HD_TICKET.TITLE not like '%Facilities%') and HD_TICKET.HD_QUEUE_ID = 16 )
UPDATE STATEMENT:
update HD_TICKET
set HD_TICKET.HD_QUEUE_ID = 10,
HD_TICKET.HD_STATUS_ID = '48'
where HD_TICKET.ID in (<TICKET_IDS>) - gfacchini 5 years ago
In the table HD_STATUS you will find all your status such as "New" for your queue, alongside that will be the ID. The ID is the data that is stored in the HD_TICKET.HD_STATUS_ID field.
So for example if your ID for "New" in queue 16 is "7" then you would use
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
from (HD_TICKET, HD_STATUS)
where ((( HD_TICKET.HD_STATUS_ID = 7) AND HD_TICKET.TITLE not like '%Facilities%') and HD_TICKET.HD_QUEUE_ID = 16 )
or similar will then work as your select statement and
UPDATE HD_TICKET
SET HD_TICKET.HD_QUEUE_ID = 10
WHERE HD_TICKET.ID in (<TICKET_IDS>)
can be used as your update statement. If this does not work or you have further issues, please get in touch and let's save the other ITNinja's all this spam ;o) - Hobbsy 5 years ago