Power Scripts: Set date value (field A) depending on another date value (field B)

[Chris K]

Dear community,

Welcome to SIL for starters! This episode: "Instead of crying in despair of my own inability, I'm asking the community for help" :)

I'm currently trying to write a SIL script for Jira Power Scripts, which sets the value of a custom field A depending on the value of a custom field B in a defined Jira project. Both fields are date fields. The script must include the following if-statements:

If the value of field A is in the first quarter of a year, then field B should have the value 30.06 of the respective year.
If the value of field A is in the second quarter of a year, then field B should have the value 15.08 of the respective year.
If the value of field A is in the third quarter of a year, then field B should have the value 01.11 of the respective year.
If the value of field A is in the fourth quarter of a year, then field B should have the value 15.12 of the respective year.
I am looking for a formula that outputs the quarter of the respective year, since I don't want to hard code the dates of the current year in the script, which would only provide a short-term solution. 

Do you have any ideas how to solve this?

Greetings Chris

[Anna Hryhoruk _Appfire_ · Accepted solution]

Hello @Chris K 

First of all,I was looking for some formulas to calculate quarters and found this javascript approach Get Quarter from a Date [Fiscal + Calendar] | Excel Formula

Normally, when we try to configure some field value dependencies in SIL, we use Live Fields. I am not sure if you used that in the past, so will share some Live Fields documentation here as well: Live Fields Configuring Live Fields . So we will have two scripts: hook and main one.

Let`s start from hook define quearter.sil 

date d= argv["customfield_18000"]; //replace with custom field id for "A" field 
number cnst =3; // formula for quarter calculation ROUNDUP ( MONTH / 3)
number mnth=month(d); //parsing date value and getting month number 
number temp= (mnth/cnst);
number quarter=ceiling(temp, 1);
runnerLog(quarter);
//lfGlobalMessage("here:" +quarter); //just for visial testing
if (quarter==1){
lfSet("B", "30/Jun/23");

} else if (quarter==2){
lfSet("B", "15/Aug/23");

} else if (quarter==3){
lfSet("B", "01/Nov/23");

} else if (quarter==4){
lfSet("B", "15/Dec/23");
}

If I write it down as single formula I have issues with defining the quarter for January and February, that quarter is calculated as 0 instead of 1. So, I worked around it this way (but you may try to simplify it yourself, maybe I missed something) .

But to make this script react dynamically to A field value changes you need one more script:

main define quarter.sil

lfInstantHook({"customfield_18000", "customfield_18001"} , "define quarter.sil" );
lfWatch("customfield_18000",{"customfield_18000", "customfield_18001"}, "define quarter.sil", {"change"});

where 18000 custom field id for field A and 18001custom field id for field B, so replace it with your ones. Also, make sure you refer to the above hook script with the correct name. This short script need to be added to the Live Fields Configuration as described here Configuring Live Fields

Some documentation links that you may need: lfSet lfWatch lfInstantHook

Hope it helps!
Anna

[Chris K]

Hi @Anna Hryhoruk _Appfire_

Thanks for providing such a detailed solution! As we just tested together, it works perfectly. Thank you for your help and the time! :)

Greetings

Chris

[Anna Hryhoruk _Appfire_]

 @Chris K  My pleasure to help! :) 

[Andrew Laden]

You will have to get comfortable with https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15489112/Date+and+Interval+Routines

You can use https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15480763/year to get the year of the current date.

You can then construct the date "values" (aka the numeric value of the date) for

• Start of Each quarter
• The "target" date that you want set. (30.06, 15.08, 01.11, 15.12)

using https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15481340/toDate

Then its just a nested if or a case statement to compare the current date to the start of quarter dates, setting the target date.

Shouldn't be too hard.

You may want to consider if you want to use automation to set the custom field upon edit of the source field, vs using power custom fields to calculate the field value automatically. There might be performance impacts if you use power custom field, since you are doing a few calculations and they will get done more often.