JQL sort on custom number field not working

[Dave Cuff]

Please assist.  I have a very simple JQL query that is not sorting my custom number field correctly.  Seems to be treating the field as a string.

[Nic Brough -Adaptavist-]

What type of field is your urgency?

[Dave Cuff]

Hi Nic

 The urgency field is also a custom number field

[Nic Brough -Adaptavist-]

Sorry, I completely missed that - I meant to ask what type of field the "priority score" is, not urgency.

[Dave Cuff]

Hi Nic,

No worries.  Was wondering. The priority score field is also a number field.  Screenshot is in the first message

Dave

[Nic Brough -Adaptavist-]

Hmm.  How are you filling the field in?  Are humans typing it in on create/edit/transition, or is it being calculated by something?

[Dave Cuff]

Hi Nic,

They are calculated fields.  Calculated using scriptrunner scripts in listeners.

Dave

[Dave Cuff]

BTW - Here is an example of one of the scripts.

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.issue.customfields.option.LazyLoadedOption

def customFieldManager = ComponentAccessor.customFieldManager
def issueLinkManager = ComponentAccessor.issueLinkManager
def issue = event.issue

// Calculations included in this script
// Size score
// Benefit Score
// Strategic Focus Area
// Importance rating
// ROI Score
// Opportunities Score
// Updated priority score on linked issues.

//
// Added by David Cuff 14/8/2020
// Calculate the Size Score
//
// If issue does not have the correct status do not do these calculations.

//if (issue.status.name == "Prioritised" || issue.status.name == "Classified" ) {
def finField = customFieldManager.getCustomFieldObjects(issue).findByName("Financial Impact")

double finScore1;
LazyLoadedOption finOption = (LazyLoadedOption) issue.getCustomFieldValue(finField)

if (finOption?.getValue() == "< R1 millon") {
finScore1 += 1
} else if (finOption?.getValue() == "< R10 million") {
finScore1 += 2
} else if (finOption?.getValue() == "> R10 million") {
finScore1 += 3
} else {
finScore1 = 0
}

//
// Added by David Cuff 14/8/2020
// Calculate the Size Score
//

def sizeScoreField = customFieldManager.getCustomFieldObjects(issue).findByName("Size Score")
def sizeField = customFieldManager.getCustomFieldObjects(issue).findByName("Size Impact")

double sizeScore1
double sizeScore2
LazyLoadedOption sizeOption = (LazyLoadedOption) issue.getCustomFieldValue(sizeField)

if (sizeOption?.getValue() == "≤ 170 work hours") {
sizeScore1 += 1
} else if (sizeOption?.getValue() == "≤ 700 work hours") {
sizeScore1 += 2
} else if (sizeOption?.getValue() == "> 700 work hours") {
sizeScore1 += 3
} else {
sizeScore1 = 0
}
sizeScore2 = finScore1 + sizeScore1
sizeScoreField.updateValue(null, issue,
new ModifiedValue(issue.getCustomFieldValue(sizeScoreField), sizeScore2),
new DefaultIssueChangeHolder())
//
// Added by David Cuff 12/8/2020
// Calculate the Benefit Score - Business Architecture Impact
//

def benefitScoreField = customFieldManager.getCustomFieldObjects(issue).findByName("Benefits Score")
def busArchImpactField = customFieldManager.getCustomFieldObjects(issue).findByName("Business Architecture Impact")

double benefitScore1;
LazyLoadedOption benefitOption = (LazyLoadedOption) issue.getCustomFieldValue(busArchImpactField)

if (benefitOption?.getValue() == "No impact on business capability maturity") {
benefitScore1 += 1
} else if (benefitOption?.getValue() == "Enhance or extend to existing business capabilities") {
benefitScore1 += 2
} else if (benefitOption?.getValue() == "Creates new business capability or matures existing capability") {
benefitScore1 += 3
} else {
benefitScore1 = 1
}
benefitScoreField.updateValue(null, issue,
new ModifiedValue(issue.getCustomFieldValue(benefitScoreField), benefitScore1),
new DefaultIssueChangeHolder())
//
// Added by David Cuff 12/8/2020
// Calculate the Strategic Focus Area Score
//

def strategicFocusAreaField = customFieldManager.getCustomFieldObjects(issue).findByName("Strategic Focus Area")
//def strategicFocusAreaScore = issue.getCustomFieldValue(opportunitiesScoreField)
double strategicFocusAreaScore
if ( strategicFocusAreaScore != null )
{
String myString = ""
String myString2 = ""
int strategicFocusAreaCount = 1
int int1 = 0
int int2 = 0
myString = strategicFocusAreaScore
while ( int1 < myString.length() - 1)
{
int1++
int2 = int1 + 1
myString2 = myString.substring(int1, int2)
if ( myString2 == "," )
{
strategicFocusAreaCount++
}
}
if ( strategicFocusAreaCount == 1 )
{
strategicFocusAreaScore += 1
} else if ( strategicFocusAreaCount == 2 ) {
strategicFocusAreaScore += 2
} else if ( strategicFocusAreaCount == 3 ) {
strategicFocusAreaScore += 4
} else if ( strategicFocusAreaCount >= 4 ) {
strategicFocusAreaScore += 8
}

// importanceRatingScoreField.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(importanceRatingScoreField), importanceRatingScore), new DefaultIssueChangeHolder())
}
//
// Added by Nicholas Sithole 23/7/2020
// Calculate the Urgency Rating Score
//
//def urgencyRatingField = customFieldManager.getCustomFieldObjects(issue).findByName("Urgency Rating")
//def urgencyRatingScoreField = customFieldManager.getCustomFieldObjects(issue).findByName("Urgency Rating Score")

//LazyLoadedOption urgencyRatingOption = (LazyLoadedOption) issue.getCustomFieldValue(urgencyRatingField)

//double urgencyRatingScore;

//switch(urgencyRatingOption?.getValue()) {
// case "Passed Due - The required date has passed":
// urgencyRatingScore = 32;
// break;
// case "Critical - Required in the next 1 to 2 months":
// urgencyRatingScore = 16;
// break;
// case "Very High - Required in the next 2 to 3 months":
// urgencyRatingScore = 8;
// break;
// case "High - Required in the next 3 to 6 months":
// urgencyRatingScore = 4;
// break;
// case "Medium - Required in the next 6 to 12 months":
// urgencyRatingScore = 2;
// break;
// case "Low - Required in the next 12+ months":
// urgencyRatingScore = 1;
// break;//

// default:
// urgencyRatingScore = 0;
// break;
//}

//
// Calculate the importance rating score
//
def importanceRatingScoreField = customFieldManager.getCustomFieldObjects(issue).findByName("Importance Rating Score")
def importanceRatingField = customFieldManager.getCustomFieldObjects(issue).findByName("Business Importance")

double importanceRatingScore;

LazyLoadedOption importanceRatingOption = (LazyLoadedOption) issue.getCustomFieldValue(importanceRatingField)

switch(importanceRatingOption?.getValue()) {
case "Very Low: Not one of the top 20 prioritised work requests of the business area":
importanceRatingScore = 1;
break;
case "Low: One of the top 20 prioritised work requests of the business area":
importanceRatingScore = 2;
break;
case "Medium: One of the top 10 prioritised work requests of the business area":
importanceRatingScore = 4
break;
case "High: One of the top 5 prioritised work requests of the business area":
importanceRatingScore = 8;
break;
default:
importanceRatingScore = 0;
break;
}

importanceRatingScoreField.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(importanceRatingScoreField), importanceRatingScore), new DefaultIssueChangeHolder())

//
// Add the ROI score to the importance score
//
def projectdriver = customFieldManager.getCustomFieldObjects(issue).findByName("Project Driver")
LazyLoadedOption projectDriverOption = (LazyLoadedOption) issue.getCustomFieldValue(projectdriver)

if (projectDriverOption?.getValue() == "ROI") {
importanceRatingScore += 4
} else if (projectDriverOption?.getValue() == "No ROI") {
importanceRatingScore += 2
} else if (projectDriverOption?.getValue() == "Mandatory") {
importanceRatingScore += 8
} else {
importanceRatingScore = 88
}
importanceRatingScoreField.updateValue(null, issue,
new ModifiedValue(issue.getCustomFieldValue(importanceRatingScoreField), importanceRatingScore),
new DefaultIssueChangeHolder())
//
// Add the Importance score and Strategic Focus Area score to the Benefit Score
//
double benefitScore2
benefitScore2 = benefitScore1 + importanceRatingScore + strategicFocusAreaScore
benefitScoreField.updateValue(null, issue,
new ModifiedValue(issue.getCustomFieldValue(benefitScoreField), benefitScore2),
new DefaultIssueChangeHolder())
//
// Add the Opportunities score to the importance score
//
def opportunitiesScoreField = customFieldManager.getCustomFieldObjects(issue).findByName("Project opportunities - Importance")
def opportunitiesScore = issue.getCustomFieldValue(opportunitiesScoreField)

if ( opportunitiesScore != null )
{
String myString = ""
String myString2 = ""
int opportunityCount = 1
int int1 = 0
int int2 = 0
myString = opportunitiesScore
while ( int1 < myString.length() - 1)
{
int1++
int2 = int1 + 1
myString2 = myString.substring(int1, int2)
if ( myString2 == "," )
{
opportunityCount++
}
}
if ( opportunityCount == 1 )
{
importanceRatingScore += 1
} else if ( opportunityCount == 2 ) {
importanceRatingScore += 2
} else if ( opportunityCount == 3 ) {
importanceRatingScore += 4
} else if ( opportunityCount >= 4 ) {
importanceRatingScore += 8
}
importanceRatingScoreField.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(importanceRatingScoreField), importanceRatingScore), new DefaultIssueChangeHolder())
}

//
// Calculate the Priority Score
//
//def priorityScoreField = ComponentAccessor.customFieldManager.getCustomFieldObjectByName("Priority Score")
//
//double priorityScore = urgencyRatingScore * importanceRatingScore;

// Update linked feature priority score
//def linkedIssues = issueLinkManager.getLinkCollectionOverrideSecurity(issue).getAllIssues()
//if (linkedIssues) {
// linkedIssues.each {
// def oldPriorityScore = it.getCustomFieldValue(priorityScoreField)
// priorityScoreField.updateValue(null, it, new ModifiedValue(oldPriorityScore, priorityScore), new DefaultIssueChangeHolder())
// def oldBenefitScore = it.getCustomFieldValue(benefitScoreField)
// benefitScoreField.updateValue(null, it, new ModifiedValue(oldBenefitScore, benefitScore2), new DefaultIssueChangeHolder())
// def oldSizeScore = it.getCustomFieldValue(sizeScoreField)
// sizeScoreField.updateValue(null, it, new ModifiedValue(oldSizeScore, sizeScore2), new DefaultIssueChangeHolder())
// }
//}

// Set custom field values
//urgencyRatingScoreField.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(urgencyRatingScoreField), urgencyRatingScore), new DefaultIssueChangeHolder())
importanceRatingScoreField.updateValue(null, issue, new ModifiedValue(issue.getCustomFieldValue(importanceRatingScoreField), importanceRatingScore), new DefaultIssueChangeHolder())

[Nic Brough -Adaptavist-]

I am going to admit I am struggling to read the script (I'm not good when a well-laid-out script is posted as written text and possibly with missing line-breaks, but that's not your fault, Community doesn't handle copy/paste well in my humble opinion)

But I think the important bit I was looking for seems to be there - you previously said your field type is number, and I'm 99% sure your script writes a number type object into it.

So, it's not your setup (field or script).  I am stuck, I cannot see what else it might be.  But I would like to try two more tests to try to get a bit more info:

• The easy one - could you repeat the screenshot you took originally, but after changing the "order by" to ASC?  - your 5 issues show the problem perfectly, but we may be able to spot a potential pattern if the ordering comes out differently for the same 5 when it is reversed.  (However, if the order really is 100% reversed, as in 12345 turns into 54321, don't bother with screenshots, we just need to know it is cleanly reversed)
• The more painful one - could you log in as an admin and re-index the project keyed with WR and then run the search again twice (with the order by clauses DESC and ASC) and see if you get the same?

[Dave Cuff]

Hi Nic,

Sorry for delay in replying.  Re-indexing did solve the problem.  I am rather concerned that it was the solution.  Why would re-indexing help and then how often should we re-index

I do still have an issue with another number field also updated using a double field.  does this mean I must re-index again?

[Nic Brough -Adaptavist-]

Ah, that makes sense.

Your listener is amending data on issues, but not indexing it.  Listeners are intended to do something in response to an event, they can't know that an issue may need re-indexing, you have to code for it to tell Jira to re-index the issue.