Saturday, November 07, 2020

Using Custom Fields and Project Functions in MS Project 2019 (1)


One very important and significant functionality available in MS Project 2019 is availability of various project functions which can be used with the custom fields. While for normal management work with MS Project, you may not need these fields, you may have to use them for some out-of-the-box thinking and applicability. 

For example, you can use the fields, functions and operators to have:

I’ve listed a few above. There can be many other real-world applications. For the below examples, I’ve used MS Project 2019. You may try with other versions. 

Viewing Custom Fields

To know the custom fields, go to the Project tab – Properties group – Custom Fields command. 

You can also see the available custom fields by:

  • Checking the Gantt Chart tools – Format tab – Columns group -- Custom Fields command. 
  • Right clicking on any column on the tabular side of the view and choosing Custom Fields from there.

This will open up the “Custom Fields” dialog box as shown below.  

Types of Custom Fields

The custom fields available can be “Task” related, “Resource” related or “Project” related as the dialog box shows. 

Next, you can see that there are various types of custom fields. 

  • Number custom fields: From Number1 to Number20
  • Duration custom fields: From Duration1 to Duration10
  • Cost custom fields: From Cost1 to Cost10
  • Date custom fields: From Date1 to Date10
  • Start custom fields: From Start1 to Start10
  • Finish custom fields: From Finish1 to Finish10
  • Flag custom fields: From Flag1 to Flag20
  • Text custom fields: From Tex1 to Tex30
  • Outline Code custom fields: From Outline Code1 to Outline Code10

As you can see there are over 100 custom fields which you can use as you plan for your project or program.

Among the custom fields, the most used ones will be the Number, Flag, Date, and Text custom fields. Date is another custom field, which is also highly likely to be used. I’ve not seen much usage of other custom fields, though Cost and Duration can be used for earned value measurement (EVM) and earned schedule measurement (ESM), respectively. 

In fact, for earned schedule measurement, I’ve already used the custom fields before. You can read on it in the below link:

Earned Schedule Management (ESM) with MS Project 2019 Custom Fields


Formula Dialog Box 

This is the most interesting part available in MS Project 2019, and you can have a plethora of formulas using the available normal fields as well as the custom fields.

To write your own formula, select the radio button next to “Formula” under “Custom Attributes” and then click on the Formula button.  This will open up the “Formula” dialog box associated with the custom field.


As shown above, you can:

  • Enter the formula for in the formula area.
  • Enter the fields you want to associate by clicking on the “Field”.
  • Enter the functions you want to associate by clicking on “Function”.

Elements of Formula Dialog Box

The formula dialog box, as noted above, has primarily 3 elements – text area to write the formula, fields which can be used in the formula and functions which also can be used in the formula. 

Above the fields and functions, you have various mathematical operators such as +, -, /, * and also logical operators such as AND, OR, NOT among others. This is shown in the below figure.

There are a number of fields available when you click on the drop down arrow of Insert: Field command. These fields include the custom fields. These are shown in the below figure.


Next to the Fields, you have Insert: Function command. With this command, you can insert any function or a set of functions as you want. Of course, you have to build the logic needed to have the final formula. The functions under “Conversion” are shown in the below figure. 

For a management practitioner, the most used functions will be under “General”, “Date” and “Microsoft Project 2019” specific functions. 

Practical Examples 

Let’s take a few examples to understand the usage of formulas built with the help of fields and functions. 

Question - 1: You want to show the task ID number along with a prepended string, informing on the followings: 

  • Task type: A summary task or non-summary task
  • Task ID: The identifier value of the task.
  • WBS Outline level: The level, e.g., level 1 or level 2 of the WBS the task belongs.

An example string can be: “This is a summary task with Task ID 3. It's under WBS outline level of 2.”

Solution: 

To have the solution, we will have the following steps:

  • Create a custom text field and rename it as “String Description”. 
  • Next, we will open-up the formula dialog box and enter the following formula as shown in the below figure. This formula is concatenating multiple strings.  

As shown above, in the formula, I’ve the followings:

  • It's a text field, which means it will have a string value.
  • I’ve taken the “Type” field to know on the task type and added it to the previous string. Type is a “Number” field and hence to convert to string, I’ve used the “CStr” function, i.e., CStr([Type]), which will convert the number into a string. 
  • I’ve taken the “ID” field and again converted it to string using the CStr function. The ID field is under “ID/code”. 
  • I’ve taken the “Outline Level” field and again converted it to string using the CStr function. The ID field is under “Number”. 

The result is shown below along with the project plan. Remember to enable the “Use Formula” radio button so that the formula is applied. 


Let’s interpret the result.

  • Task A1 is with Task ID 3 and it’s correctly shown in the above string. Similarly, for other tasks. 
  • Task A1 is at Outline Level 3 and it’s correctly shown in the above string informing at outline level 3. Similarly, for other tasks. 
  • Task A1 is a non-summary tasks and hence the value for [Type] is “0”. For summary tasks, the value for [Type] is “1”. These are also shown correctly.  

However, it’s possible that you may want to show an enhance version of string, informing:

  • Task A1 is a non-summary task
  • Work Package-1 as a summary task. 

This can be done with IF function, which MS Project 2019 notes as “IIF” and it’s under one of the functions under General category. We will know this function, along with others, in the next post. 



No comments:

Post a Comment

Sign-in and put your name while asking queries in comments. Any comment is welcome - comments, review or criticism. But off-topic, abusive, defamatory comments will be moderated or may be removed.