Department not auto updating since upgrade to version 7.0.121306
Hello,
I'm having trouble with my custom ticket rule. Ever since we upgraded to server 7.0.121306 it does not auto populate. I did get the correct user field info from another post. This is what I have now: NOTE: I can see the custom department lists after inputting this...
query: SELECT DISTINCT (USER_FIELD_VALUE.FIELD_VALUE) FROM USER join USER_FIELD_VALUE on USER.ID = USER_FIELD_VALUE.USER_ID and USER_FIELD_VALUE.FIELD_ID = 1
So I'm assuming I need to change the ticket rule as well but need assistance with this.
This is what I have currently:
SELECT HD_TICKET.ID AS ID
FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1 AND HD_TICKET.SUBMITTER_ID <> 0)
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE4 = S.CUSTOM_1 WHERE
T.ID=<TICKET_IDS>
Thank you for your time.
5 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
JasonEgg
7 years ago
This is because user custom fields have been divided into different tables for v7. The info is now located in USER_FIELD_DEFINITION and USER_FIELD_VALUE. So you will want to grab the info from USER_FIELD_VALUE to update HD_TICKET.
Comments:
-
Ok thank you for letting me know! I will give it a try. - jfinley 7 years ago
-
I have this same problem, can someone clarify this. Thank you
The rule I have below if no longer working.
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 = S.LOCATION WHERE T.ID=<TICKET_IDS>
04/03/2017 13:28:54> Starting: 04/03/2017 13:28:54 04/03/2017 13:28:54> Executing Select Query... 04/03/2017 13:28:54> selected 1 rows 04/03/2017 13:28:54> Executing Update Query... 04/03/2017 13:28:54> mysqli error: [1054: Unknown column 'S.LOCATION' in 'field list'] in EXECUTE("UPDATE HD_TICKET T JOIN USER S ON (S.ID = T.SUBMITTER_ID) SET T.CUSTOM_FIELD_VALUE3 = S.LOCATION WHERE T.ID=6363") 04/03/2017 13:28:54> Ending: 04/03/2017 13:28:54 - agahlbeck 7 years ago-
So USER_FIELD_DEFINITION should have an entry for "LOCATION" with an ID# and USER_FIELD_VALUE contains the value for location, using the user ID#, location ID# (from above) and the value. This answer assumes you're using a user field for location rather than a linked asset. - JasonEgg 7 years ago
I see the SQL log has an unknown column error:
03/23/2017 16:27:17> Starting: 03/23/2017 16:27:17 03/23/2017 16:27:17> Executing Select Query... 03/23/2017 16:27:17> selected 1 rows 03/23/2017 16:27:17> Executing Update Query... 03/23/2017 16:27:17> mysqli error: [1054: Unknown column 'S.CUSTOM_1' in 'field list'] in EXECUTE("UPDATE HD_TICKET T JOIN USER S ON (S.ID = T.SUBMITTER_ID) SET T.CUSTOM_FIELD_VALUE4 = S.CUSTOM_1 WHERE T.ID=7584 ") 03/23/2017 16:27:17> Ending: 03/23/2017 16:27:17 - jfinley 7 years ago
My understanding is that Kace took away CUSTOM_1 column in the latest server update and switched it with something else but I have no clue what it is.
As far as the ticket is concerned. It doesn't update like it should. - jfinley 7 years ago
So USER_FIELD_DEFINITION should have an entry for "LOCATION" with an ID# and USER_FIELD_VALUE contains the value for location, using the user ID#, location ID# (from above) and the value. This answer assumes you're using a user field for location rather than a linked asset.
Where to the values get placed in the statement?
Do I still use the current setup but replace the location? - agahlbeck 7 years ago