Tricky Reference Qualifier on Catalog Item Variable

[jmiskey]

We have three Custom Tables for a project that look like this:

Project Table:

Enviornment Table:

Role Table:

So, on the Catalog Item, they need to fill out three Reference fields (variables) based on these tables:

- Project selection

- Environment selection

- Role selection

The Role selection field has a Reference Qualifier, to only show active records where the Project field matches the Project selection and the Environment field matches the Environment selection.  We have this working the way that we want.  Here is the tricky part, they would like to add a Reference Qualifier to filter the Environment selection based on the other two tables.

For example, if they chose Project Name "AAA", we can look at the Role table, and see that there are 3 active records that have a value of "AAA" for Project.  These represent two environments: PRD (for two of the Roles) and TST.  So since PRD and TST are the only Environments for active records in the Role table that have a value of "AAA" for the Project, the Environments selection should be filtered to only allow them to choose from PRD and TST.

How would we write the Reference Qualifier (probably needing a Script Include) to do that? 

By the way, the Project and Environment fields in the Role table are References to the other tables.

(Note: I thought originally of adding a check box for each Project type to the Environment table, and having them maintain that, but that isn't feasible, as new Projects may be added in the future, and we do not want to just keep adding new check box fields every time a new Project is added!).

Thanks

[Manmaya Kumar S]

Okay, here's how we can do it:

- We write a script include function to be used in the reference qualifier on the Env table and we then pass the selected Project value as a parameter.
- Then we check if its empty or having a value; Empty scenario is straightforward, we return all the active ones.

- However for the non-empty scenarios, we do a query against the Role table in stead of Env table, using the selected project in the query.
- From there, all the matching rows, we extract the sysIDs of the associated environment records and combine them in an array and send back to the portal form like how we generally do.
        like sys_idIN+sysIds.join()

This was we should be able to achieve this scenario.
Try this out and if you can't get it let me know I will try to help you with the designing of the queries.

Thanks!


 

[jmiskey]

Sorry, I should have been clearer.  I understand the concept, it is actually coming up with the Script Include that I need the help with (it is easy to just state it in English, much harder to write in actuality).  The tricky part is that the same Environment can appear on many active records in the Role table (like the first two lines I show in my table example).  However, there could be a LOT more matches, i.e. over 10 of the same environment). 

While I know it should, in theory, work, regardless of how many times you pull the same Environment sys_id back, I think it would be better if we were somehow able to weed out all duplicates, so it does not duplicate any sys_ids in the Javascript code it returns.

So I am really looking for specific help in writing the Script Includes for this, not general theortetical help.

[jmiskey]

OK, I think I almost got it!  I actually having it working, it just isn't weeding out the duplicates like I intended.

First, here is the function in the Script Include I am calling:

getEnvironmentFilter:function(){
        //get key variables from request form
        var proj = current.variables.project_name;

		//instatiate array variable
		var myEnv = [];

		//glide record on Workspace Access table torecords matching project name (and are active and requestable)
		var gr = new GlideRecord('x_ebcbs_databricks_workspace_access');
		gr.addQuery('active','true');
		//gr.addQuery('requestable','true');
		gr.addQuery('project',proj);
		gr.query();

		//loop through results of query and add environment record to array
		while(gr.next()){
			myEnv.push(gr.environment.sys_id);
		}
		gs.log('Envs: ' + myEnv,"JOE1");

		//remove duplicates from array
		var uniqueEnv = myEnv.filter(function(item, index) {
			return myEnv.indexOf(item) === index;
		});

		gs.log('Unique ENVs:' + uniqueEnv,"JOE2");

		//return query
		var crit = "sys_idIN" + uniqueEnv.join();
		return crit;

    },

Here is the section that is not weeding out my duplicates:

		//remove duplicates from array
		var uniqueEnv = myEnv.filter(function(item, index) {
			return myEnv.indexOf(item) === index;
		});

I stole that code from this link here: https://www.servicenow.com/community/developer-articles/play-with-arrays-in-servicenow-javascript-so....

Here is how I know it is not working.  As you can see from the code, I added logging statements to return the records before and after my filter to eliminate the duplicates.  Here are the results of that logging:

You can clearly see it is not removing the duplicate.

Does anyone have any idea why, or know of another way I can eliminate duplicates in this Script Include function?  I wonder if maybe it doesn't like the "return" in the middle of the code block, since that is what you use to return the value of the Script Include function.

[Manmaya Kumar S]

Okay,
@jmiskey , you have got it alright and just need to weed out the duplicates, here's a better way to do it, every time you push the sys_id in the myEnv array just add an if condition which will check if the sys_id is already included or not, like:

var gr = new GlideRecord('x_ebcbs_databricks_workspace_access');
		gr.addQuery('active','true');
		//gr.addQuery('requestable','true');
		gr.addQuery('project',proj);
		gr.query();

		//loop through results of query and add environment record to array
		while(gr.next()){
                       if(myEnv.indexOf(gr.environment.sys_id) == -1)// here's what you need to add
			myEnv.push(gr.environment.sys_id);
		}

This way it would eliminate the need of removing the duplicates later on.
Or we can still use the array.filter method and we can even let it be returned with the duplicates, its not going to show them twice in the form.


Try this out and do let me know,

Thanks!