Query all the project tasks associated with a demand

[sigmachiuta]

I am trying to link all my project tasks back to my demand.   This is an after update BR that queries on the project tasks.   There can be multiple projects per demand thus multiple projects/ tasks.   I am having a little trouble linking back in my queries and then using the parents in my next queries.   So for every project task I am looking at the parent of that task, the project and when i have the demand number looking back at the project in gr2.   Is there an easier way i am getting confused on how to do this.   I have tried an after insert business rule on the project task but if you apply a template it doesn't populate.

     //get demand number
       var prj = current.parent
   var arr=[];
   var arr2=[];
   var gr= new GlideRecord('pm_project');
   gr.addQuery('number',prj);
   gr.query();
   while(gr.next()){
   arr.push(gr.getValue('parent'));
// get any other projects with same demand
var gr2 = new GlideRecord('pm_project');
   gr2.addQuery(arr,current.parent)
   gr2.query();
   while(gr2.next()){
   //since there can be multiple projects per demand return all 
   //project tasks that are associated to this demand (multiple projects)
   }
   //return arr.toString();
}

[lorisd_avanzo · Accepted solution]

Hi,

It's a little bit hard/confusing by just reading some code, but if I understood well, the purpose here should just be creating an array which contains the parent (let's call it PRJ) of the current project task, then all the projects which parent is the same Demand parent (DMN) of PRJ. The Business Rule, as you said, runs on the Project Task table.

First, I think the code line number #8 is wrong, you are querying for the number field but you are actually passing a sys_id as second parameter (prj is equal to current.parent which is a reference field, so that value is actually a sys_id). I believe you should change the below code (line numbers #8-#12):

gr.addQuery('number',prj);   



gr.query();   
while(gr.next()){   

To be just:

if (gr.get(prj)){

This way you will just query directly for the sys_id of the parent project (prj), and the if condition will just check there is a result (we are expecting just 1 result from this query).

Second, I believe the query at the code line number #18 is wrong as well, your query is:

 gr2.addQuery(arr,current.parent);

If you want to get the projects which parent is equal to DMN (the parent of PRJ, as for the case described above) you should change the query to:

gr2.addQuery("parent",gr.getValue('parent'));

in the last while constructor (code lines number #20-#27 ), you would just need to push the sys_id of the current gr2 record into the array called arr:

arr.push(gr2.getValue("sys_id");

So that at the end of the script you will return the array of sys_ids for the queried records. If you want to return the numbers you can modify the script accordingly.

I hope the above response helps

In case I understood wrong the use case you want to develop, just let me know.

Regards,

Loris

[sigmachiuta]

I am still a little confused.   Here is the goal.   I am needing to run a Business Rule on the project task table that will query the project (here i might need to use top_task because the project task parent changtes if its a dependent task) and get the demand number when i have that demand number I need to return all the project tasks associated with that demand since there is multiple projects.

Then i will do some additional processing checking the tasks with a special flag and see if they are all closed and if so set a field on the demand record.

An 'easy' way i was going to do this is create a BR on the project_task table that would copy the demand number down to the project task.   An after Business Rule on insert.   But when i apply the template it does not copy the demand number it does work because i also clicked update and after i enter any data and save the record it copies the demand number to the task.

[lorisd_avanzo]

That's probably because of the meaning of a template, you use it to pre-populate the fields in a form then you would have to save the record manually. Even though the Project Template feature is a little bit more articulated than the standard one used in the other forms of the SN platform.

In any case, I believe you can bypass this by using a Display Business Rule instead of the before/after Insert/Update Business Rule.

You may also find useful the below code:

if (RP.isPopup())
         return;

This way you will avoid to run the Business Rule when you over over the info icon from the list view.

I hope it helps

Regards,

Loris D'Avanzo