Restrict reference selection options based on another field.

[RCA]

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.

[Sandeep Rajput]

@RCA For the second field, you need to write a script include based reference qualifier. This reference qualifier will take first variable as input and will return the sys_id of those records which are no more than 4 years out from the current year.

To know more about advanced qualifier please refer to this post https://www.servicenow.com/community/developer-articles/reference-qualifiers-in-servicenow/ta-p/2765... and check the Advanced qualifier section.

Hope this helps.

[RCA]

Thanks Sandeep.  So there is no need to create an onChange script on the first variable, that was the initial approach i was taking.  As i mentioned, i am a novice with scripting, including script includes however I will take a look at link you provided. 

[Community Alums · Accepted solution]

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

[RCA]

Thanks Sai for the assistance!!! huge Help. For clarity in my particular case, the below is the code provided by Sai but modified to fit my variables.

Variable 1 reference field : select_fiscal_year  .Points to PPM OOTB table fiscal_period with simple condition Fiscal Type is Year and Open is True

Variable 2 field: start_fiscal_period_cap  .Points to PPM OOTB table fiscal_period with Advanced Use reference qualifier.

Script: 

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

Script inlcude:

Name: FiscalAjax

var FiscalAjax = Class.create();
FiscalAjax.prototype = {
    initialize: function() {

    },
    getFiscalPeriods: function(currentYear) {
		gs.addErrorMessage(currentYear);

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

        while (gr.next()) {
            var year = parseInt(gr.name.toString().replace('FY', '').trim());
            if ((year >= currYear) && (year < parseInt(currYear + 5))) {  // set to 5 to include current year plus 4 out years.
                fiscalPeriods.push(gr.sys_id.toString());
            }
        }
        return fiscalPeriods;
    },

Thanks again to @Community Alums  for the assistance on this.