Request task report using dynamic filter

[sony8]

Hi all ,

I am working on one custom application.

here there is a request table and a request task table.

first we create request and using related section we create task for request table.

i need to build a filter on request and I need to include some fields related to task table.

on my request I don’t have task related field but on my task we have request id enable.

i need to pull some records which are dependent on request and task.

my first condition is as follow

request status work in progress

counter > 1

my other condition is as follow 

on the request task there is one flag field it should be fals.

i have created a dynamic filter on request as below 

sys_id is one of javascript:myscriptinclude().functionName();

on my script include :

functionName: function(){

var query=‘state=2^counter>0’;

return this.getRecords(‘bgc_req’,query).join(,);

};

functionTask: function(sys_id_req){
var query1=‘requesttask_flag=false’;

return this.getRecords(‘bgc_req_task’,query1).join(,);

};

my expectation is

request status is work in progress AND 

counter >0 AND 

Request task flag =false

return count

now I am not understanding how to query all in one go . Please help me

[Akash4 · Accepted solution]

Hi Sony,

1. To achieve this we may try to query both tables and join them through the sys_id (from request) and request_id (from request task). Later, can filter the request records based on the conditions from both tables

2. We can do needful via Script Include and refer it in the Dynamic filter like sys_id IN javascript:MyScriptInclude().functionName();

3. In the SI > try processing like Request status = Work in Progress with Counter > 0 & at least one related task has flag = false.

Hints: query Request table first with state=<WIP>^counter>0 > push results to an Array. Secondly, query Request Task table by using a field say “Flag=false” for any tasks with query of request_id=req_sys_id > return true if any task has this Flag as Flase.

Happy learning!