/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Objective: After selecting a user using a CUSTOM_FIELD with the field type of USER, I want the Service Desk to do a query on the KACE database to return the assets associated with that USER.

Challenge: Once I save a ticket with the results of a query, the ASSET query results from previous tickets are shown in the CUSTOM_FIELD which defeats the purpose of showing which assets are associated with the selected USER.

Troubleshooting: I have tried creating a new queue with the same query. Results are shown in the new Queue. 

I have tried moving to a different field in the same queue with the same query.  Results are shown in the new field.

I have tried moving to a different field in a new queue with the same query.  Results are shown in the new queue in the new field.

I have tried deleting some of the entries in the database using a trigger thinking that somewhere in the database, the query results were being cached.  I looked at HD_TICKET_CHANGE and HD_TICKET_CHANGE_FIELD.  Removing the BEFORE_VALUE and AFTER_VALUE as well as the LOCALIZED_DESCRIPTION did not clear the query results.

I defined the following fields:

Field: CUSTOM_FIELD_VALUE14
Field Type: User
Ticket Layout, Label: User
Required: Not Required
Permission: Owners Only - Hidden from User

Field: CUSTOM_FIELD_VALUE0
Field Type: Multiple Select
Selected Values: Query: Select ASSET.NAME from ASSET, USER, HD_TICKET where HD_TICKET.CUSTOM_FIELD_VALUE14=USER.ID and USER.ID=ASSET.OWNER_ID and HD_TICKET.ID=HD_TICKET.CUSTOM_FIELD_VALUE2
Ticket Layout, Label: Asset by User
Required: Not Required
Permission: Owners Only - Hidden from User

Field: CUSTOM_FIELD_VALUE2
Field Type: Text
Ticket Layout, Label: Ticket #
Required: Not Required
Permission: Owners Only - Hidden from User

Ticket Rule:
Name: Enter Ticket # on Save
If CUSTOM_FIELD_VALUE2='' then set CUSTOM_FIELD_VALUE2=<TICKET_IDS>

 

Answer Summary:
The SQL query being used is not able to select a specific ticket and so the SQL query is pulling the results form all tickets, which resulted in results being "appended" in the field.
Cancel
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

Answers

1

I don't think it's possible to make a select list based on information for the given ticket because there's no way to format the query for a specific ticket that I can think of. I think that the closest you can get would be to have a text field that contains all of the assets assigned to the user, it wouldn't be a select list so you couldn't choose a particular asset for the user. 

So your CUSTOM_FIELD_VALUE0 would be a Notes field and then you would have a rule that would populate that field based on CUSTOM_FIELD_VALUE14.

Would that accomplish what you need?

Answered 03/13/2013 by: chucksteel
Red Belt

  • Thanks for the response. The way I get around the challenge of which ticket to reference is by having a trigger rule that inserts the ticket # into a custom field in the ticket. That way when I need to reference the ticket # in a specific ticket I do: HD_TICKET.ID=HD_TICKET.CUSTOM_FIELD_VALUE#.

    I am able to make the query work, the challenge is in the query retaining the value between tickets.

    Thanks again chucksteel!
    • That's just it. I don't think that the query is working correctly because it still can't reference the current ticket. Won't all of your tickets have CUSTOM_FIELD_VALUE# equal to the ticket ID? If so, then the query still can't determine which specific ticket is being referenced.
  • Ah I get it now. So when the query runs because it's looking through all the tickets where my CUSTOM_FIELD_VALUE# = HD_TICKET.ID it pulls those assets. That would explain why results from previous tickets are getting appended to results in other tickets.

    Thanks chucksteel!
Please log in to comment