Querying "sc_cat_item" table

madhusudanshett · ‎02-16-2016

Hello SNC

I am querying "sc_cat_item" table to display the items on a page based on Category sys_id.

<g:evaluate jelly="true" >

var grCatItem = new GlideRecord('sc_cat_item');

grCatItem.addQuery('active', true);

grCatItem.addQuery('category', grSC.sys_id);

grCatItem.orderBy('order');

grCatItem.query();

grCatItem;

</g:evaluate>

It checks only for the main Category on the form.

i.e.

It doesn't check for the categories in the View/Related List

If item is listed under many categories then one category will show on the form rest on the related list/view.

So I want to query the related list/view to check whether category sys_id matches with any category in the related list/view.

Please help me to complete this requirement.

Thanks in Advance.

Brian Dailey1 · ‎02-16-2016

Hi Madhusudan,

I think Alexander was on the right track using the many-to-many table... but he has the logic backwards for your purpose.

Try this instead for your addQuery() to include a search for the m2m related items as well (I've made bold my changes):

<g:evaluate jelly="true" >

//Gather all those from the Related Items list

var grRelated = new GlideRecord('sc_cat_item_category');

grRelated.addQuery('sc_category', grSC.sys_id);

grRelated.query();

var relatedItems = '';

var sep = '';

while(grRelated.next()){

relatedItems += sep + grRelated.sc_cat_item.sys_id;

sep = ',';

}

var grCatItem = new GlideRecord('sc_cat_item');

grCatItem.addQuery('active', true);

grCatItem.addQuery('category=' + grSC.sys_id + '^ORsys_idIN' + relatedItems);

grCatItem.orderBy('order');

grCatItem.query();

grCatItem;

</g:evaluate>

See how that works for you.

Thanks,

-Brian

Brian Dailey1 · ‎02-16-2016

Sure, will check it out. Please mark this one as *Correct* if that was the solution.

Thanks,

-Brian