Building Custom Tables and Filters in Microsoft Project
Most people using Microsoft Project have the requirement to store additional information which may not be an option in Microsoft’s standard tables. Storing additional data in custom table fields then creates the requirement to build customised filters to help find and extract that information. This post covers customising Microsoft Project tables and filters in step by step instructions.
Customised Tables in Microsoft Project
Tables may be modified or new ones created to suit a specific need. New tables may be displayed in the More Tables list in the same way as Views.
Create a Table to show Tasks and Notes
Click the View tab and, in the Data group, click Tables from the menu,
Click More Tables.
Project will display the More Tables window.
- Choose View, Tables, More Tables from the menus.
- Click New and in the Name box type Task&Notes.
- Enter these Field Names and set the alignments:
| ID | Right |
| Name | Left |
| Duration | Right |
| Start | Right |
| Finish | Right |
| Notes | Left |
The finished result should look similar to that below.
Click on OK and then Apply.
Drag the vertical split bar to the right then select Print.
Close the view and adjust the column widths as necessary.
Adding Columns to Tables
If none of the Microsoft Project tables show all the information required, a column can be added to an existing table.
To add columns
- In a sheet view, select in the column heading of the Add New Column column.
This will cause a list of all the fields available in Project to be shown.
- Choose the Field required.
In most cases this will be all that is required.
Set Up a Column for Responsibility
- Place the cursor in the heading of the Add New Column column.
- Type the heading Responsibility and press Enter
- Right Click in the heading of the Responsibility Column.
- Choose Field Settings from the menu
The Field name will be shown as Text1(Responsibility).
- Type Responsibility as the column Title.
- Set alignment to Left.
- Click OK.
Custom Filters in Microsoft Project
Editing a Filter
- Choose View and click the down arrow next to Filter:.
- Choose More Filters.
- Select a filter and click Edit.
- Examine the options available.
Custom Filter Values
The essence of the custom filter above is the contents of the Value column. The value can be a simple criterion against which the filter checks (a resource name or simple numeric value) or a string or program to test and define.
The Filter Definition dialogue box
Choose View, click next to Filter: and choose New…
Use this dialogue box to create or change a custom filter. Here various changes can be made such as:
- Define the criteria, including the field test, and value, which tasks or resources must meet to be displayed or highlighted by the filter.
- Change the set of filters that appears on the Filtered for submenu.
There are various details in the filter definition table, which can be defined to create the criteria which tasks or resources must meet to be displayed or highlighted.
And/Or If the filter contains more than one criterion row, enter an operator for each row. If an operator is not included, Project uses the And operator.
Field Name Enter the field whose contents are to be tested. Project checks the contents of this field for all the tasks or resources in the current view. If the contents of the field meet the test specified, the task or resource is displayed or highlighted.
Test Type a test, or click one from the list. Project uses the test to compare the value or values typed in the Value(s) box with the field information for each task or resource. Project indicates if a test is not valid for the field selected. Choose one of the following tests:
equals Displays or highlights tasks or resources whose field contents exactly match the value. The field may not contain any values other than the matched value. For example, if a task view is filtered to show tasks whose Resource Name field equals “Jane Clayton”, Project displays all the tasks to which “Jane Clayton” is assigned. Project does not display the tasks to which “Jane Clayton (Supervisor)” is assigned or to which “Jane Clayton” and several other resources are assigned.
does not equal Displays or highlights tasks or resources whose field contents do not match the value exactly.
is greater than Displays or highlights tasks or resources whose field contents are greater than the value.
is greater than or equal to Displays or highlights tasks or resources whose field contents are greater than or equal to the value.
is less than Displays or highlights tasks or resources whose field contents are less than the value.
is less than or equal to Displays or highlights tasks or resources whose field contents are less than or equal to the value.
is within Displays or highlights tasks or resources whose field contents fall within the range specified by the two values.
is not within Displays or highlights tasks or resources whose field contents fall outside of the range specified by the two values.
contains Displays or highlights tasks or resources whose field contents contain the value. For example, if tasks are filtered for whose Name field contains “develop,” Project displays “Develop documentation” as well as “Have development team review specs”.
does not contain Displays or highlights tasks or resources whose field contents do not contain the value.
contains exactly Displays or highlights tasks or resources whose field contents contain a task or resource which exactly matches the value. This differs from the equals test because the contains exactly test can operate on fields such as Resource Names or Predecessors, which can list multiple values at once. For example, suppose the filter is created: Resource Names contain exactly Jane. Now if there is a resource named Jane assigned to Task 1, Jane Clayton assigned to Task 2, and Jane and Jane Clayton assigned to Task 3. Task 1 and Task 3 would meet the contains exactly criteria. If the filter is created: Resource Names equals Jane, only Task 1 would meet the criterion.
Value(s) Enter the numbers, dates, or text that the field contents are to be tested against. A field from the list can also be chosen to compare its contents with the contents of the field specified in the Field Name column. Values must match the type of field being tested against. For example, if Cost is selected for the field name, Finish Date cannot be used in the Value(s) field. When inappropriate values are entered in the Value(s) field, Project displays a message when the Filter is saved.
To indicate a range for the is within or is not within test, separate the values with the list separator character. For example, if within has been selected in the Test column, tasks which fall within two dates can be found by typing:
1/1/11,2/1/11
Wildcards
Wildcards can be used as part of the value when the test is equals or does not equal. The question mark (?) wildcard and the asterisk (*) wildcard can appear anywhere in the text. For example, all the following are valid values:
Install*Clock????-401*
To filter using a character reserved for use as a wildcard, type a caret (^) in front of the character. For example, to filter for tasks which include an asterisk in their name, type ^*.
Interactive Filters
To create an interactive filter, type a message enclosed in quotation marks, followed by a question mark (?). For example, “Enter date:”? When this filter is applied, a dialogue box appears and displays the message. You use this dialogue box to enter the value or range of values to be tested against the field specified in the Field Name column.
To create a calculated filter, type a field name enclosed in square brackets ([ ]) or click a field name from the list. When the filter is applied, Project compares the value in the field specified with the value in the field specified in the Field Name column.
Note: Wildcards cannot be used when specifying a calculated filter.
To filter for fields which have no date, type NA in the Value(s) field. In number and currency fields, to filter for no number, enter 0. To filter for text fields which have no text entered, leave the Value(s) field blank.