Flat Lists and Tree Lists, Query Lists and Input Lists
Whether you work from a blank Office Excel worksheet or modify work items that you exported from Visual Studio Team Foundation Server, you can choose whether you want to work with a flat list or a tree list. Also, you can choose whether or not the list is refreshed based on a work item query.
List Structure: A hierarchical or tree list shows the relationships among work items and enables you to add and alter the linked relationships. A flat list shows work items but not the relationships. If you create a work item list from the results of a query, the type of query determines the type of work item list. The results of a Tree of Work Items query always appear in Office Excel as a tree list. The results of a Flat List of Work Items or a Work Items and Direct Links query always appear in Office Excel as a flat list.
List Refresh: A query list is tied to a query that is stored in Team Foundation. When you refresh a query list, Office Excel re-runs the query and shows you the latest data, so the set of work items in the list can change after the refresh. An input list is not tied to a query. Instead, you specify each of the work items, often by selecting them from the results of one or more queries. When you refresh an input list, you can see the latest data about each of those work items, but the set of work items does not change. In the work item list header, the name of the query, when applicable, and the refresh list type are displayed.
In this topic
Review the following table to determine the combination of list types that best supports the task that you want to accomplish. Review the rest of this topic for more information about each list type.
Create and upload many unrelated work items
Perform bulk edits on many unrelated work items
Query list or input list
Perform bulk edits on many work items and their dependent or related work items
Perform top down planning and upload recently created work items
View and modify the hierarchy and parent-child link relationships of many work items
Review reports based on a filtered set of work items that change over time
Flat or tree list
You can convert the list type at any time. When you change the list type, you may introduce changes to the list structure as well as the contents of the list. For example, when you change from an input list to a query list, all work items within your worksheet are replaced with the work items found by the query and the list structure that matches the query.
Review the following table to determine what will change in your worksheet based on the type of conversion performed. For more information, see Refresh Work Items and Change the List Type in Office Excel.
Change to list configuration
Changes made to the worksheet
Flat List to a Tree List
Renames the Title column to Title1 and adds a new Title2 column. You can only publish and refresh those work items already listed in the worksheet.
Tree List to a Flat List
Flattens the tree structure and changes the query list to an input list. You can only publish and refresh those work items already listed in the worksheet.
Input List to a Query List
Refreshes the spreadsheet with the work items contained in the query that you select. If the query is a tree query, then the list becomes a tree list.
Query List to an Input List
Refreshes the spreadsheet with only those work items that are currently contained in the document. A tree list is converted to a flat list.
When you open a work item list, the list type appears in the header, next to List type: either Flat or Tree.
You can use a flat list to add, modify, or update work items which either do not have linked relationships or when the linked relationships are not your current focus. This type of query list has only one Title column, which contains the title of every work item.
You can use a tree list to view and modify the parent-child relationships between work items, such as when you manage work schedules using a work breakdown structure. A tree list shows these relationships by using multiple Title columns. Each child work item and its siblings appear in a row under its parent, and the title of the child and its siblings appear one Title column to the right of the title of its parent.
For more information about how to work with tree lists and tree queries, see the following topics:
To create a tree list from a blank worksheet
To change the tree hierarchy and update work items found by a tree query
To create a tree list from a tree query in Office Excel
To create a tree query
Errors in Tree Lists Can Cause Invalid Links
When you publish changes from a tree list, Team Foundation interprets the data in the Title columns to determine the pattern of links between work items. When you create or modify a tree list, any of the following conditions can cause an invalid link or cause a tree link to be created between incorrect work items:
A row between parent and child work items is blank
The title of a work item is in the wrong column
Within a row, multiple Title columns contain data
The list has been sorted
You may not discover these errors until you try to publish the changes. For more information about invalid links and how to address them, see Resolve Invalid Links in a Microsoft Excel List Tree.
When you create a list of work items in Office Excel, you can create either a query list or an input list. You should choose the type of list based on how you want the work item list to change when you refresh it from the work item database. Both kinds of work item lists refresh work items from the work item database. You can use both kinds to add work items to the work item database or modify existing work items. The difference is that an input list contains a specific set of work items regardless of their status or progress, whereas a query list updates to contain the work items that match a specific query.
For example, if you create an input list by specifying the IDs of four work items, that list will always show those four work items. However, if you create a query list that shows the work items created during the previous day, and you refresh the list once each day, the set of work items will change every day.
When you open query results that are shown in Office Excel, by default the query type determines the list type, as shown in the following table:
Flat or Tree
Query or Input
Flat List of Work Items
[None] (Indicates it is an input list)
Work Items and Direct Links
Tree of Work Items
When you open a query list, the name of the query appears next to Query in the list header.
Use a query list when you want to periodically refresh the work item list to see the results of rerunning a query. A query list is at first tied to the query that was used to create it, and later you can tie it to a different query. When you refresh a query list, Team Foundation Server reruns the query and updates the query list with the query results. For example, if team members add new work items to the work item database that match the query, those work items are copied to the query list when you refresh the list. If you use the query list to add new work items to the work item database, the work items remain in the query list when you refresh the list only if they match the query. Otherwise, they are removed from the query list. If a team member changes the query to which the work item list is tied, the contents of the list change when you refresh the list. In the work item list header, next to Query, a query list shows the name of the query to which it is connected. The Refresh from query option in the Configure List Properties dialog box converts a work item list to a query list.
Use an input list when you require that the work item list contain only the work items that you started with plus any work items that you add. You can create an input list from a query, but thereafter the input list is not tied to that query. When you refresh an input list, Team Foundation Server does not replace the work items in the list with current query results. For example, if another team member adds a work item to the work item database that matches your initial query, the work item does not appear in an input list unless it is a child of a work item that is already in the input list. The refresh process does not remove a new work item that you created in the input list, even if the new work item does not match the original query. In the work item list header, next to Query, input lists display [none]. The Refresh current work items only option in the Configure List Properties dialog box converts an existing work item list to an input list.