TFS query PowerShell script

It’s been a while since I posted any PowerShell code here, so here’s a quickie script that I’m using to help with my workflow for checking in Dynamics AX changes.

First, a little background: We have a slightly odd workflow set up in TFS for tracking the projects we do in Dynamics AX. For each project, we open a TFS work item. We then check in all changes for that project under that work item. So far, so good. But we also routinely close out a work item after the first deployment that contains a check-in for that work item. So bug fixes and enhancements after the initial deployment are technically being checked in against a closed work item.

The problem here is that the check-in dialog within AX shows a list of open work items assigned to the current user, and doesn’t provide a mechanism for searching for other work items. It does allow you to manually add a work item to the list, but it can only do that by ID. But we have our own project number, which is stored as part of the work item title, and we generally don’t use the TFS work item ID for anything, so I generally don’t know it off the top of my head. So I often have to go into Team Explorer in Visual Studio to look up the work item ID for a project before I can check it in. That’s not a huge inconvenience, but I thought it would be nice to have a little PowerShell script that could look up the work item ID for me, given an AX project number.

The script shown below isn’t terribly complicated, but it shows off a few interesting little things. I started with an example script taken from Julian Kay’s blog.

First, we’re using the TFPT command-line query capability. This is part of TFS Power Tools, and uses a query syntax called WIQL.

Second, we’re doing a little rudimentary parsing of the data returned from TFPT to pull out the first work item ID. Then, we’re copying it to the clipboard with the clip command. (Looking at this script, I’m pretty such there’s a better way for me to pull out the work item ID, but the way I’m doing it now works fine.)

And finally, we’re displaying the results to the screen, so if by chance more than one result is returned, I can see the list and decide which work item is the right one.

If I wanted to go a few steps further with this, I could probably integrate this into AX completely. The check-in dialog in AX is a regular AX form named “SysVersionControlCheckIn”, and there’s no reason I couldn’t customize it. (But that’s a problem for another rainy day.)

# Given AX project #, return ID.
param (
    [string]$projno =  $( Read-Host "Enter project # (e.g. 123.4)" )
[string]$tfpt = "C:\Program Files (x86)\Microsoft Team Foundation Server 2012 Power Tools\TFPT.EXE"
[string]$svr = "http://myTfsServer:8080/tfs/defaultcollection"
[string]$projname = "myProjName"
[string]$query = "SELECT [System.Id], [System.Title] FROM WorkItems " +
    "WHERE [System.TeamProject] = '$projname' " +
    "AND [System.Title] CONTAINS '$projno' " +
    "ORDER BY [System.Id] asc"
$data = & $tfpt query /collection:$svr /wiql:$query /include:data
if ($data -ne $null) {
    $line = ($data | select -first 1)
    $taskid = $line.split("`t")[0]
    $taskid | clip
Write-Host "Press any key to continue ..."
$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.