Accessing custom fields

DPQL Reference

Several content types in Deskpro, including tickets and articles, support custom fields. You can access these fields in custom reports using the following syntax.

Each custom field has an ID number. To see them in the admin interface, click the small gear icon at the top right, and select Show IDs. Make note of the ID as it will be needed in your query.

../_images/custom-field-ids.png

Once you have the ID, you can reference the value of a particular field via custom_data[#]. For example, to refer to a custom ticket field with ID number 12, you would use tickets.custom_data[12].

If you are trying to retrieve a large number of fields, (over about 55), limitations in the underlying MySQL database will cause the query to fail with a “There is something wrong with your query” message. This is only likely to be an issue if you have a lot of custom fields. You can work around this limitation by retrieving data with the API Basics

This query will give a count of all tickets created this month per value for the custom field with ID 12:

SELECT DPQL_COUNT() AS 'Total Tickets'
FROM tickets
WHERE tickets.date_created = %THIS_MONTH%
GROUP BY tickets.custom_data[12]

This will list the tickets created this month, split by the values of the custom field with ID 12:

SELECT tickets.id, tickets.subject, tickets.person
FROM tickets
WHERE tickets.date_created = %THIS_MONTH%
SPLIT BY tickets.custom_data[12]

This will list a count of the tickets which have a particular custom field option set in the ticket (in this example, it will look for tickets with 'Desired Size' set to Medium or Large):

SELECT DPQL_COUNT()
FROM tickets
WHERE tickets.custom_data.field_id = '10' OR tickets.custom_data.field_id = '9'

image.png

Published: 23/03/2018

Last updated: 02/12/2019