How do I filter a Dictionary Entry to active only, if there is already an Advanced qualifier?

[thomaskennedy]

On my Communications form I have two References, Category and Subcategory, whose values are related in a many-to-many table. I want to filter out all rows, on both fields, where active=false. So that means:

1. Show only active in Category DONE

2. Show only active in Subcategory TODO

3. Show only active, related subcategories when Category changes DONE

#1 is done, on Category, by a Simple qualifier condition of Active Is true.

#3 is done by an Advanced qualifier calling a script include:

RelatedSubcategories:function(category_sysid){
var sys_list='';
var iter = 1;
var m2m = new GlideRecord('x_xxx_store_comm_m2m_subcategories_categories');
m2m.addQuery('categories', '' + category_sysid);
var grSQ = m2m.addJoinQuery('x_xxx_store_comm_subcategories','subcategories','sys_id');
grSQ.addCondition('active',true);
m2m.query();
while(m2m.next()){
if(iter > 1) { sys_list += ','; }
sys_list += m2m.getValue('subcategories');
iter++;
}
return 'sys_idIN' + sys_list;
},

That leaves #2. I want the Dictionary Entry for Subcategory to show only rows where Active=true, just as Category does. However, existing records will show subcategories that have since been marked inactive. And, with the reference qualifier already in use, filtering the subcats when cat changes, I don't see how I can do that.

As an aside: While the simple qualifier on Category seems to run when the reference is evaluated (that is, on query), the Advanced qualifier on subcategory seems to run only when Category changes. Is this true?

It seems to me the answer is to create a view (in the RDBMS sense) over the Subcategory table, filter out inactives, and have the Dictionary Entry pull sys_ids from that view instead of from subcategory. But I don't see anything strictly resembling an rdbms view.

I could use a Client Script to remove inactive entries, but there must be a cleaner way.

I could try a business rule, if there were a way to know that the query is being done from the Communications form, and not from some other context where the inactives are needed, for example in the keyword management module.

How do you do this?

[Nate23]

you mean a pre-existing record already has a subcat value of something that is inactive and you dont want that? or onload on a new record it load inactive?

[thomaskennedy]

Yes and yes. It would be nice to have inactive values taken out of existing records on load. I can see the business asking for that.

And yes, new records should not show inactive records. Suppose for example the Subcategory were not limited to 'none' at first, but listed all values. I would not want inactive ones included. That's easy to do with a simple qualifier, but if I switch to the advanced qualifier to react to to changes in Category, I'm left with the potential problem of what to do on load, if there are values that should no longer appear.

I'm going to try the dynamic qualifiers, and find out what 'dynamic' means.

[Nate23]

okay so this does not clear out a saved value in that field. A validation would have to be made onLoad to clear out inactive values. active/inactive process is to keep historical data in your tables instead of deleting the records and those metrics disappearing forever. hence why when you load a form with a saved value of something that "use" to be active it is still there.

You could just create a quick onload check. use GlideAjax to send sys_id of subcat to a script include then have the script include query to see if subcat is inactive something like this:

var subcatClient = this.getParameter('sysparm_subcat')

var subcat= new GlideRecord('x_xxx_store_comm_m2m_subcategories_categories');      

if(subcat.get(subcatClient )){

return subcat.active;

}

return false;

If you need more info on GlideAjax - GlideAjax - ServiceNow Wiki

In your client script you should evaluate the true/false return and do a g_form.clearValue(); on your subcat. This should take care of the inactive records appearing onload.

[Nate23]

An example of this would the inactive value happening else where is lets say a user in your system no longer works there. Their account in ServiceNow should now be inactive. However, any incident or request that they opened will still have their name tied to it even though they are inactive.

So just an extra recommendation is you may notify the user before clearing the subcat.

g_form.addInfoMessage('The last defined subcategory' + subcatname + ' has been deprecated, please pick a new subcategory');

Also this same issue is probably applicable to your category field. try making a category inactive then pulling up a pre-existing record with that category you made inactive.

[thomaskennedy]

Yes, that is probably how we would handle inactive values. I suppose there is an edge case where we would clear them out on open, but even then we would inform the user. Thank you, this is all very helpful.