When certain conditions are met, the data from these reports may not be correct.

[rutik_Wadhawane]

We are reaching out to you to inform you of an issue that impacts instances on Now Platform Vancouver Patch 3 Hot Fix 1 and above that use the "Related List Condition" in reports. When certain conditions are met, the data from these reports may not be correct because the “OR” logical operator is acting as an “AND” operator. 
Our records indicate your noted instances have reports defined with a "Related List Condition," and are already on an affected version, or can be impacted upon upgrade to an affected version.
For instances on Vancouver Patch 3 Hot fix 1 or above that have reports using "Related List Condition," when certain conditions are met the "OR" logical operator acts as an "AND" operator, which results in incorrect data being returned.

1. This issue is not apparent in the logs because no errors are shown.

also view the article for Reference KB1629327 

[Mark Manders]

What is your question? 
Did you receive this email? Are you just trying to get posts on the community without providing any useful information? Any instance affected will have received this notification, so posting it as question (without a question) doesn't really make sense. In fact: that email contains the solution to this issue (or at least work around), which you don't even mention (and the permanent solution will be updating to Vancouver patch 9 or Washington patch 2):

Description

ServiceNow has identified a defect introduced in Vancouver Patch 3 Hot Fix 1 and above.

Reports using the "Related List condition" (RLQuery) will not display the data correctly if they meet one or more of the below conditions.

• "OR" conditions with multiple BETWEEN operators,
• "OR" conditions on multiple glide_list fields,
• "NQ" operator

Because the "OR" logical operator in a related list condition query behaves as an "AND" operator.

No errors are seen for this issue in the logs.

Note: In this Knowledge Article, in the Attachments section, there is a script to identify the reports on your instances that may be affected ("PRB1717895 customer script.txt"). 

Steps to Reproduce

Create a report on the incident table with "related list conditions" like below

The data returned is incorrect.

Workaround

Background

Currently a RLQUERY with the following filter conditions will return an incorrect result set:

NQ (New Criteria)

var gr = new GlideRecord('incident');
gr.addEncodedQuery('RLQUERYtask_ci.task,>=1,m2m^ci_item=0c43cd95c61122750182c13263adfa76^NQapplied=true^ENDRLQUERY');

BETWEEN condition with OR

var gr = new GlideRecord('change_request');
gr.addEncodedQuery('RLQUERYsysapproval_approver.sysapproval,>=1,m2m^sys_created_onONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^ORsys_created_onONLast year@javascript:gs.beginningOfLastYear()@javascript:gs.endOfLastYear()^ENDRLQUERY');

OR condition on glide_list field

Assuming that group.type field is a glide_list field

var gr = new GlideRecord('sys_user');
gr.addEncodedQuery('u_license_group=aba5e1f4db80f78083513a2b7c9619f2^RLQUERYsys_user_grmember.user,>=1,m2m^group.typeLIKEf97d2680db42e34000197d668c961972^ORgroup.typeLIKEbebfafa7dbc33304859da8061b961953^ENDRLQUERY');

These cases are all OR conditions in one form or another. The issue is that the conditions are treated as AND instead of OR. To workaround this for now, we can break the filter conditions up into separate queries and combine the results of those queries in an equivalent Script Include and use as a custom report filter.

 

Applying the Workaround

Let’s assume the report is as follows:

Source Type: Table

Table: Incident

Related List Conditions:

Greater than or Equal to 1, task_ci → task

ci_item is 0c43cd95c61122750182c13263adfa76 NQ

applied is true

written as an encoded query that is:

“RLQUERYtask_ci.task,>=1,m2m^ci_item=0c43cd95c61122750182c13263adfa76^NQapplied=true^ENDRLQUERY”

Create a Script Include as follows:

Name: CustomReportingFilters

Client Callable: True

Active: True

Script:

var CustomReportingFilters = Class.create();

CustomReportingFilters.prototype = {

	getRecordsWithRelatedRecords: function() {
		var relatedRecords = [];
		var gr = new GlideRecord('incident');
		gr.initialize();
		gr.addEncodedQuery("RLQUERYtask_ci.task,>=1,m2m^ci_item=0c43cd95c61122750182c13263adfa76^ENDRLQUERY");
		gr.query();
		while (gr.next()) {
			relatedRecords.push(gr.getUniqueValue());
		}

		gr.initialize();
		gr.addEncodedQuery("RLQUERYtask_ci.task,>=1,m2m^applied=true^ENDRLQUERY");
		gr.query();
		while (gr.next()) {
			relatedRecords.push(gr.getUniqueValue());
		}

	return new ArrayUtil().unique(relatedRecords);
},

type: 'CustomReportingFilters'

};

This Script include will return the same sys_id’s for the incident's that match the related list query condition defined in the report.

Now, create a report as follows:

Source Type: Table

Table: Incident

Conditions: [sys_id] [is] [javascript:new CustomReportingFilters().getRecordsWithRelatedRecords()]

Run the report. This should return the expected Incident records.

This workaround can be applied to the other cases as well. Keep in mind that this workaround is a general example of how to create a custom report filter with a script include; The example should be modified to fit the customer's situation.

FAQ:

Q: Can I apply the workaround before upgrading to an impacted version?
A: Yes. It can be applied before and after.

Related Problem: PRB1717895