Share via


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

  • Choosing the List Structure and List Type to Support Your Task

  • Changing the List Type

  • Flat Lists and Tree Lists

  • Query Lists versus Input Lists

Choosing the List Structure and List Type to Support Your Task

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.

Task

List structure

List refresh

Create and upload many unrelated work items

Flat list

Input list

Perform bulk edits on many unrelated work items

Flat list

Query list or input list

Perform bulk edits on many work items and their dependent or related work items

Tree list

Query list

Perform top down planning and upload recently created work items

Tree list

Input list

View and modify the hierarchy and parent-child link relationships of many work items

Tree list

Query list

Review reports based on a filtered set of work items that change over time

Flat or tree list

Query list

Changing the List Structure and List Refresh

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

Procedure

Changes made to the worksheet

Flat List to a Tree List

  1. On the Team tab menu, click Add Tree Level.

  2. In the Convert to Tree List dialog box, click Parent-Child or other type of link, and then click Yes.

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

  1. On the Team menu, click Configure, and then click List.

  2. Click Refresh work items only, and then click Apply.

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

  1. On the Team menu, click Configure, and then click List.

  2. Click Refresh from query, and specify the work item query to use to refresh the list.

  3. Click Apply.

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

  1. On the Team menu, click Configure, and then click List.

  2. Click Refresh work items only, and then click Apply.

Refreshes the spreadsheet with only those work items that are currently contained in the document. A tree list is converted to a flat list.

Flat Lists and Tree Lists

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.

Office Excel Work Item Tree List that shows how the column layout displays parent-child work item links

Microsoft Excel work item tree list

For more information about how to work with tree lists and tree queries, see the following topics:

Task

Related topic

To create a tree list from a blank worksheet

Performing Top-Down Planning Using a Tree List of Work Items (In Excel)

To change the tree hierarchy and update work items found by a tree query

Configure Work Items in a Tree List in Office Excel

To create a tree list from a tree query in Office Excel

Create, Open, and Modify Work Items Using Office Excel

To create a tree query

View and Modify Work Items in a Tree View

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.

Query Lists versus Input Lists

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:

Query type

Flat or Tree

Query or Input

Flat List of Work Items

Flat

[None] (Indicates it is an input list)

Work Items and Direct Links

Flat

Query

Tree of Work Items

Tree

Query

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.

See Also

Tasks

Performing Top-Down Planning Using a Tree List of Work Items (In Excel)

Refresh Work Items and Change the List Type in Office Excel

Connect a Microsoft Office Document to Team Foundation Server

Create, Open, and Modify Work Items Using Office Excel

Configure Work Items in a Tree List in Office Excel

View and Modify Work Items in a Tree View

Concepts

Managing Work Items Using Microsoft Excel Bound to Team Foundation Server