1st workaround


Table of contents


Working Principle

When a value is selected in a Connect field, the key defined in the field configuration is stored in Jira:

It's this data that is migrated by Configuration Manager.

The purpose is to update the Connect field key with a value common to both Jira instances:

  • For a Connect field storing Issue IDs, the field key should be changed to Issue keys.
  • For a Connect field storing Option IDs (such as a Select List), the field key should be changed to Option names.

For example, let's suppose that the source instance contains an issue whose id is "10000" and key is "TEST-1", while the target instance contains an issue whose id is "10001" and the key is "TEST-1". The Issue key is common to both instances and can be used for matching. By updating the Connect field key from Issue ID to Issue key in the source instance, the Connect field values can be migrated correctly by Configuration Manager.

Special case: JQL fields

In the case of a field connected to the "Jira issues (JQL)" datasource, the Issue ID is stored automatically and it's not possible to change the field key. You need to switch the datasource to "Jira database (SQL)" and then convert the JQL query into an SQL query.

Steps to follow

Case 1 - when the field key is an Issue ID

Context

Let's say the Connect field that stores Issue IDs is using the following query:

SELECT id, summary FROM jiraissue

As follows:

A. Edit the Connect field with a temporary configuration

First, you need to update the Connect field configuration and change its key from Issue ID to Issue key. To do so, you need to use $currentCustomfieldValue to retrieve the old keys, as this variable contains the key currently stored in Jira.

Please update your Connect field query as follows (and keep your initial query somewhere):

#if($currentCustomfieldValue.size() > 0)
  SELECT CONCAT(p.pkey,'-',j.issuenum), j.summary
  FROM  jiraissue j
  JOIN project p ON  j.project = p.id
  WHERE j.id IN ($currentCustomfieldValue.stringList())
#else
  $query.abort()
#end

This field configuration is temporary until the script below is executed. Its purpose is to retrieve the Issue key corresponding to the returned Issue ID.

(info) If the field is connected to the "Jira issues (JQL)" datasource, switch the datasource to "Jira database (SQL)"

B. Run a script to update the Connect field keys

You then need to run a script to update the Connect field key in all affected issues, based on the temporary Connect field query configured above.

Thus, the Issue ID will be changed to the Issue key in all affected issues:

log.warn("EC SCRIPT - Script Starts")
log.warn("EC SCRIPT --------------------------------------------------")
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.event.type.EventDispatchOption
   
def pluginAccessor = ComponentAccessor.getPluginAccessor()

def customFieldManager = ComponentAccessor.getCustomFieldManager()
def issueManager = ComponentAccessor.getIssueManager()
   
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def user = ComponentAccessor.getJiraAuthenticationContext().getUser()

def query = jqlQueryParser.parseQuery("A_VALID_JQL_QUERY")   
def connectFieldsToBeUpdated = ["customfield_XXXXX","customfield_YYYYY"]

log.warn("EC SCRIPT --------------------------------------------------")
def results = searchService.search(user, query, PagerFilter.getUnlimitedFilter())
results.getResults().each { myIssue ->
    log.warn("EC SCRIPT - Updating issue "+myIssue.key)
    def issueToUpdate = issueManager.getIssueObject(myIssue.key)

    connectFieldsToBeUpdated.each{connectFieldId -> 
        def connectField = customFieldManager.getCustomFieldObject(connectFieldId)
        log.warn("EC SCRIPT - connect field: "+connectFieldId)
        def connectFieldValue = myIssue.getCustomFieldValue(connectField)
        if(connectFieldValue){
            log.warn("EC SCRIPT - => "+connectFieldId+" contains the following value:"+connectFieldValue)

            connectFieldValue = extracValueFromJson(connectFieldValue)
            log.warn("EC SCRIPT - => extracted value:"+connectFieldValue)
            
            def issueKeys = getIssueKeys(connectFieldValue, issueManager)
            if(issueKeys){
                log.warn("EC SCRIPT - => "+connectFieldId+" has been updated with these issue-keys:"+issueKeys)
                issueToUpdate.setCustomFieldValue(connectField, '{"keys":['+issueKeys+']}')
            } else {
                log.warn("EC SCRIPT - => "+connectFieldId+" has not been updated")
            }
            
        } else {
            log.warn("EC SCRIPT - => "+connectFieldId+" is empty")
        }
    }
    issueManager.updateIssue(user, issueToUpdate, EventDispatchOption.DO_NOT_DISPATCH, false)
    log.warn("EC SCRIPT --------------------------------------------------")
}

log.warn("EC SCRIPT - Script End")

def extracValueFromJson(jsonValue){
    log.warn("EC SCRIPT - ==> extracValueFromJson - Start")
    def match = (jsonValue =~ /\[.*?\]/)[0] // extract value thanks to a regex
    def valueString = match.substring(0, match.length() - 1).substring(1) // remove square brackets
    valueString = valueString.replace('"','') // remove double quotes
    log.warn("EC SCRIPT - ==> extracValueFromJson - End")    
    return valueString.tokenize(',')
}

def getIssueKeys(connectFieldValue, issueManager){
    log.warn("EC SCRIPT - ==> getIssueKeys - Start")    
    String issueKeys = ""
    connectFieldValue.each{ value ->
        if(value.toString().contains('-') && value.toString().tokenize('-')[1].isNumber()){
            log.warn("EC SCRIPT - ===> "+value+" is an issue-key => we don't do anything")
        }
        else if(value.toString().isNumber()){
            log.warn("EC SCRIPT - ===> "+value+' is an issue-id => we convert the issue-id to issue-key')
            MutableIssue selectedIssue = issueManager.getIssueObject(value.toLong())

            if(selectedIssue){
                if(issueKeys.length() <= 0){
                    issueKeys = '"'+selectedIssue.getKey()+'"'
                } else {
                    issueKeys += ',"'+selectedIssue.getKey()+'"'
                }	
            } else {
                log.warn("EC SCRIPT - ===> The issue '"+value+"' has not been found => we don't do anything")
            }
        }
        else {
            log.warn("EC SCRIPT - ===> '"+value+"' is not an issue-id or an issue key => we don't do anything")
        }    
    }
    log.warn("EC SCRIPT - ==> getIssueKeys - End")
    return issueKeys
}

Please replace:

  • A_VALID_JQL_QUERY with a JQL query returning all the issues to be updated

  • customfield_XXXXX and customfield_YYYYY with the Connect fields ID

You can update multiple Connect fields at the same time, but be aware that this script may cause slowdowns depending on the number of Connect fields being updated and the number of issues returned by the JQL query.

This script may take a long time to run based on the scope of affected issues. We thus recommend you to schedule this maintenance operation during non-business hours in order to avoid performance issues

C. Edit the Connect field with a permanent configuration

Once the key has been updated in all affected issues, you can replace your temporary query with a permanent one.

Different cases are possibles:

Case 01 - SQL field:
  • Assuming your initial Connect field query (storing Issue IDs) was the following:
    SELECT id, summary FROM jiraissue
  • The new Connect field query (storing Issue keys) should be:
    SELECT CONCAT(p.pkey,'-',ji.issuenum), ji.summary 
FROM jiraissue ji
INNER JOIN project p ON p.id = ji.project
Case 02 - JQL field:
  • Assuming your field was connected to the "Jira issues (JQL)" and the query was:
    PROJECT = PM AND ISSUETYPE = Task
  • The new datasource should be "Jira database (SQL)" and the query should be:
    SELECT CONCAT(p.pkey,'-',ji.issuenum), ji.summary 
FROM jiraissue ji
INNER JOIN project p ON p.id = ji.project
INNER JOIN issuetype it ON it.id = ji.issuetype
WHERE p.pkey = 'PM'
AND it.pname = 'Task'

D. Migrate with CMJ

Your Elements Connect configuration is then ready to be migrated with Configuration Manager.

Case 2 - when the field key is an Option ID

Context

Let's assume that the Connect field stores option IDs from a custom Select List (whose ID is "10300") and uses the following query:

SELECT DISTINCT cfo.id, cfo.customvalue FROM customfieldvalue cfv
INNER JOIN customfieldoption cfo ON cfv.stringvalue = cfo.id
WHERE cfv.customfield = 10300

As follows:

A. Edit the Connect field with a temporary configuration

First, you need to update the Connect field configuration and change its key from Option ID to Option Name. To do so, you need to use $currentCustomfieldValue to retrieve the old keys, as this variable contains the Option ID currently stored in Jira.

Please update your Connect field query as follows:

#if($currentCustomfieldValue.size() > 0)
  SELECT DISTINCT cfo.customvalue FROM customfieldvalue cfv
  INNER JOIN customfieldoption cfo ON cfv.stringvalue = cfo.id
  WHERE cfv.customfield = 10300
  AND cfo.id IN ($currentCustomfieldValue.stringList())
#else
  $query.abort()
#end

(info) Please replace 10300 according to your own custom field ID.

This field configuration is temporary until the script below is executed. Its purpose is to retrieve the Option Name corresponding to the returned Option ID.

B. Run a script to update the Connect field keys

You then need to run a script to update the Connect field key in all affected issues, based on the temporary Connect field query configured above.

Thus, the Option ID will be changed to the Option Name key in all affected issues:

log.warn("EC SCRIPT - Script Starts")
log.warn("EC SCRIPT --------------------------------------------------")
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.jira.event.type.EventDispatchOption
   
def pluginAccessor = ComponentAccessor.getPluginAccessor()

def customFieldManager = ComponentAccessor.getCustomFieldManager()
def issueManager = ComponentAccessor.getIssueManager()
def optionsManager = ComponentAccessor.getOptionsManager()
   
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchService = ComponentAccessor.getComponent(SearchService)
def user = ComponentAccessor.getJiraAuthenticationContext().getUser()

def query = jqlQueryParser.parseQuery("A_VALID_JQL_QUERY")   
def connectFieldsToBeUpdated = ["customfield_XXXXX","customfield_YYYYY"]
def customfieldFieldId = "customfield_10300"

def customfield = customFieldManager.getCustomFieldObject(customfieldFieldId)

log.warn("EC SCRIPT --------------------------------------------------")
def results = searchService.search(user, query, PagerFilter.getUnlimitedFilter())
results.getResults().each { myIssue ->
    log.warn("EC SCRIPT - Updating issue "+myIssue.key)
    def issueToUpdate = issueManager.getIssueObject(myIssue.key)

    connectFieldsToBeUpdated.each{connectFieldId -> 
        def connectField = customFieldManager.getCustomFieldObject(connectFieldId)
        log.warn("EC SCRIPT - connect field: "+connectFieldId)
        def connectFieldValue = myIssue.getCustomFieldValue(connectField)
        if(connectFieldValue){
            log.warn("EC SCRIPT - => "+connectFieldId+" contains the following value:"+connectFieldValue)

            connectFieldValue = extracValueFromJson(connectFieldValue)
            log.warn("EC SCRIPT - => extracted value:"+connectFieldValue)
                        
            def fieldConfig = customfield.getRelevantConfig(myIssue)     
            def optionNames = getOptionNames(connectFieldValue, fieldConfig, optionsManager)
            if(optionNames){
                log.warn("EC SCRIPT - => "+connectFieldId+" has been updated with these Option Names:"+optionNames)
                issueToUpdate.setCustomFieldValue(connectField, '{"keys":['+optionNames+']}')
            } else {
                log.warn("EC SCRIPT - => "+connectFieldId+" has not been updated")
            }
            
        } else {
            log.warn("EC SCRIPT - => "+connectFieldId+" is empty")
        }
    }
    issueManager.updateIssue(user, issueToUpdate, EventDispatchOption.DO_NOT_DISPATCH, false)
    log.warn("EC SCRIPT --------------------------------------------------")
}

log.warn("EC SCRIPT - Script End")

def extracValueFromJson(jsonValue){
    log.warn("EC SCRIPT - ==> extracValueFromJson - Start")
    def match = (jsonValue =~ /\[.*?\]/)[0] // extract value thanks to a regex
    def valueString = match.substring(0, match.length() - 1).substring(1) // remove square brackets
    valueString = valueString.replace('"','') // remove double quotes
    log.warn("EC SCRIPT - ==> extracValueFromJson - End")    
    return valueString.tokenize(',')
}

def getOptionNames(connectFieldValue, fieldConfig, optionsManager){
    log.warn("EC SCRIPT - ==> getOptionNames - Start")
    String optionNames = "" 

    connectFieldValue.each{ value ->
        def name = optionsManager.findByOptionId((Long) value.toLong())

        if(optionNames.length() <= 0){
            optionNames = '"'+name+'"'
        } else {
            optionNames += ',"'+name+'"'
        }	 
    }

    log.warn("EC SCRIPT - ==> getOptionNames - End")     
    return optionNames
}

Please replace:

  • A_VALID_JQL_QUERY with a JQL query returning all the issues to be updated

  • customfield_XXXXX and customfield_YYYYY with the Connect fields ID

  • customfield_10300 with the custom Select List ID

You can update multiple Connect fields at the same time, but be aware that this script may cause slowdowns depending on the number of Connect fields being updated and the number of issues returned by the JQL query.

This script may take a long time to run based on the scope of affected issues. We thus recommend you to schedule this maintenance operation during non-business hours in order to avoid performance issues

C. Edit the Connect field with a permanent configuration

Once the key has been updated in all affected issues, you can replace your temporary query with a permanent one:

SELECT DISTINCT cfo.customvalue FROM customfieldvalue cfv
INNER JOIN customfieldoption cfo ON cfv.stringvalue = cfo.id
WHERE cfv.customfield = 10300

(info) Please replace 10300 according to your own custom field ID.

D. Migrate with CMJ

Your Elements Connect configuration is then ready to be migrated with Configuration Manager.