Script include in reporting

[Nabilah]

Hi,

I am trying to create a script include that I will call from a report. The script include should list out all company records that have at least one request item associated to it. This is the script for the relationship I created which applies to core_company and queries from sc_req_item. It works:

(function refineQuery(current, parent) {

	var ritms = [];
	var gr = new GlideRecord('sc_req_item');
	gr.query();
	while (gr.next()) {
		var company = gr.variables.vendor_name;
		if (company == parent.getValue('sys_id')) {
			ritms.push(gr.getValue('sys_id'));
		}
	}
	current.addQuery('sys_id', ritms);
})(current, parent);

I want to convert this into a script include that will list out all company records that have at least one RITM associated to it. Can someone please help. (I have tried the related list conditions on report but this relationship does not come up to select).

[Jaspal Singh]

Hi Nabilah,

Did you try something as a Script include? If so, please share.

[Nabilah]

@Jaspal Singh 

var CompanyRequestItemUtils = Class.create();
CompanyRequestItemUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {

   
    getCompaniesWithRequestItems: function() {
        var companies = [];
        var gr = new GlideRecord('core_company');
        gr.query();
        while (gr.next()) {
            var companySysId = gr.getUniqueValue();
            var requestExists = new GlideRecord('sc_req_item');
            requestExists.addQuery('variables.vendor_name', companySysId);
            requestExists.query();
            if (requestExists.hasNext()) {
                companies.push(companySysId);
            }
        }
        return companies;
    },
   
   process: function() {
        return this.getCompaniesWithRequestItems();
    },

    type: 'CompanyRequestItemUtils'
});

[Sohail Khilji]

Hi @Nabilah ,

it will not work on this line > 

requestExists.addQuery('variables.vendor_name', companySysId);

instead of ritm table you need to do this on variables table. i.e. mtom table

i hope this helps...

[Nabilah]

Hi @Jaspal Singh @Sohail Khilji Can any of you help?