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 · Accepted solution]

Okay I think your best option is to just the the oob choice list type field and use dependencies... or restructure your table and consolidate from have 3 tables down to 1. all you need is a parent field that is a reference to the same table as itself. These I think would be the best options.

...But if you are just experimenting and want to know how to do it this way.....

RelatedSubcategories:function(category_sysid){
var sys_list='';


var m2m = new GlideRecord('x_xxx_store_comm_m2m_subcategories_categories');

m2m.addEncodedQuery('subcategories.active=true^categories='+category_sysid);

m2m.query()

while(m2m.next()){

sys_list+=','+m2m.getValue('subcategories');

   }

return 'sys_idIN' + sys_list.substring(1);

}

[thomaskennedy]

This app is in prod now, and experience has shown that the business revises (add, remove, rename) these keywords every couple of months. (We were not involved in requirements gathering or development -- we would certainly have asked about that and planned for it.) So we're regularly receiving change requests just to update keywords. If there's a simple, safe way to allow the business users to modify dependent-field keywords we could add a module for that. (That sounds like a bad idea - what do you think?) Hence my thought to replace these literal keywords with references that can be renamed, inactivated, filtered, reused across multiple categories and so on, and exposed in modules so the business can update as they please.

If I understand you, that .active call on line 07 is reaching into the Subcategory table and returning true if the active field on the matching row is true, while the next clause is checking the categories field (element) on the current, m2m row. Is that what it's doing? That's pretty cool, but it's still in the reference qualifier and won't filter the Subcats on load.

I'm not sure I follow your meaning with consolidating the tables into one and using a parent field. It sounds like there would be two fields:

String Name|name

Reference Parent|parent

with parent being not required. Anything with a parent would be considered a Subcategory. Anything without a parent would be a Category. Is this what you meant?

[Nate23]

right something like this

I am not sure where you are putting this script... but I would make it a script include and reference that in your advanced ref qual... and on your dictionary for subcategories look up select box add an attribute "ref_qual_elements=name_of_category_field"

[thomaskennedy]

Well I have been using ref_qual_elements, on subcategory.

The problem is, this only evaluates when category changes. It won't clear out an inactive subcat in an existing document on load. That's not a big problem but I would like to understand how to get around it.

I will try your suggestion of a single table. Perhaps if I use two dynamic qualifiers, one for cat and one for subcat, I can get the references to filter on load, and when category changes. I've haven't found hard info on exactly when these qualifiers run.

Qualifier 1, for category, would be something like 'parent=null^active=true'

Qualifier 2, for subcategory, would be something like 'parent!=null^parent=' + @category_sys_id + '^active=true'

If that works it will be a matter of making a one-table keyword system comprehensible to the users. That's critical, as the point of the exercise to hand keyword management over to the business.