K1000 7.0 Upgrade SQL Populate Department Field No Longer Works
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
Answers (2)
Please log in to answer
Posted by:
JasonEgg
7 years ago
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"
Comments:
-
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. - agahlbeck 7 years ago-
Are you using linked assets for location? - JasonEgg 7 years ago
Posted by:
chucksteel
7 years ago
Did you see these posts:
Comments:
-
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. - agahlbeck 7 years ago
