/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: JasonEgg 6 years ago
Red Belt
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"

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 6 years ago
    • Are you using linked assets for location? - JasonEgg 6 years ago
Posted by: chucksteel 6 years ago
Red Belt
0

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 6 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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