/bundles/itninjaweb/img/Breadcrumb_cap_w.png
After the changes in 7.0 I cant seem to figure out how to get the department field to populate again on all tickets. Now that K1000 uses the asset field to pull that I can only get the Location ID not the full name of the location. This also breaks all Submitted Tickets Monthly by Department (See below). Any help would be much appreciated. 

Select SQL:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET 
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) 
WHERE HD_TICKET.SUBMITTER_ID <>0


UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE3 = LOCATION_ID WHERE T.ID=




Lists submitted tickets monthly by department.
Report:

SELECT HD_TICKET.CUSTOM_FIELD_VALUE3, HD_TICKET.ID, HD_TICKET.TITLE, date_format(HD_TICKET.CREATED, '%M') as MONTH, O.FULL_NAME AS OWNER_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED, HD_PRIORITY.NAME AS PRIORITY, HD_CATEGORY.NAME AS CATEGORY, HD_STATUS.NAME AS STATUS, HD_IMPACT.NAME AS IMPACT, S.FULL_NAME AS SUBMITTER_NAME  FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH))
AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)) ORDER BY CUSTOM_FIELD_VALUE3
0 Comments   [ + ] Show comments

Comments


Community Chosen Answer


Answers

1
So your UPDATE needs to be changed to reference the new location of the user's location: it's not in the "USER" table anymore, but in "USER_FIELD_VALUE"
UPDATE HD_TICKET 
JOIN USER_FIELD_VALUE on (HD_TICKET.SUBMITTER_ID = USER_FIELD_VALUE.USER_ID)
SET HD_TICKET.CUSTOM_FIELD_VALUE3 = USER_FIELD_VALUE.FIELD_VALUE
WHERE USER_FIELD_VALUE.FIELD_ID = [THE ID OF THE LOCATION FIELD]
  AND HD_TICKET.ID = <TICKET_IDS>
You can find USER_FIELD_VALUE.FIELD_ID in the table "USER_FIELD_DEFINITION"
Answered 04/11/2017 by: JasonEgg
Red Belt

  • This seems to only populate the ID of the location rather than the location itself. I am currently using the location IDs using the following.

    UPDATE HD_TICKET T
    JOIN USER S ON (S.ID = T.SUBMITTER_ID)
    SET T.CUSTOM_FIELD_VALUE3 = LOCATION_ID WHERE T.ID=<TICKET_IDS>

    I would like it to populate the location like it did before such as Finance, Payroll, ect.

    Thanks for the help.
    • Are you using linked assets for location?

Answers


  • Yes, thank you for the comment. I can retrieve the department ID but not the department name. I am trying to modify the existing SQL query to get the same results as before but so far no luck.