business rule that will count how many requests for the same catalog item any user has raised

[LukaszW]

Hello,

I need to create a business rule that will count how many requests for the same catalog item any user has raised and display that as a message on a RITM form. I've come up with something like this:

var aggrequest = new GlideAggregate('sc_req_item');

        aggrequest.addAggregate('COUNT', 'sc_cat_item');

        aggrequest.addQuery("sc_cat_item", "=", current.sc_cat_item);

        aggrequest.addQuery("requested_for", "=", current.requested_for);

        aggrequest.query();

        while (aggrequest.next()) {

            var msg = current.requested_for.name + " has raised " + aggrequest.getAggregate('COUNT', 'sc_cat_item') + " identical requests";

            gs.addInfoMessage(msg);

        }

    (current, previous);

But while it gets the user correctly, it keeps stating that he has raised 11 RITMs like the one that is opened, which is not true (the answer should be 7, even total number of RITMs is 12 not 11). I know I must be doing something wrong but I just can't figure it out 😞 

[Sandeep Rajput · Accepted solution]

@LukaszW Have you created this BR on sc_req_item table? If yes then there is no such column as sc_cat_item on it. The column which contains catalog item reference is cat_item. Please update your script as follows and see if it works.

var aggrequest = new GlideAggregate('sc_req_item');
        aggrequest.addAggregate('COUNT', 'cat_item');
        aggrequest.addQuery("cat_item", "=", current.cat_item);
        aggrequest.addQuery("requested_for", "=", current.requested_for);
        aggrequest.query();
        while (aggrequest.next()) {
           
            var msg = current.requested_for.name + " has raised " + aggrequest.getAggregate('COUNT', 'cat_item') + " identical requests";
            gs.addInfoMessage(msg);
        }

 Hope this helps.

[LukaszW]

YES! Now it's working, thank you very much. I was pretty sure that I also tried to do this that way but seems like I didn't 🙂 Thank you 🙂 

[OlaN]

Hi,

Instead of showing this as a message on the screen with the number, it sound like it could better serve as a related list to show.

Here's how you can do.

Create a new relationship (navigate to System Definition > Relationships)

Fill out a name (i.e. Same requested item by same user)

Applies to table: sc_req_item

Queries from table: sc_req_item

Script:

	current.addQuery('requested_for', parent.getValue('requested_for'));
	current.addQuery('cat_item', parent.getValue('cat_item'));

Submit the new relationship record.

Configure the related list on sc_req_item, and add the new relationship created (search for the name you gave it).

Done!

[OlaN]

And if you want to filter out the current record from the related list, add this to the scripted query.

	current.addQuery('sys_id', '!=', parent.getUniqueValue());