The incidents in Spira are able to have customizable incident types, statuses, priorities and severities. This means that each project has unique IDs for the various attributes. Sometimes when you incorrectly load in data using our Excel Add-In, you can link incidents to attributes from other projects. This article describes the problem and the solution.

Background

The incidents in SpiraTest, SpiraPlan, and SpiraTeam allow you to have customized statuses, types, priorities and severities for each project. That means that you can have different values for these three attributes in each project. In fact, even if you have the same status names, they will have different ID values (the same is true for the other fields). Normally this is not a problem as Spira knows which status list to use for each project. However when you load in data from our Excel Add-In, it is possible to accidentally link incidents in project A with a status from project B (for example).

Detecting the Issue

The main symptom for this situation, will be that the incidents have a status, type, priority, or severity on the incident list page, but it is not selected when you go to the details page, or when you try to edit the incident on either page it is not selected. In addition, the count of (All Open) incidents on the graph will not match the count of items in the incident grid.

To conclusively verify if this is the issue, you need to run one of the following SQL commands, so to see if any incidents are listed as mismatched (or if cloud hosted, raise a support ticket for a member of the Inflectra support team to do this for you):

Status

SELECT INC.INCIDENT_ID, INC.NAME, INC.PROJECT_ID AS INCIDENT_PROJECT, INS.PROJECT_ID AS STATUS_PROJECT
FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_STATUS INS
ON INC.INCIDENT_STATUS_ID = INS.INCIDENT_STATUS_ID
WHERE INC.PROJECT_ID <> INS.PROJECT_ID

Type

SELECT INC.INCIDENT_ID, INC.NAME, INC.PROJECT_ID AS INCIDENT_PROJECT, INT.PROJECT_ID AS TYPE_PROJECT
FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_TYPE INT
ON INC.INCIDENT_TYPE_ID = INT.INCIDENT_TYPE_ID
WHERE INC.PROJECT_ID <> INT.PROJECT_ID

Priority

SELECT INC.INCIDENT_ID, INC.NAME, INC.PROJECT_ID AS INCIDENT_PROJECT, INP.PROJECT_ID AS PRIORITY_PROJECT
FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_PRIORITY INP
ON INC.PRIORITY_ID = INP.PRIORITY_ID
WHERE INC.PROJECT_ID <> INP.PROJECT_ID

Severity

SELECT INC.INCIDENT_ID, INC.NAME, INC.PROJECT_ID AS INCIDENT_PROJECT, INS.PROJECT_ID AS SEVERITY_PROJECT
FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_SEVERITY INS
ON INC.SEVERITY_ID = INS.SEVERITY_ID
WHERE INC.PROJECT_ID <> INS.PROJECT_ID

Fixing the Issue

To fix the issue, there are two options:

  1. If the statuses have the same names in both projects, you can the special SQL listed below to fix the issue (same for incident type, priority and severity)
  2. If they don't have the same names in both projects, you will need to manually fix using SQL Server Management Studio (SSMS).

Status

UPDATE INC
	SET INC.INCIDENT_STATUS_ID = MAP.NEW_STATUS_ID
FROM 
	TST_INCIDENT INC INNER JOIN
		(SELECT INC.INCIDENT_ID, T1.INCIDENT_STATUS_ID AS OLD_STATUS_ID, T2.INCIDENT_STATUS_ID AS NEW_STATUS_ID
		FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_STATUS T1
		ON INC.INCIDENT_STATUS_ID = T1.INCIDENT_STATUS_ID INNER JOIN TST_INCIDENT_STATUS T2
		ON INC.PROJECT_ID = T2.PROJECT_ID AND T1.NAME = T2.NAME
		WHERE T1.PROJECT_ID <> T2.PROJECT_ID) MAP
	ON INC.INCIDENT_ID = MAP.INCIDENT_ID
WHERE 
	MAP.INCIDENT_ID = INC.INCIDENT_ID

Type

UPDATE INC
	SET INC.INCIDENT_TYPE_ID = MAP.NEW_TYPE_ID
FROM 
	TST_INCIDENT INC INNER JOIN
		(SELECT INC.INCIDENT_ID, T1.INCIDENT_TYPE_ID AS OLD_TYPE_ID, T2.INCIDENT_TYPE_ID AS NEW_TYPE_ID
		FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_TYPE T1
		ON INC.INCIDENT_TYPE_ID = T1.INCIDENT_TYPE_ID INNER JOIN TST_INCIDENT_TYPE T2
		ON INC.PROJECT_ID = T2.PROJECT_ID AND T1.NAME = T2.NAME
		WHERE T1.PROJECT_ID <> T2.PROJECT_ID) MAP
	ON INC.INCIDENT_ID = MAP.INCIDENT_ID
WHERE 
	MAP.INCIDENT_ID = INC.INCIDENT_ID

Priority

UPDATE INC
	SET INC.PRIORITY_ID = MAP.NEW_PRIORITY_ID
FROM 
	TST_INCIDENT INC INNER JOIN
		(SELECT INC.INCIDENT_ID, T1.PRIORITY_ID AS OLD_PRIORITY_ID, T2.PRIORITY_ID AS NEW_PRIORITY_ID
		FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_PRIORITY T1
		ON INC.PRIORITY_ID = T1.PRIORITY_ID INNER JOIN TST_INCIDENT_PRIORITY T2
		ON INC.PROJECT_ID = T2.PROJECT_ID AND T1.NAME = T2.NAME
		WHERE T1.PROJECT_ID <> T2.PROJECT_ID) MAP
	ON INC.INCIDENT_ID = MAP.INCIDENT_ID
WHERE 
	MAP.INCIDENT_ID = INC.INCIDENT_ID

Severity

UPDATE INC
	SET INC.SEVERITY_ID = MAP.NEW_SEVERITY_ID
FROM 
	TST_INCIDENT INC INNER JOIN
		(SELECT INC.INCIDENT_ID, T1.SEVERITY_ID AS OLD_SEVERITY_ID, T2.SEVERITY_ID AS NEW_SEVERITY_ID
		FROM TST_INCIDENT INC INNER JOIN TST_INCIDENT_SEVERITY T1
		ON INC.SEVERITY_ID = T1.SEVERITY_ID INNER JOIN TST_INCIDENT_SEVERITY T2
		ON INC.PROJECT_ID = T2.PROJECT_ID AND T1.NAME = T2.NAME
		WHERE T1.PROJECT_ID <> T2.PROJECT_ID) MAP
	ON INC.INCIDENT_ID = MAP.INCIDENT_ID
WHERE 
	MAP.INCIDENT_ID = INC.INCIDENT_ID

 

Article Info
  • Last Updated: 1/28/2018
  • Article ID: KB321
  • Views: 544