Quarter & Fiscal Year Fields auto-filled from Date Field extraction....
/*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.*/
