After an upgrade from v5, the built-in Product field becomes a ticket custom field. Reports that use tickets.product need to use the custom field instead. This article shows the replacements.

What changed

• When an older help desk upgrades, the Product field moves into a tree-type ticket custom field, for example tickets.custom_data[#].

• Each ticket's product is now stored and updated in that custom field.

• The old tickets.product fields are no longer kept up to date. Product titles only show on tickets created before the upgrade.

• tickets.product.parent.* is always empty.

• Category is converted in the same way.

The parent groups still exist inside the new field. There's no single custom field column for the parent, so you need the block below.

Replace the old fields

In each example, replace 203 with the ID of your Product field.

Product title

Replace tickets.product.title with tickets.custom_data[203].

Product parent

Replace tickets.product.parent.title with this block:

(SELECT custom_def_ticket.title
 FROM custom_def_ticket
 WHERE custom_def_ticket.parent_id = 203
 AND (
   LOCATE(CONCAT('i:', custom_def_ticket.id, ';'),
     (SELECT GROUP_CONCAT(custom_data_ticket.field.options)
      FROM custom_data_ticket
      WHERE custom_data_ticket.ticket_id = tickets.id
      AND custom_data_ticket.root_field_id = 203))
   OR LOCATE(CONCAT('"', custom_def_ticket.id, '"'),
     (SELECT GROUP_CONCAT(custom_data_ticket.field.options)
      FROM custom_data_ticket
      WHERE custom_data_ticket.ticket_id = tickets.id
      AND custom_data_ticket.root_field_id = 203))
 )
)

You can use the block in these places:

• SELECT: block AS 'Product parent'

• WHERE: block = 'Parent name'

• GROUP BY: GROUP BY block AS 'Product parent'. Without the alias, the column header shows the raw SQL.

💡 Note: Paste the block exactly, including the quote marks. It only works in queries that start with FROM tickets. Top-level products show an empty parent.

Count tickets per parent

If you only need counts per parent, this query is simpler:

SELECT DPQL_COUNT() AS 'Tickets'
FROM tickets
WHERE tickets.custom_data[203] <> NULL
GROUP BY DPQL_HIERARCHY(tickets.custom_data[203], 1)

Troubleshooting

• Filtering for tickets with a product? Use <> NULL, not IS NOT NULL.

• No results with tickets.custom_data[203].field? These paths don't work in DPQL on the converted field. Use the block above instead.

• Using DPQL_HIERARCHY_DESCENDS_FROM? It doesn't work on the converted field. Use DPQL_HIERARCHY or the block above.