When using a SQL statement in a Service Desk custom field that leverages the values in other custom fields of a ticket, should the query results be appended between tickets?
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>
Answers (1)
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.
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?
Comments:
-
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! - Domo2k 11 years ago-
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. - chucksteel 11 years ago
-
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! - Domo2k 11 years ago
