/build/static/layout/Breadcrumb_cap_w.png

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!

0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: GillySpy 12 years ago
7th Degree Black Belt
0
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:
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=0


the 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
Senior Yellow Belt
0
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

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.
 
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