Restrict reference selection options based on another field.

RCA · ‎06-04-2024

I am a novice when it comes to scripting, and using script includes advanced or dynamic filtering, so any guidance is appreciated. The below scenario I am attempting in my personal development instance.

I have a service catalog item with a reference field on my form called select_fiscal_year that points to the fiscal_period table using a simple filter of -fiscal type is year and open is true. A user is able to select one fiscal year.

I have another reference field called start_fiscal_period_cap that allows the user to select fiscal years referenced from the same table(fiscal_period) using the same filter above(fiscal type is year and open is true. )however i want to restrict the user to only select options no more than 4 years out from the choice selected in the select_fiscal_year reference field including the year selected.

Ex 1: User selects FY 26 in the field select_fiscal_year.

In the field start_fiscal_period_cap, user should only see options. FY 26, FY 27, FY 28, FY 29 FY 30. Year select and no more than 4 years out.

Ex 2: User selects FY24 in the field select_fiscal_year.

In the field start_fiscal_period_cap, they should only see options. FY 24, FY 25, FY 26, FY 27 FY 28.

All help and guidance is appreciated.

Community Alums · ‎06-05-2024

Hi @RCA ,

- Create a script include with below code

var FiscalAjax = Class.create();
FiscalAjax.prototype = {
    initialize: function() {},
    getFiscalPeriods: function(currentYear) {

        var currYear = parseInt(currentYear.toString().replace('FY ', '').trim());
        var fiscalPeriods = [];
        var gr = new GlideRecord('fiscal_period');
        gr.addEncodedQuery('Add your Query');// fiscal type is year & open is true
        gr.query();

        while (gr.next()) {
            var year = parseInt(gr.u_f_year.toString().replace('FY ', '').trim());
            if ((year >= currYear) && (year < parseInt(currYear + 4))) {
                fiscalPeriods.push(gr.sys_id.toString());
            }
        }
        return fiscalPeriods;
    },
    type: 'FiscalAjax'
};

- In your second variable (start_fiscal_period_cap), add below in reference qualifier

javascript&colon; 'sys_idIN'+new FiscalAjax().getFiscalPeriods(current.variables.select_fiscal_year.getDisplayValue());

Result:

Use the above code to achieve your requirement. Tested in PDI

If my answer helped you in any way, please mark it as helpful or correct.

View solution in original post

RCA · ‎06-06-2024

Note: you can remove the line below, as it was used for troubleshooting

gs.addErrorMessage(currentYear);