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!
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

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>
Answered 02/08/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
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
Answered 02/21/2012 by: natearms
Senior Yellow Belt

  • I'm interested in this also, but I'm curious as to what you had to modify. Thank you
    • 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.
Please log in to comment
Answer this question or Comment on this question for clarity

Share