Dynamic filter of reports to exclude values

[Sharique Azim]

Hi All,

I am trying to build a report where I am looking for those values which is not available on some other table.

In my RITM table, I have a custom string field where input are given, so it could be anything, including   random string.

There are some generic templates on some other custom table , which has no relation with any of the tables records.

Now, the requirement is, i want those records which are not available on the custom table ..

Suppose,

as you see "Ghulam" (which mean slave in hindi/urdu ) cannot be a record in the other   table, so should have only   come up , while "Branch manager" could be a valid record and thus should not   be available in the report.

Script include:

also tried arr.join(',');

Now, my issue is   i am getting those records which is is existing in the custom table.

Thanks and Regards,

Shariq

[Josh Cooper]

I *THINK* I understand what you're trying to accomplish, but it's going to be a little more complicated, although I believe you're on the right path.

The issue you're going to run into is that you don't necessarily have a good way to use the results of your script include in the way that you would need to, because you're returning a list of string values and you don't want it to be any of these string values.

Also note that your current conditions are looking for any roles that aren't default, which is why Branch could be in there right now.

The best way I can think that this might work, use your script include to handle all of the filtering, and just provide the results set as a list of the records to include by sys_id.  

So as you're doing now, build the array of terms that are 'not allowed'.   Use THAT as an input to a second GlideRecord query to the table you're interested in.   Start with a resultSet variable set to empty.   For each result in the first GlideRecord query (in other words, inside the 'while' block), query the original table for any records containing that term.   For each result you get, add it to the resultSet variable (resultSet = resultSet + badTerm.sys_id + ',') where 'badTerm' is the name of the second GlideRecord query.

At the end, you should end up with resultSet equal to a comma seperated list of all the sys_ids of records with forbidden terms in them.   Then you can use that as your filter criteria like "sys_idIN" (resultSet).   So it would only show the bad ones.

I'm not 100% sure that you can use the comma separated list of sys_ids in a regular condition builder like you have, but if you cannot, then it should be easy to do as a "Before Query" business rule.   You set that on the table itself that you're querying and it uses whatever script you supply to limit the results, whenever that table is referenced.

[Sharique Azim · Accepted solution]

Hi Josh,

Indeed your approach to filter and return bad values worked.

here is the   code   for those interested

Currently,   I am trying   to optimize the code,and would update once successful. Thanks again .

[Sharique Azim]

Hi Josh,

I see there is a remarkable performance issue, do you think we can do something to optimize the code?