/build/static/layout/Breadcrumb_cap_w.png

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>

 


0 Comments   [ + ] Show comments

Answers (1)

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.
Posted by: chucksteel 11 years ago
Red Belt
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?


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

Don't be a Stranger!

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

Sign up! or login

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