Document toolboxDocument toolbox

How to set a custom field value when date is between two other dates

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

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) def issueManager = ComponentAccessor.getIssueManager() def versionManager = ComponentAccessor.getVersionManager() def customFieldManager = ComponentAccessor.getCustomFieldManager() 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) //For Version field def version = versionManager.getVersion(issue.getProjectId(), destinationValue) if (version == null) { log.debug("Creating a new Unrelased version with name = [" + destinationValue + "]"); version = versionManager.createVersion(destinationValue, null, null, null, issue.getProjectId(), null, false) } log.debug("Version " + version); issue.setCustomFieldValue(destinationCustomField, [version]) //For Text field //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.getTime() >= date1.getTime() && toCheck.getTime() < date2.getTime(); }

Create a new issue and verify the target value changed

 

You can also check the execution history log

2022-03-26 21:23:12,862 DEBUG [LookupManager]: ***** Get table list ***** 2022-03-26 21:23:12,864 DEBUG [LookupManager]: ***** Get table id by name ***** 2022-03-26 21:23:12,864 DEBUG [LookupManager]: ***** Get table information for table***** 2022-03-26 21:23:12,864 DEBUG [LookupManager]: Table information: [columns:[[id:1, name:Start], [id:2, name:End], [id:3, name:Target]], name:Table, id:1] 2022-03-26 21:23:12,864 DEBUG [LookupManager]: ***** Get column information for table***** 2022-03-26 21:23:12,864 DEBUG [LookupManager]: Column information: [[id:1, name:Start], [id:2, name:End], [id:3, name:Target]] 2022-03-26 21:23:12,864 DEBUG [LookupManager]: ***** Get table entries by table id ***** 2022-03-26 21:23:12,865 DEBUG [LookupManager]: ***** Start Comparision... ***** 2022-03-26 21:23:12,865 DEBUG [LookupManager]: Custom Field X: 24/03/2022 2022-03-26 21:23:12,865 DEBUG [LookupManager]: startDateFormatted 10/02/2022 2022-03-26 21:23:12,865 DEBUG [LookupManager]: endDateFormatted 25/03/2022 2022-03-26 21:23:12,866 DEBUG [LookupManager]: Found matchingValuesArray:[XIQ 22 Q1r2] 2022-03-26 21:23:12,866 DEBUG [LookupManager]: Version XIQ 22 Q1r2 2022-03-26 21:23:12,869 DEBUG [LookupManager]: Updated [XIQ Target Release] in TTT-21 with value [XIQ 22 Q1r2] 2022-03-26 21:23:12,870 DEBUG [LookupManager]: ***** Complete Comparision... *****