Querying "sc_cat_item" table

[madhusudanshett]

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); <!-- grSC.sys_id is category sys_id from previous query. 'category' is the field on the form -->

<!-- Condition required here -->

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.

[ahaz86]

try something like this Replace <CATALOG ITEM SYSID> with your catalog item sysid though

<g:evaluate jelly="true" >

var grCategories = new GlideRecord('sc_cat_item_category');

gr.addQuery('sc_cat_item', <CATALOG ITEM SYSID>);

gr.query();

var catSysIds = '';

while(grCategories.next()){

        if(catSysIds == ''){

                  catSysIds = grCategories.sc_category.sys_id;

        }else{

                  catSysIds += ',' + grCategories.sc_category.sys_id;

        }

}

var grCatItem = new GlideRecord('sc_cat_item');

grCatItem.addQuery('active', true);

grCatItem.addEncodedQuery('sc_cat_item.sys_idIN'+catSysIds );

grCatItem.orderBy('order');

grCatItem.query();

grCatItem;

</g:evaluate>

[madhusudanshett]

Hi Alexander

Thank you for the reply.

Let me you explain you about the following code.

First I am picking the catalog sys_id then using that ID I am querying sc_category table for parent categories.( Category whose parent field is empty)

Then again I am querying sc_category table to find the sub-categories of the parent category.

After that I am querying "sc_cat_item" table to find the items which has the parent category sys_id. Here I need to check the related list/view as well.

<-- Checking Catalog ID -->

<g:evaluate var="jvar_sc" jelly='true' object="true">

var sc_catalog = new GlideRecord('sc_catalog');

sc_catalog.get(jelly.RP.getParameterValue('sysparm_catalog_id1')); <!-- Catalog sys_ID-->

sc_catalog;

</g:evaluate>

<Finding Parent Category -->

<g:evaluate var="jvar_grSC" jelly="true" object='true'>

var grSC = new GlideRecord('sc_category');

grSC.addQuery('active',true);

grSC.addQuery('sc_catalog', jelly.RP.getParameterValue('sysparm_catalog_id1'));

grSC.addNullQuery('parent');

grSC.orderBy('order');

grSC.query();

grSC;

</g:evaluate>

<j:while test="${grSC.next()}">

<!-- Find subcategories -->

<g:evaluate jelly='true' object="true">

var child = new GlideRecord('sc_category');

child.addQuery('active', true);

child.addQuery('parent', grSC.sys_id);

child.orderBy('order');

child.query();

child;

</g:evaluate>

<!-- Get and set the subcategory count -->

<j:set var="jvar_subcat_row_count" value="${child.getRowCount()}" />

<g:evaluate jelly="true" >

var grCatItem = new GlideRecord('sc_cat_item');

grCatItem.addQuery('active', true);

grCatItem.addQuery('category', grSC.sys_id); <!-- grSC.sys_id is category sys_id from previous query(parent).   'category' is the field on the form -->

<!-- Condition required here -->

grCatItem.orderBy('order');

grCatItem.query();

grCatItem;

</g:evaluate>

Since it is dynamic I don't have a particular sys_id of Catalog or Category or Item.

Please tell me how I can use the code which is provided by you for "sc_cat_item".

Thank you

[Brian Dailey1 · Accepted solution]

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);

<!-- Condition required here -->

grCatItem.orderBy('order');

grCatItem.query();

grCatItem;

</g:evaluate>

See how that works for you.

Thanks,

-Brian

[madhusudanshett]

Hi Brian

It worked.

Thank you very much for your help.

Could you please help me with this post as well.Roles Comparison in Table "sys_app_module"

Thank you.