Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Current »

Goal

This section shows the instructions to use the ScriptRunner together with Lookup Manager to set a custom field value when the date is between two other dates

Pre-requisites

✔️ Create a lookup table
✔️ Lookup Table Edit permission
✔️ ScriptRunner installed

Demonstration

When the value of “Custom Field X” is set to any date between “Start” and “End”, I want to set the “XIQ Target Release” custom field value to the specified value which is defined in the lookup table

E.g. if the Custom Field X value is set to “11/2/2022”, then the XIQ Target Release custom field value should be set to “XIQ 22Q1r2”

Step 1: Create a Lookup Table

Start

End

Target

10/2/2022

25/3/2022

XIQ 22 Q1r2

25/3/2022

6/5/2022

XIQ 22r3

Step 2: Use the lookup ScriptRunner function to compare the date values and update the destination custom field

1) Add a ScriptRunner Custom Listener, select the project(s) and event(s) based on your scenario

2) Paste the following code into the Inline script field. This script will check if the Custom Field X value is in between the Start and End values. If true, then the Target value will be used to set the XIQ Target Release custom field.

import com.onresolve.scriptrunner.runner.customisers.PluginModule
import com.onresolve.scriptrunner.runner.customisers.WithPlugin
import com.akelesconsulting.jira.plugins.rest.LookupService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.IssueManager;
import com.atlassian.jira.issue.MutableIssue;
import org.apache.log4j.Logger
import org.apache.log4j.Level
import groovy.json.JsonSlurper
import java.text.SimpleDateFormat
import java.time.format.DateTimeFormatter
import java.sql.Timestamp
import com.atlassian.crowd.embedded.api.User
import com.atlassian.jira.event.type.EventDispatchOption

@WithPlugin("com.akelesconsulting.jira.plugins.LookupManager")

@PluginModule
LookupService lookupService

def log = Logger.getLogger("LookupManager")
log.setLevel(Level.DEBUG)

IssueManager issueManager = ComponentAccessor.getIssueManager();
def customFieldManager = ComponentAccessor.customFieldManager
def user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()

//Please change the following:
def tableName = "Table"
def sourceCustomFieldName = "Cutom Field X"
def destinationCustomFieldName = "XIQ Target Release"

def dateFormatter = DateTimeFormatter.ofPattern('dd/MM/yyyy')

log.debug("***** Get table list *****")
def tableListText = lookupService.getTableList()
def tableListArray = new JsonSlurper().parseText( tableListText )
//tableListArray.each { log.debug it }

log.debug("***** Get table id by name *****")
int tableId = lookupService.getTableIdByName(tableName)
//log.debug("Table id: " + tableId)

log.debug("***** Get table information for table*****")
def table = tableListArray.find {table -> table.name.equals("Table")}
log.debug("Table information: " + table)

log.debug("***** Get column information for table*****")
def columns = table.columns
log.debug("Column information: " + columns)

def startColumn = columns[0]
def endColumn = columns[1]
def targeteColumn = columns[2]

def startColumnId = startColumn.id as String;
def endColumnId = endColumn.id as String;


log.debug("***** Get table entries by table id *****")
def tableEntriesText = lookupService.getTableEntriesById(tableId)
def tableEntriesArray = new JsonSlurper().parseText( tableEntriesText )
//tableEntriesArray.each { log.debug it }

MutableIssue issue = (MutableIssue) event.issue;
def sourceCustomField = customFieldManager.getCustomFieldObjectByName(sourceCustomFieldName)
def sourceCustomFieldObj = issue.getCustomFieldValue(sourceCustomField) as Timestamp

if (sourceCustomFieldObj != null) {

    log.debug("***** Start Comparision... *****")
    def customFieldDateFormatted = dateFormatter.format(sourceCustomFieldObj.toLocalDateTime());
    log.debug("Custom Field X: " + customFieldDateFormatted)

    for(entry in tableEntriesArray){
        def startDateText = entry[startColumnId]
        def startDateObj = Date.parse('dd/MM/yyyy', startDateText)
        def startDateFormatted = dateFormatter.format(startDateObj.toLocalDateTime());
        log.debug("startDateFormatted " + startDateFormatted)

        def endDateText = entry[endColumnId]
        def endDateObj = Date.parse('dd/MM/yyyy', endDateText)
        def endDateFormatted = dateFormatter.format(endDateObj.toLocalDateTime());
        log.debug("endDateFormatted " + endDateFormatted)

        if (dateBetween(startDateObj, endDateObj, sourceCustomFieldObj)) {
            //If true, retrieve the target value
            def matchingValuesArray = lookupService.lookup(tableId, endColumn.name, endDateText, targeteColumn.name)
            log.debug("Found matchingValuesArray:" + matchingValuesArray)

            def destinationValue = matchingValuesArray[0]

            def destinationCustomField = customFieldManager.getCustomFieldObjectByName(destinationCustomFieldName)

            issue.setCustomFieldValue(destinationCustomField, destinationValue)
            issueManager.updateIssue(user, issue, EventDispatchOption.DO_NOT_DISPATCH, false)

            log.debug("Updated [" + destinationCustomFieldName + "] in " + issue.key + " with value [" + destinationValue + "]")
            log.debug("***** Complete Comparision... *****")
            break;
        }
    }
}

def dateBetween(Date date1, Date date2, Date toCheck){
    return toCheck.after(date1) && toCheck.before(date2)
}

Create a new issue and verify the target value changed

demo.mp4

You can also check the execution history log 

2022-03-24 12:52:33,601 DEBUG [LookupManager]: ***** Get table list *****
2022-03-24 12:52:33,603 DEBUG [LookupManager]: ***** Get table id by name *****
2022-03-24 12:52:33,603 DEBUG [LookupManager]: ***** Get table information for table*****
2022-03-24 12:52:33,603 DEBUG [LookupManager]: Table information: [columns:[[id:5, name:Start], [id:6, name:End], [id:7, name:Target]], name:Table, id:2]
2022-03-24 12:52:33,603 DEBUG [LookupManager]: ***** Get column information for table*****
2022-03-24 12:52:33,603 DEBUG [LookupManager]: Column information: [[id:5, name:Start], [id:6, name:End], [id:7, name:Target]]
2022-03-24 12:52:33,603 DEBUG [LookupManager]: ***** Get table entries by table id *****
2022-03-24 12:52:33,605 DEBUG [LookupManager]: ***** Start Comparision... *****
2022-03-24 12:52:33,605 DEBUG [LookupManager]: Custom Field X: 24/03/2022
2022-03-24 12:52:33,605 DEBUG [LookupManager]: startDateFormatted 10/02/2022
2022-03-24 12:52:33,605 DEBUG [LookupManager]: endDateFormatted 25/03/2022
2022-03-24 12:52:33,606 DEBUG [LookupManager]: Found matchingValuesArray:[XIO 22Q1r2]
2022-03-24 12:52:33,610 DEBUG [LookupManager]: Updated [XIQ Target Release] in TTT-10 with value [XIO 22Q1r2]
2022-03-24 12:52:33,610 DEBUG [LookupManager]: ***** Complete Comparision... *****
  • No labels