Service Desk Custom Field: Is it possible to reference one custom field in the query of another custom field?
Any help on this would be greatly appreciated. Sorry if I'm asking this in a confusing way or if there is a terribly obvious answer that I'm missing. I'm trying to customize a ticket in the Service Desk portion of Kace Systems Management Appliance. My question is, is it possible to reference a custom field name in the query of another custom field's query? Is this something I can do or would this require paid development by Quest?
Our Help Desk team asked for a custom field where they can select from a dropdown the name of the user that the ticket is for and have that auto-populate several other custom fields(department, location, phone number). We want to use the Submitter field to hold the name of the technician. We aren't deadset on this so we can change the Submitter to be the person the work is being done for if that would help.
I created a custom field that is type User, named CUSTOM_37, labeled User. I created another custom field that is type Single Select, named CUSTOM_5, labeled Department.
The Department custom field(CUSTOM_5) has this query for both the Default and Select Values
query:SELECT DISTINCT USER_FIELD_VALUE.FIELD_VALUE FROM ORG1.USER_FIELD_VALUE WHERE FIELD_ID = 3;
This pulls back a list of our departments and selects the first department is finds. We are storing our departments as Field_ID3 on the USER_FIELD_VALUE table.
Is there something I can do to change the query to pull back the department based on the user name or user ID? Or will the require modification by Quest?
Thank you for any help
Answers (3)
Hi Andy,
Please describe in detail the workflow to help me better understand the ask.
How is the ticket created? Web, Email, or both?
Who do you want as the actual submitter? The customer, or employee with an issue, or the person who created the ticket on behalf of someone else.
Will the ticket owner be the person who works on the ticket until resolution?
Top Answer
Thanks so much for getting back to me! I really appreciate it
For now we are looking at creating the ticket over the web but we would like to create with email eventually. We have a Help Desk team that will create the tickets inside of Kace.
I looked in to how the ticket is created and what it looks like afterwards. It looks like we will be able to go with the submitter being the person that the ticket is for in most cases. E.g. Jack(owner) builds a computer for Jill(Submitter). I had missed that the ticket shows the ticket creator at the top of the ticket after it is created. I was previously going to use the submitter to keep track of the creator. Now that won't be necessary.
Is it possible for a service desk custom field query to reference the Submitter's name or ID? We would like this to keep track of certain information for the Submitter. On our current help desk system, when the help desk technician selects the name of the submitter from a dropdown, several fields are auto-populated. I noticed this may not be necessary as well because once the ticket is created you can click on the information field next to the Submitter label and see much of the user's information. I would still like to know if it is possible.
e.g.
query:SELECT USER_FIELD_VALUE.FIELD_VALUE FROM ORG1.USER_FIELD_VALUE WHERE USER_ID = 1871 AND FIELD_ID = 3;
Instead of the specific ID(in this case 1871), I would like to use a variable where the variable is the Submitter ID field on the ticket about to be created
Comments:
-
Andy,
The SMA feature you want to use is "Ticket Rules."
This feature is powerful and dangerous at the same time. As a precaution, you should fully back up the SMA before testing any ticket rule. If possible, you can try on a development SMA.
Ticket rules can run on Ticket Save to query the current values and update/change the original values based on the results.
You can use the search option to find previous posts that contain ticket rules and SQL syntax examples. - KevinG 9 months ago-
Interesting. I will definitely look in to those. Thank you for the suggestion! I could create the custom user fields on the tickets, have the help desk technician leave those fields blank when creating tickets, have new tickets set to a custom status, run a rule on all tickets with that custom status to check for the needed information and fill in the user field that the help desk technician left blank - andy.edge 9 months ago
-
As Hobbsy stated, you cannot do this as a dynamic field. To reiterate what KevinG said, Ticket Rules are the way to go. This is what I have done to accomplish what you are looking for and you can keep everything on the original template. Put your custom fields on the template. Hide the fields that you want to calculate. At the end of the fields (on the template) that you want the user to populate, add a checkmark for something like "Calculate Fields" and instruct them to hit "Apply." Make the hidden fields appear based on Conditional Logic based on that field being true. Write Ticket Rule(s) based on Ticket Save and filter the Where clause on Change ID and Description. Test in your TEST environment before implementing in Production. Bad things can happen if you are not careful (like updating tickets that you didn't mean to update). Engage help if you need it. - Gurnzy Girl 9 months ago
-
Thank you for your help. I appreciate it! - andy.edge 9 months ago
You cannot do this as a dynamic field, in other words, selecting a value on the screen that populates other fields with values, as you would need to run a ticket rule and that means you need to save the ticket.
You can of course have more dynamic forms using templates and conditional formatting but that is a close as you can get.