Auto Assigning Managers as Approvers
So before I waste any more time trying to figure this out on my own, I figured I would ask.
What I'm trying to do is assign the submitter's manager as an approver to the ticket. I've reserved a custom slot on the user import that will pull in the users manager from active directory, unfortunately it pulls in the FQDN of the manager, but this isn't a big issue since SUBSTRING(SUBSTRING_INDEX(USER.CUSTOM_2, ',', 1)FROM 4) will get rid of it and leave just the managers name left. Now that I've got the managers name, I would like to match that name with the USER.FULL_NAME column and then return their ID, which will then be applied to the ticket.
Is this even possible to do?
Thanks!
What I'm trying to do is assign the submitter's manager as an approver to the ticket. I've reserved a custom slot on the user import that will pull in the users manager from active directory, unfortunately it pulls in the FQDN of the manager, but this isn't a big issue since SUBSTRING(SUBSTRING_INDEX(USER.CUSTOM_2, ',', 1)FROM 4) will get rid of it and leave just the managers name left. Now that I've got the managers name, I would like to match that name with the USER.FULL_NAME column and then return their ID, which will then be applied to the ticket.
Is this even possible to do?
Thanks!
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
GillySpy
12 years ago
The ticket rule would fire on ticket save. If this is the only condition then the query would look to see if a manager exists and optionally if the ticket already has an approver. what do you want to do if the owner is removed and no owner is re-added (ie the ticket becomes
unassigned)?
untested
select query:
the update
unassigned)?
untested
select query:
select DISTINCT HD_TICKET.ID from HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN USER OWNER ON OWNER.ID=OWNER_ID
JOIN USER MANAGER ON MANAGER.FULL_NAME= SUBSTRING(SUBSTRING_INDEX(OWNER.CUSTOM_2, ',', 1)FROM 4)
WHERE
APPROVER_ID=0the update
UPDATE HD_TICKET T
JOIN USER OWNER ON OWNER.ID=OWNER_ID
JOIN USER MANAGER ON MANAGER.FULL_NAME= SUBSTRING(SUBSTRING_INDEX(USER.CUSTOM_2, ',', 1)FROM 4)
set APPROVER_ID=MANAGER.ID
WHERE
APPROVER_ID=0
and T.ID =<TICKET_IDS>
Posted by:
natearms
12 years ago
Thank you so much for this Gerald, I had to modify some things, but this has helped me out tremendously.
It's not necessarily having an owner assigned to a ticket, it's having the submitter's manager set as the approver on a ticket, so really... the owner isn't necessary
It's not necessarily having an owner assigned to a ticket, it's having the submitter's manager set as the approver on a ticket, so really... the owner isn't necessary
Comments:
-
I'm interested in this also, but I'm curious as to what you had to modify. Thank you - andibogard 12 years ago
-
This is my functioning code for auto-assigning managers as approvers on tickets
Select Query:
select
HD_TICKET.ID,
S.NAME AS STATUS
from HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID
JOIN USER SUBMITTER ON SUBMITTER.ID=SUBMITTER_ID
JOIN USER MANAGER ON MANAGER.FULL_NAME= SUBSTRING(SUBSTRING_INDEX(SUBMITTER.CUSTOM_2, ',', 1)FROM 4)
JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
WHERE C.DESCRIPTION Like 'Ticket Created%'
AND APPROVER_ID = 0
AND C.MAILED = 0
AND HD_TICKET.HD_QUEUE_ID = 1
AND S.NAME != 'Closed'
OR S.NAME !='Widthdrawn by user'
Update query:
UPDATE HD_TICKET
JOIN USER SUBMITTER ON SUBMITTER.ID=SUBMITTER_ID
JOIN USER MANAGER ON MANAGER.FULL_NAME= SUBSTRING(SUBSTRING_INDEX(SUBMITTER.CUSTOM_2, ',', 1)FROM 4)
set APPROVER_ID=MANAGER.ID
WHERE APPROVER_ID=0
and (HD_TICKET.ID in ())
You will need to have the Custom2 field under users import the managers active directory distinguished name (manager field in AD). You'll also have to modify the queue ID, and change the "S.NAME" to whatever your closure fields are. - natearms 12 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.