Custom List query to only select leaf projects

[NickGeise1359991]

I've created a page with a custom list that displays Projects in the Open state. I'd also like to limit the display to only be leaf projects (projects that do not have any child projects). Is there a query I can use in the custom list to check if a project is childless?

[bulmi01]

NickGeise1359991,

 

I don't believe this is available for a query in the Custom List.  morky01, any thoughts on this one?

 

Michael

[morky01]

NickGeise1359991, try this in your query:

 

(Children.ObjectID = null)

 

That's the standard syntax for determining whether any collection in WSAPI is empty or not. 
More in the docs here: https://rally1.rallydev.com/slm/doc/webservice/rest_collections.jsp (Querying by Collection Existence section at the bottom)

[michael.addington]

Darn it all,  you beat me to posting the answer.  Ah well, my skills are stronger none the less from figuring it out.   

[NickGeise1359991]

That seems to work, thank you very much. One quick follow-up if you’ve got time – the custom list displays all projects (when no filters are set) in the workspace regardless of where I have project-focus. Other custom lists (eg: stories, features,…) limit the results to the project that I have focus and all the projects beneath it in the hierarchy.

 

Thanks,

Nick

[morky01]

Firstly, you're right- the project wsapi endpoint is one of the few that does not respect the standard project + scope up/down semantics that most other artifacts do.

 

Luckily, there's a bit of a workaround, in that you can use the special {projectOid} token in queries in the custom list app to create something like this:

 

((Parent.ObjectID = {projectOid}) OR (Parent.Parent.ObjectID = {projectOid}))

 

You'd need to add some more Parent.Parent.Parent clauses depending how deep your hierarchy is.  This is not a perfect solution by any means, but it might help a little bit?

 

The full list of supported tokens for custom list queries is here in the help: Build App Queries | CA Agile Central Help 

[Cathrin Callas]

Thanks for the tip!  My Rally help page says those are "unsupported" (not that that would stop me from using them):

 

Unsupported Nouns
Unsupported nouns can be used with curly-brace substitution in custom lists only. These are not supported and may not be present on any given revision:

• {projectName}
• {projectOid}
• {user}

[morky01]

Yep, unsupported in the broad sense, as-in wsapi itself doesn't understand these, and other query settings boxes on other apps also do not support them.  They are supported and you should feel safe using them on custom list.  

 

Some day it would be great to have wider support of these tokens in the wsapi itself.

[Pat Cousins]

It seems that if we set the scope of an app (like Custom List) to a specific project, {projectOID} follows that scope.  Is it possible to still query on the user's actual (global) scope?  In my case, I want to see portfolio items at the top project level that have children in the current project (or one of its children).

[morky01]

From custom list I don't think it's possible to get that information.  {projectOid} will always reference the scope the app is pinned to, or global otherwise.

 

From custom apps based on App SDK 2 you can get this information though:

 

https://help.rallydev.com/apps/2.1/doc/#!/api/Rally.app.Context-method-getGlobalContext 

    var localScope = this.getContext().getProject();
    var globalScope = this.getContext().getGlobalContext().getProject();

[corkr03]

NickGeise1359991, 

 

One potentially rare but possible edge case to look out for when using (Children.ObjectID = null) query to show leaf projects is that query will not return leaf projects that are leafs because they have a closed project under them.   That may or may not be desired behavior for your scenario.  Using (Children.State != "Open") query will return all leaf projects, including those that have children that are closed.  

 

Finally, to add to morky01's excellent workaround below, Agile Central only allows for a max of 9 levels of project hierarchy, so you can limit your query to account for that.  

[NickGeise1359991]

Thank you Kristy!

 

Thanks,

Nick

[Cathrin Callas]

Another option is using "(DirectChildrenCount = 0)" in your query.