/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000 - Autopopulate Department Field on Ticket Save

07/03/2017 883 views
I seem to be having trouble finding where the SQL field for department is. If I could find out the name of that (error is on the update query S.CUSTOM_VALUE1) I think the selection and update queries are correct.

I saw the trick where you can get the wizard to tell you names of fields but I cant get it to give me the SQL column names.

Selection:
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:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE0 = S.CUSTOM_VALUE1 WHERE T.ID=<TICKET_IDS>

Error:
07/03/2017 16:42:44> Starting: 07/03/2017 16:42:44 07/03/2017 16:42:44> Executing Select Query... 07/03/2017 16:42:44> selected 1 rows 07/03/2017 16:42:44> Executing Update Query... 07/03/2017 16:42:44> mysqli error: [1054: Unknown column 'S.CUSTOM_FIELD_VALUE1' in 'field list'] in EXECUTE("UPDATE HD_TICKET T JOIN USER S ON (S.ID = T.SUBMITTER_ID) SET T.CUSTOM_FIELD_VALUE0 = S.CUSTOM_FIELD_VALUE1 WHERE T.ID=4968") 07/03/2017 16:42:44> Ending: 07/03/2017 16:42:44
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
The custom fields for users are no longer in the USER table. As of version 7 they are now in USER_FIELD_VALUE. This update statement should work for you:
update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE0 = (select FIELD_VALUE FROM USER_FIELD_VALUE  where USER_ID = SUBMITTER_ID and FIELD_ID = 1) where 
        T.ID = <TICKET_IDS>;

Answered 07/05/2017 by: chucksteel
Red Belt

  • Thanks!
    It seems to be updating the field in the logs. However, when I go to look, it does not seem like it is setting a value to the department field.

    07/05/2017 12:57:01> Starting: 07/05/2017 12:57:01 07/05/2017 12:57:01> Executing Select Query... 07/05/2017 12:57:01> selected 1 rows 07/05/2017 12:57:01> Sending email... 07/05/2017 12:57:01> sent mail to myemail@mydomain 07/05/2017 12:57:01> Executing Update Query... 07/05/2017 12:57:01> updated 1 rows 07/05/2017 12:57:02> Ending: 07/05/2017 12:57:02
    • Have you verified in the database that department is stored in custom field 1?
      • Under the layout ticket fields in queue customization, it is custom_1. I could be connecting two things that are not connected but I was pretty sure that was a way to verify.
      • So that's the field in the ticket layout, what about for the user?
      • How would I go about verifying it for the user?

        I used the wizard and only selected the department field, giving me HD_TICKET.CUSTOM_FIELD_VALUE0.

        I'm not sure how to get the name for the other side of it.
      • Open the details for a user, click the link at the top of the page for Customize Additional Fields, this will show the custom user fields. The data that gets populated is setup in the LDAP User Import (Settings, User Authentication, import schedule (the bell icon)).
      • Ah there is the issue....we didnt import the department into a custom field.

        Could a sloppy workaround be setting it to the value of a label? We have everyone labeled with their department name.
      • Yes, that would be possible, although it would require that all of the department labels are in a label group or have a naming scheme such as "Department - whatever" so that the rule can know which label to use (as it is possible/likely that some users will have more than one label).
 
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