Mike,
What you are doing here is querying for a particular “Team Instance”. I am not sure what the correct term is for this, possibly just “Team”. But internally at Weatherford we always refer to “Team Templates” and “Team Instances” to avoid confusion. In your example query you are correctly building joins between the Change Object, Team Instance, Role Principal Map and Principal. But the same Team Instance is not associated with different change objects (you are only querying one), so if you join them the query will return nothing.
To get returns from both, what you need to do is add a second Team, Role Principal Map and Principal to the “From” tab in your query. You could give these an alias to make building the joins easier. E.G. “Principal Task”, “Principal Notice” etc. Then you build joins appropriately between the first and second set of objects, and add the second set of objects to the returns of the query.
If you wanted to limit returns to just principals that are associated with both the Task and the Notice, then you could just use one principal and build both joins to that. If you wanted to have a mutually exclusive query, only returning users for the Task where they are not in the activity, then you need to query both and add a condition between them. I have included some screenshots in the attached PowerPoint. Please note that our installation is different from yours in that we are still using Foundation and I have not tested the query, but I was just trying to get you enough info so you could do your own experimentation.
One tip I will give you when you start building complex queries that rely on joins to limit returns (assuming you don’t have a query limit set on the server), is that it is a really good idea to add some fixed constant query criteria (object numbers forinstance) while testing so that when you are testing you will only return a few records even if you get the joins wrong. Otherwise if you miss a join your query will try and return every instance for the object you added in the “from” section, clearly this could easily bring your methodserver down if you have enough of those.
Best regards,
-----
Lewis
Lewis
In Reply to Mike Lockwood:
We currently have a very useful Query Builder report related to change management. It includes people by Role for the Change Task Assignee and Reviewer Roles. We'd like to include people by Role for one Change Notice Role as well. Tech Support advises that this is not possible.
Please see attached screencaptures of how we currently have the report, esp the Team Role Principaljoins. Note: Reporting theChange NoticeRole works with no problem if run withwithout including RolesPrincipals from the Change Task (consistent with what Tech Support said).
Any thoughts on how to get what is needed by outsmarting Windchill?
Ref - Message from Tech Support:
You opened xxx with PTC Technical Supportwith the following description: "query builder - add team member."
I think the query in the case would need to be unioned which can't be done in query builder. It's just intended for basic queries really. You can easily modify this report to get the result, but not both in the same report which I believe you had already done. The problem is that the change task and change notice point to different entries in the team table.
