How to Populate a Helpdesk Custom Field Using Results From a Database Query

How to Populate a Heldpesk Custom Field Using Results From a Database Query

With version 5.x and newer of the K1000 appliance you can designate a query in a custom helpdesk field which will populate that field with the results of that query.

The query should have the following properties:

• It should only return one single column
• The query should be sorted in some way -- usually on the column being returned

The custom field should have the following properties:

• Has not been used to store other data in this queue before (or old tickets that have used it have been deleted / the field values have been cleared)
• It can have a default value, but it is recommend that it is one in the results

The way to do it is to

1. Go to the customization of the queue:
   Heldpesk->Configuration->*Select the queue)->Ticket Defaults[customize these values]
2. Then under the section called "Custom fields" you define the desired field to be of field type "single select" or "multiple select".
3. Set the select list of values as the query with the word "query:" written at the front so that the K1000 appliance knows to get the results.
   e.g.

   query:select ASSET.NAME FROM ASSET WHERE ASSET_TYPE_ID=5 ORDER BY ASSET.NAME ASC

Troubleshooting

• If the query returns an error then you will see the message "An SQL error occurred generating the List" inside the custom field when viewing the ticket. You can test the query using the [Mysql Query Browser]
• If you delete the query later the values will not be cleared from the tickets.
• Only the first column in the query will show in the list of values so that is why your query should only return on column
• You should have no carriage returns in your query. This a common thing to have if testing somewhere else and pasting it in