Skip to main content
Inspiring
July 7, 2023
Answered

Quarter & Fiscal Year Fields auto-filled from Date Field extraction....

  • July 7, 2023
  • 1 reply
  • 651 views

/*As per the subject line and, as usual, I'm over my head. :S 

I've developed the following script to auto-fill a Quarter and Fiscal Year fields based on a date (dd mmm yyyy) field : */

 

var originalDate = this.getField("Date").value;
var dateObj = util.scand("mm/dd/yyyy", originalDate);
var month = dateObj.getMonth() + 1;
var year = dateObj.getFullYear();
var Q = "";
var Y = "";
var yearQuarterMap = {
2020: { "1st": "21", "2nd": "20", "3rd": "20", "4th": "20" },
2021: { "1st": "22", "2nd": "21", "3rd": "21", "4th": "21" },
2022: { "1st": "23", "2nd": "22", "3rd": "22", "4th": "22" },
2023: { "1st": "24", "2nd": "23", "3rd": "23", "4th": "23" },
2024: { "1st": "25", "2nd": "24", "3rd": "24", "4th": "24" },
2025: { "1st": "26", "2nd": "25", "3rd": "25", "4th": "25" },
2026: { "1st": "27", "2nd": "26", "3rd": "26", "4th": "26" },
2027: { "1st": "28", "2nd": "27", "3rd": "27", "4th": "27" },
2028: { "1st": "29", "2nd": "28", "3rd": "28", "4th": "28" },
2029: { "1st": "30", "2nd": "29", "3rd": "29", "4th": "29" },
2030: { "1st": "31", "2nd": "30", "3rd": "30", "4th": "30" },
2031: { "1st": "32", "2nd": "31", "3rd": "31", "4th": "31" },
2032: { "1st": "33", "2nd": "32", "3rd": "32", "4th": "32" },
2033: { "1st": "34", "2nd": "33", "3rd": "33", "4th": "33" },
2034: { "1st": "35", "2nd": "34", "3rd": "34", "4th": "34" },
2035: { "1st": "36", "2nd": "35", "3rd": "35", "4th": "35" },
2036: { "1st": "37", "2nd": "36", "3rd": "36", "4th": "36" },
2037: { "1st": "38", "2nd": "37", "3rd": "37", "4th": "37" },
2038: { "1st": "39", "2nd": "38", "3rd": "38", "4th": "38" },
2039: { "1st": "40", "2nd": "39", "3rd": "39", "4th": "39" },
2040: { "1st": "41", "2nd": "40", "3rd": "40", "4th": "40" },
2041: { "1st": "42", "2nd": "41", "3rd": "41", "4th": "41" },
2042: { "1st": "43", "2nd": "42", "3rd": "42", "4th": "42" },
2043: { "1st": "44", "2nd": "43", "3rd": "43", "4th": "43" },
2044: { "1st": "45", "2nd": "44", "3rd": "44", "4th": "44" },
2045: { "1st": "46", "2nd": "45", "3rd": "45", "4th": "45" },
2046: { "1st": "47", "2nd": "46", "3rd": "46", "4th": "46" },
2047: { "1st": "48", "2nd": "47", "3rd": "47", "4th": "47" },
2048: { "1st": "49", "2nd": "48", "3rd": "48", "4th": "48" },
2049: { "1st": "50", "2nd": "49", "3rd": "49", "4th": "49" },
2050: { "1st": "51", "2nd": "50", "3rd": "50", "4th": "50" },
2051: { "1st": "52", "2nd": "51", "3rd": "51", "4th": "51" }
};
switch (true) {
case (month >= 1 && month <= 3):
Q = "2nd";
break;
case (month >= 4 && month <= 6):
Q = "3rd";
break;
case (month >= 7 && month <= 9):
Q = "4th";
break;
case (month >= 10 && month <= 12):
Q = "1st";
break;
default:
Q = "";
}
if (year in yearQuarterMap && Q in yearQuarterMap[year]) {
Y = yearQuarterMap[year][Q];
}

this.getField("Quarter").value = Q;
this.getField("Fiscal_Year").value = Y;

/* While it works precisely how I need it to, there are two issues I respectfully request assistance with"

1) I was only able to account for the variable of a new fiscal year within a calendar year by itemizing each year -- thereby limiting the total number of possible years to those itemized (currently stopping at Q1FY52). Is it possible to allow the FY continue infinitely without the itemizing of years and

2) Can this be optimized in any way to minimize the sheer volume of lines?

My sincerest thanks in advance and I'll keep my fingers crossed someone has an answer.*/

This topic has been closed for replies.
Correct answer Bernd Alheit

When Q is "1st" add 1 to year.

For Y take the last 2 digits of the year.

1 reply

Bernd Alheit
Community Expert
Community Expert
July 8, 2023

You can determine Y from year without the array.

Inspiring
July 8, 2023

I initially attempted that but was unable to ensure 1st Quarter (October - December) of a fiscal year still within a calendar year was displayed as the next Fiscal_Year for the calendar year it was in..
Fiscal years are from 01OCT - 30 SEP broken into 3 month increments -- i.e. Q1 (OCT-DEC), Q2 (JAN-MAR), Q3 (APR-JUN), and Q4 (JUL-SEP).  The array has been the only way I've been able to make it work..

Bernd Alheit
Community Expert
Bernd AlheitCommunity ExpertCorrect answer
Community Expert
July 8, 2023

When Q is "1st" add 1 to year.

For Y take the last 2 digits of the year.