Is it possible to print the FULL_NAME from a Custom 'User' type field selection?
We have a queue that we use for onboarding new employees. In this queue there is a custom 'User' type field, from which the submitter can select an existing user whose access the new employee's should mirror. We use a rule to send emails to distribution lists so that the appropriate new accounts can be created. I would like to have this email include the selection from the custom 'User', so recipients know whose access they should mirror.
Using the following SQL returns the user's ID, but I need the FULL_NAME. However, I'm not sure how to define and call this information. Any assistance would be greatly appreciated!
HD_TICKET.CUSTOM_FIELD_VALUE7 as VALUE7,
If you selected CUSTOM_7 in Service Desk › Configuration › Queue Customization | "Your Service Desk Queue name".
The actual data for that field is stored in HD_TICKET.CUSTOM_FIELD_VALUE6 as this starts at Zero.
In the SQL statement "as CUSTOM_7_USER_NAME" is what the column name will be in the report for this custom field, you can rename this title.
Here is some SQL that I believe based on your description, should get you started and can be modified to meet your needs.
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
(select FULL_NAME from USER where HD_TICKET.CUSTOM_FIELD_VALUE6 = USER.ID) as CUSTOM_7_USER_NAME
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE != 'closed'
order by OWNER_NAME