/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Department not auto updating since upgrade to version 7.0.121306

03/23/2017 1342 views
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

Comments

  • You're running this on ticket save?
  • are you receiving an error or is the query returning unexpected results?
  • Yes I am.
    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
  • Thanks for your response. It's showing "Unknown column 'S.CUSTOM_1' in 'field list".

    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.
  • JasonEgg 19 hours ago Flag
    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?

All Answers

0
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.
Answered 03/23/2017 by: JasonEgg
Red Belt

  • Ok thank you for letting me know! I will give it a try.
    • 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
      • 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.

Don't be a Stranger!

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

Sign up! or login

View more:

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