projTimeCards

I was recently asked how I might show a related list of all time cards belonging to a project or any of its sub projects.

A project record has a related list for time cards by default. However, it only lists those timecards that are written against the Project directly. What if we wanted to get all time cards associated for a project and all of its sub projects or project tasks?

The following solution may be better implemented with a faster algorithm, but please give me a break, I wrote this solution during a dance recital dress rehearsal so I didn’t have a lot of time to think 🙂

The challenge here is that there can be any number of sub projects to a given project. The code I wrote here has a hard coded maximum sub-project depth of 10, but you could choose a value that makes the most sense to you.

I went to System Definition > Relationships and created a new relationship with the following:

Name: Project Timecards
Applies to table: Project
Queries from table: Time Card

Query with

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
var maxLevels = 10;
var tcids = rptGetTimeCards(parent.sys_id, "task", "", maxLevels);

if( tcids ){
  current.addEncodedQuery("sys_idIN"+tcids);
}

function rptGetTimeCards( projID, refFieldPath,  timecards, levels ){
  if( levels==0 ){
    return timecards;
  }
  var gr = new GlideRecord("time_card");
  gr.addQuery(refFieldPath, projID);
  gr.query();
  while(gr.next()){
    timecards += ","+gr.sys_id;
  }
  return rptGetTimeCards(projID, refFieldPath+".parent", timecards, (levels-1));
}

Once you have this, you can add the related list to your project form.

Of course, I would recommend some try/catch statements and maybe the creation of a dynamic query script or a script include, etc. But this gives the general idea.