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
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.

CODE

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

CODE

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... *****