Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Search on the WBS field by using special operators

 

Version 1, Published on November 23rd, 2015

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is

related to all standalone desktop versions of MS Project Professional/Standard supporting the custom field formulas feature.

 

Let us first explore the special operators that can be used in the custom field formulas, before starting to discuss how to use these operators to perform searches on the WBS field's data.

 

 

Using Special Operators: Like and Between…And

 

A criterion defined in a task or resource filter (or custom AutoFilter) or for a graphical indicator can be converted to a condition to be tested in a formula, by using a logical operator corresponding to the test expression selected. Test expression is the item selected from the drop-down list of the Test column in the Filter Definition dialog box, or in the Custom AutoFilter dialog box or in the Indicator criteria table of the Graphical Indicators dialog box. The following table lists test expressions and the corresponding comparison operators:

 

Test expression

Logical operator

equals

=

does not equal

<> 

is greater than

is greater than or equal to

>=

is less than

is less than or equal to

=<

 

The table above does not include the tests for “within” and “contain”. The “within” tests can be converted into multiple conditions composed of comparison expressions combined by logical operators. For example, consider the filtering criterion Number1 is within 1,9. Any value in the Number1 field that falls within the range specified by 1 and 9 (inclusive) satisfies that criterion. The filtering criterion should be changed to Number1 is not within 1,9 (inclusive) in order to find the values that falls outside the same range. So how can we create conditions representing these two criteria ? The answer is below:

 

Filter applied to the Number1 field:

The formula testing the same condition in the Text1 field:

Number1 is within 1,9

iif (

1 <= [Number1] And [Number1] <= 9;

[Number1]; “” )

Number1 is not within 1,9

iif (

Not (1 <= [Number1] And [Number1] <= 9);

[Number1]; “” )

 

Using a custom text field avoids display of zeros in the Text1 column for the values that do not satisfy the filtering criterion. There is a more practical way to perform the tests above in the formulas; using a special operator that does not have a button in the Formula dialog box: the Between...And operator. So we can construct the formulas as follows:

 

iif ( [Number1] Between 1 And 9; [Number1]; “” )

 

and

 

iif ( [Number1] Not Between 1 And 9; [Number1]; “” )

  

Suppose that we need to see the tasks whose predecessor is the task with identification number 5. We can quickly list those tasks by using AutoFilter, but here we will create custom filters (or custom AutoFilters) in order to demonstrate how “contain” tests work as shown below:

 

Task table:

Filtering criterion used and the task lines displayed:

Predecessors

contains 5

Predecessors

does not contain 5

Predecessors

contains exactly 5

5,4

1,55

3,7,53

6,5,9

20,4,5

No result

5,4

6,5,9

20,4,5

 

As it is seen in the table above, the tests “contains” and “does not contain” list the predecessor lists containing any number of 5s and the predecessor lists that does not contain any 5s, respectively. The test “contains exactly” recognizes the delimiter and finds only 5s standing alone. The “contain” tests of the filters used for string search in the fields like the one shown above can be performed by using the Instr or the StrComp functions in formulas.

 

These formulas will be relatively complex formulas, especially when we need to find the multiple occurrences of a string in the field. Instead, we can use some other special operator, which do not have a corresponding button in the Formula box; the Like operator.

 

The Like operator requires two operands; the left operand is any string expression (here, the field Predecessors) and the right operand is the pattern string. The Like operator returns true if each character of the string expression matches the character at the corresponding position in the pattern string; for example, the condition [Text1] Like “abc” returns true if the Text1 field contains the string abc exactly.

 

The pattern string may contain any combination of the following elements:

 

  • A specific character.

  • A character list: for example, [abc] represents any of the characters a, b or c, [129] represents any of the numeric characters 1, 2 or 9.

  • A character or digit range: for example, [a-z] represents any character from a to z, inclusive; [0-9] represents any digit from 0 to 9, inclusive.

[!a-z] represents outside a range of characters, [!0-9] represents no digit.

  • A wildcard character: * represents any number of characters, ? represents a single character, # represents a single digit. A wildcard character is enclosed in square brackets in order to use as literal character, such as [*], [?] and [#].

  

As a result, the Like operator helps us to find the text information in the fields that satisfies any pattern that we define. Let us now create custom flag field formulas doing the same string searches by using the Like operator:

 

Filter applied to the Predecessors field:

The formula testing the same condition in the Flag1 field:

Predecessors contains 5

[Predecessors] Like “*5*”

Predecessors does not contain 5

[Predecessors] Not Like “*5*”

Predecessors contains exactly 5

[Predecessors] Like "5,*" OR

[Predecessors] Like "*,5,*" OR

[Predecessors] Like "*,5"

 

Note that the filter for "Predecessor contains 5" or the corresponding formula is not a useful one since there seems to be no practical use of searching "5" in the task ID numbers. Also the filter for "Predecessor contains exactly 5" or the corresponding formula is not a useful one because the tasks linked to the task of the ID# 5 with a dependency other than the default finish-to-start will be excluded in the result. The formula can be improved for the other types of the task dependencies as follows:

 

[Predecessors] Like "3[SF][SF],*" Or [Predecessors] Like "*,3[SF][SF],*" Or [Predecessors] Like "*,3[SF][SF]" Or

[Predecessors] Like "3,*" Or [Predecessors] Like "*,3,*" Or [Predecessors] Like "*,3"

 

We do not need to use the iif function in the formulas entered to the custom flag fields since the Like operator returns logical values.

 

Regarding operator precedence and associativity; both Like and Between…And operators have the same precedence and associativity as the comparison operators. Also note that the wildcard characters (*, ? and #) are treated as literal characters when used in the operands of Between…And operator.

 

Let us now continue with the examples of using the special operators in order to perform searches on the WBS field.

 

 

Grouping task data based on WBS code

 

In the following example, the Text1 field’s formula (not calculated for summary rows) is used to find WBS elements matching the custom pattern defined:

 

Text1 field’s formula: [WBS] Like "WBS-*F.[1-3]0?"

 

 

Note that the Like operator (also referred to as pattern matching operator) returns Boolean results (i.e., logical results), and therefore, the custom text field displays the returned values as Yes or No. Otherwise, we need to use the CBool function to convert the results of the formula to the Boolean type values in order to see Yes or No, instead of -1 or 0, in the custom text field. Alternatively, a custom flag field can also be used; notice the difference between Yes (or No) in a custom text field which is a string and Yes (or No) in a custom flag field which is a Boolean value.

 

The pattern "WBS-*F.[1-3]0?" represents any string containing;

 

  • any number of characters between “WBS-“ and “F.” which matches WBS-TTF. and WBS-TKF..

  • then any numeric character that matches 1, 2 or 3 (any numeric character in the range from 1 to 3, inclusive), that is, WBS-TTF.3 and WBS-TKF.2;

  • and finally 0 and any single character represented by “?”.

 

So the matching items are WBS-TTF.301 and WBS-TKF.201 as it is seen above. Instead of the range [1-3], list of characters can be used as in [123]; this is what we need to do when we want to match a numeric character between the dot (.) and 0 in the WBS codes of the schedule above to any numeric character in a list such as [14789].

 

We can now apply filtering or grouping on Text1 while working on the task list. For example, filtering out the lines containing No in the Text1 column and then grouping on Text1 by using AutoFilter give us the total work and cost amounts for the tasks specified with the WBS codes, WBS-TTF.301 and WBS-TKF.201 as it is seen below:

 

 

Note    The information presented in this section, regarding usage of special operators in the expressions, is based on experimentation with the product. Therefore, always do your own tests before using them in real project environment since there is no guarantee that these operators will be recognized and they will demonstrate the same behavior as described above in all versions of the product.

 

We can use another custom text field Text3 to display information on the group summary row about the pattern on which the grouping is based. The Text3 field’s formula is as follows (select the Use formula option):

 

iif( [Group By Summary] And InStr([Name],"Yes"),"Info on pattern",

iif( [Text1]="Yes", Space( [Outline Level] ) & [Name],"")

)

 

The string “Info on pattern” in the formula can be replaced with any information describing the purpose of the pattern search operation. This is the resulting table:

 

 

The custom text field is not a Boolean type field like a flag field, therefore, the Yes that the field shows is just a text string, instead of a Boolean Yes (that is, logical Yes); for this reason, the condition [Text1]="Yes" is used instead of [Text1]=Yes in the iif function.

 

 

Grouping task data based on WBS level

 

Consider the WBS codes defined in the schedule above; the whole WBS column can be copied to a custom outline code field lookup with the same code mask definition. While defining code mask, you also need select the checkbox Allow additional items to be entered into the field. (Values will be added to lookup in “Data entry options” section of the Edit Lookup Table dialog box in order to avoid pasting error.

 

The descriptions for the items can be added to the lookup table as shown below:

 

 

Suppose that we have now the exact same WBS code in the Outline Code1 field, so we can define a task group as follows:

 

 

The following picture shows the schedule grouped based on custom outline code field with the group interval set to level 1 (the period is the separator, so level 1 corresponds to the first part of the outline code on the left of the first period from left to right):

Group summary rows show the descriptions, instead of the codes, if there are descriptions entered for the items in the lookup of the Outline Code1 field. But we need to see the items (or the codes). Our descriptions are prefixed with the code, therefore, we can extract the code part by using a custom text field Text2 with a formula like this:

 

iif( [Group By Summary], Left([Name],Instr([Name],"|")-1), Space( [Outline Level] ) & [Name] )

 

We can now display the codes at the group summary level as well, as shown below:

 

 

In a group view, we can always hide the Name column, or minimize its width or move it to the last position on the table if we want to hide the group headers.

 

The above table shows the task data grouped based on the custom outline code field’s data which is identical to the WBS codes in this example. We have manually copied WBS codes to a task custom outline code field since we cannot apply grouping to the WBS field. On the other hand, unlike the custom outline code fields, WBS field automatically expands as new items are added to the outline structure of the project. Therefore, it is important to update the Outline Code1 field by repeating the copy process when the WBS codes change.

 

 

Grouping assignment data based on WBS codes

 

In the Task Usage view below, the Text1 field shows Yes for the tasks whose WBS code matches the pattern "WBS-*F.[1-3]0?". In this part of the example, we will find total cost of a particular resource assigned to these tasks. Note that the Text1 field’s formula works only in the task lines. Text4 field’s formula and the settings are as follows:

 

            Text4 field’s formula: iif( [Group By Summary], "", WBS & " (" & [Name] & ")" )

Calculation for task and group summary rows - Use formula

Calculation for assignment rows - Roll down unless manually entered

  

 

 

Let us now define a task group based on the task custom text field Text1 with the checkbox Group assignments, not tasks turned on in the Group Definition dialog box. When the new group is applied to the table, Project will hide all the task and summary lines and the group header for Text1: Yes will show the total resource hours scheduled and the corresponding cost on the tasks whose WBS code matches our particular pattern as shown below:

 

 

Let us now run the filter Using Resource… by selecting the work resource R2 in the filter’s drop-down list. The resulting table will be as follows:

 

 

Instead of applying the custom table, the custom group and the Using Resource… filter to the Task Usage view separately, a task usage view using these elements can be defined. Then displaying the table view above can be as easy as changing a view. The filter will ask for the resource name as soon as we select this view.

 

 


Revision History:

Version 1 - November 23rd, 2015 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.