Database picker field showing '- null' at the end of drop down selection

[Jose Ramirez]

We implemented a database picker field that almost works the way we need it to, however, during the drop down selection, we are seeing a '- null' in the UI from our field, only when selecting the value.

When the value is selected, the trailing '- null' goes away (that is good) in the actual issue field (view screen).

In the drop down, the value '- null' does not appear.

Only in the field itself during edit, does the '- null' appear.

I have pictures included above for a better illustration.

//Configuration Script below

import com.atlassian.jira.issue.Issue

import com.onresolve.scriptrunner.canned.jira.fields.editable.database.SqlWithParameters

import groovy.sql.GroovyRowResult

// IS_ACTIVE = 1 Active/Enabled

getSearchSql = { String inputValue, Issue issue, originalValue ->

    // return SqlWithParameters, that will control the search SQL..., eg:

    new SqlWithParameters("""

        select INITIATIVE_INTERNAL_ID, INITIATIVE_ID, INITIATIVE_NAME, IS_ACTIVE

        from [rpt].[JIRA_INITIATIVE_V]

        where lower(INITIATIVE_NAME) like '%' + lower(?) + '%' and

        (IS_ACTIVE = 1 or INITIATIVE_INTERNAL_ID = ?)

        """, [inputValue, originalValue])

}

getValidationSql = { id, Issue issue, originalValue ->

    new SqlWithParameters("""

        select INITIATIVE_INTERNAL_ID, INITIATIVE_ID, INITIATIVE_NAME,IS_ACTIVE

        from [rpt].[JIRA_INITIATIVE_V]

        where INITIATIVE_INTERNAL_ID = ? and

        (IS_ACTIVE = 1 or ? = ?)

        """, [id, id, originalValue])

}

//renderOptionHtml = { String displayValue, GroovyRowResult row ->

//    "${row.INITIATIVE_ID} - ${row.INITIATIVE_NAME}"

//}

renderOptionHtml = { String displayValue, GroovyRowResult row ->

    def init_id = row[1].toString()

    def init_name = row[2].toString()

    "$init_id - $init_name"

}

[Tuncay Senturk · Accepted solution]

Hi @Jose Ramirez 

I am not sure but this may be most probably because how you handle empty or null values for the INITIATIVE_NAME field. When it is null or empty, the rendered string becomes something like INITIATIVE_ID - null.

I'd change the renderOptionHtml function as below

renderOptionHtml = { String displayValue, GroovyRowResult row ->
   def init_id = row[1]?.toString() ?: ""
   def init_name = row[2]?.toString() ?: "No Name"
   "$init_id - $init_name"
}

[Jose Ramirez]

Hi @Tuncay Senturk ,

Your answer was closer to the solution, but the No Name appended the '- No Name' to the value, therefore I took the extra step and removed the "No Name" to it, made it "", and we're even closer now, still have a trailing '-' (see picture).

renderOptionHtml = { String displayValue, GroovyRowResult row ->

   def init_id = row[1]?.toString() ?: ""

   def init_name = row[2]?.toString() ?: ""

   "$init_id - $init_name"

}

This is passable for our purposes but it would be perfect to get the trailing '-' out of it.

[Jose Ramirez]

@Tuncay Senturk  Thank you for getting us closer to our goal.

[Tuncay Senturk]

Use the below code

renderOptionHtml = { String displayValue, GroovyRowResult row ->
   def init_id = row[1]?.toString() ?: ""
   def init_name = row[2]?.toString() ?: ""

   // Construct the display value based on available parts
   if (init_id && init_name) {
     "$init_id - $init_name"
   } else {
     "$init_id$init_name"
   }
}

[Jose Ramirez]

Awesome, thank you, that was the golden touch.

[Tuncay Senturk]

Pleasure!

[Tuncay Senturk]

Please accept the answer so that others can benefit from it.