Archived Work Items by Custom Field Option in Insights

Jordi Romera

Have you ever tried to delete a Jira custom field option, only to discover that Jira says it is still in use?

A custom field option may look unused in JQL searches but still be present in archived work items. When the custom field is shared across many Jira spaces, finding those work items manually can be difficult.

You could raise a ticket with Atlassian Support and ask them to identify the archived affected work items. Or you could use an Insights chart and find them yourself.

This chart shows the archived work items that use a selected custom field option.

It supports these custom field types:

• Select List (single choice)
• Select List (multiple choices)
• Checkboxes
• Radio Buttons

A small chart for a Jira administrator, one less support ticket for Atlassian.

Follow these steps to create the Archived Work Items by Custom Field Option

Open Atlassian Insights: https://YOURSITE.atlassian.net/insights.

If your site still uses Atlassian Analytics open https://YOURSITE.atlassian.net/analytics/ instead.

Click on the Create Dashboard blue button on the top-right corner:

Create a new chart on the dashboard:

Select the custom chart option:

Select the SQL tab option:

Copy and paste the SQL code below, then click “Run Query”:

SQL query:

SELECT DISTINCT
    i.issue_key AS `Work item key`,
    f.name AS `Custom field`,
    f.field_key AS `Field key`,
    f.value AS `Custom field option`
FROM jira_issue AS i
INNER JOIN jira_issue_field AS f
    ON f.issue_id = i.issue_id
    AND f.workspace_id = i.workspace_id
WHERE i.is_archived = TRUE
  AND f.field_type IN (
      'com.atlassian.jira.plugin.system.customfieldtypes:select',
      'com.atlassian.jira.plugin.system.customfieldtypes:multiselect',
      'com.atlassian.jira.plugin.system.customfieldtypes:multicheckboxes',
      'com.atlassian.jira.plugin.system.customfieldtypes:radiobuttons'
  )
  AND f.value IS NOT NULL
  AND TRIM(f.value) <> ''
ORDER BY
    `Custom field` ASC,
    `Custom field option` ASC,
    `Work item key` ASC
LIMIT 10000

The chart has been created successfully:

Now, select a custom field option to find the archived work items that use it:

Less searching, faster access to archived work items, and fewer surprises when deleting Jira custom field options 😄

Have you built any Jira Admin life-saving charts in Insights?