/build/static/layout/Breadcrumb_cap_w.png

How can I save a custom field from the user table in a ticket

We are pulling in the accounting code of the department the user is in from active directory into our users table.  I want the accounting code saved in the ticket record so we can report hours spent working on that accounting code.  How can I accomplish this?

 

Asked 9 years ago 1657 views
2 Comments   [ + ] Show comments
  • You'll have to write a ticket rule if you want it done automatically where it looks up the user that is submitting the ticket and updates a field in the ticket with the account code.

    Another option is if your users go to the portal to send in tickets. Create a drop down with the various codes, besides accounting, and make it a required field they have to fill in before they can submit a ticket. - nshah 9 years ago
  • I'm working on the ticket rule to run when the ticket is saved. Is there a way to select just the ticket that is being saved in the select portion? I'm brand new to this product. It doesn't seem like this should be very difficult. But so far it doesn't update the custom field.

    Update statement looks like this:

    UPDATE
    HD_TICKET Inner Join
    USER On HD_TICKET.SUBMITTER_ID = USER.ID Inner SET HD_TICKET.CUSTOM_FIELD_VALUE8 = USER.BUDGET_CODE WHERE HD_TICKET.ID IN (<TICKET_IDS>)

    How to get the right value for <TICKET_IDS> ? - mreigler 9 years ago
    • The select statement needs to return the ticket IDs in a column named ID. Rules generated with the wizard should automatically do this. Those values are fed to the update statement and KACE replaces the <TICKET_IDS> with a comma separated list when the rule runs. - chucksteel 9 years ago
      • Is there anyway to select just the ticket ID of the ticket being saved. I don't want to update all the tickets from a user. If a user moves departments I need the old tickets to remain with the code from the old department. - mreigler 9 years ago
      • The select statement in the rule specifies which tickets are selected. If your rule is too broad then it will match too many tickets. For something like this I normally set the default value of the custom ticket field to "Set on Save" and have my rule check for that. This way the rule only runs once per ticket. - chucksteel 9 years ago

Answers (1)

Posted by: chucksteel 9 years ago
Red Belt
0
Here is the update statement I use to populate a service desk field with the user's location:
update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE8 = (select LOCATION FROM USER  where ID = SUBMITTER_ID) where 
        T.ID = <TICKET_IDS>;
To use a different field change the select statement to match that field:
update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE8 = (select BUDGET_CODE FROM USER  where ID = SUBMITTER_ID) where 
        T.ID = <TICKET_IDS>;

 
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