Using Formulas and Graphical Indicators with Custom Fields
Applies to: Office 2010 | Project 2010 | Project Server 2010 | SharePoint Server 2010
In this article
Creating and Validating Formulas
Custom Field Formulas and Localization
Formula References to Resource, Project, and Task Fields
Errors in Formulas
Custom field values and graphical indicator values in Microsoft Project 2010 can be calculated by using formulas. You can create formulas for local custom fields by using Microsoft Project Standard 2010 or Microsoft Project Professional 2010 and for enterprise custom fields by using Project Web App or the Project Server Interface (PSI).
This article includes the following sections:
Creating and Validating Formulas
Custom Field Formulas and Localization
Formula References to Resource, Project, and Task Fields
Errors in Formulas
For descriptions and syntax of the functions that are available for writing formulas, see the Reference section of Help in Project Standard or Project Professional, or see Project Functions for Custom Fields on Office.com. Project 2010 uses the same Jet Expression service for formulas that Microsoft Access 2010 uses. For general examples and more information about formulas and expressions, see Guide to Expression Syntax in the online help for Access and Functions (Category List) in the MSDN Library (https://msdn.microsoft.com/en-us/library/ff836861.aspx).
Creating and Validating Formulas
To create or edit a formula for a local custom field or outline code, you must use Project Professional 2010 or Project Standard 2010. It is not necessary to install and launch Project Professional 2010 on the computer running Project Server to create or validate formulas for enterprise custom fields. However, you can use Project Professional 2010 to create a local custom field with a formula and then add the field to the enterprise.
You can create and edit formulas and add graphical indicators for enterprise custom fields on the New Custom Field page and the Edit Custom Field page in Project Web App. Microsoft Project Server 2010 calculates the values of formulas for project, task, and resource custom fields when a project is published. For example, if you use the Project Web App Resource Center page to add a resource custom field that has a formula, you can directly add the formula without starting Project Professional 2010 as Project 2007 requires.
Note
When you create or reconfigure a custom field in Project Web App, you must restart Project Professional to see the new or changed enterprise custom field in the lists of custom fields.
Project Web App in Project Server 2010 includes drop-down lists that make it easier to pick common fields, functions, and operators. Figure 1 shows a simple formula for a resource custom field of type Cost, named TestResourceCost, and specifies tests for the graphical indicators.
Figure 1. Creating custom field formulas in Project Web App
.gif "Creating custom field formulas")
If you add the TestResourceCost column to the Resource Sheet view of an enterprise project in Project Professional 2010, the graphical indicators show the relative cost for enterprise resources and for local resources.
Note
The Pick Field list in Project Web App shows only fields that exist in Project Server. For a Project Server field to validate, it must exist also in Project Professional. You can add fields from Project Professional that the Pick Field list does not show. For more information, see the Formula References to Resource, Project, and Task Fields section.
The following procedure shows a more useful example of a custom field that uses a formula and graphical indicators.
To create and use an enterprise custom field with graphical indicators
Use Project Web App to create a task custom field of type Text, named Budget Indicator.
To use the sample formulas, paste the code into a text editor to remove the line breaks, and then paste the single line of code into the Formula text box on the New Custom Field page.
Note
The CStr function in the following formula works only if the [Baseline Finish] field is defined; otherwise, the formula returns #ERROR for the "No baseline" case. If you try the formula, use it without graphical indicators so that you can see the text results. When you use the formula with graphical indicators, the error is not obvious in Project Professional 2010 because no indicator shows.
Switch(Len(CStr([Baseline Finish])) < 3, "No baseline", ([Cost] + 1) / ([Baseline Cost] + 1) > 1.2, "Overbudget by 20% or more", ([Cost] + 1) / ([Baseline Cost] + 1) > 1, "Overbudget", True, "Under budget")To fix the previous formula so that it works when a task does not have a baseline, evaluate the dates for the [Baseline Estimated Finish] field. The following example modifies the Switch statement.
Switch(Not ([Baseline Estimated Finish] >= #1/1/1984# And [Baseline Estimated Finish] < #1/1/2050#), "No Baseline", ([Cost] + 1) / ([Baseline Cost] + 1) > 1.2, "Overbudget by 20% or more", ([Cost] + 1) / ([Baseline Cost] + 1) > 1, "Overbudget", True, "Under budget")There are several ways to create a complex formula; for example, you can use nested Iif statements. The results of the previous and following formulas are the same.
Iif( [Baseline Estimated Finish] >= #1/1/1984# And [Baseline Estimated Finish] < #1/1/2050#, Iif( ([Cost] + 1) / ([Baseline Cost] + 1) > 1.2, "Overbudget by 20% or more", Iif (([Cost] + 1) / ([Baseline Cost] + 1) > 1, "Overbudget", "Under budget"), "No baseline")Tip
Because Project Professional 2010 does not need to be restarted before testing formula changes in local custom fields, it can be quicker to edit and test a formula locally and then copy the correct formula to Project Web App.
Use an external editor, such as Microsoft Visual Studio, to save a text file of multiple examples of formulas.
For more information about dealing with errors in custom field formulas, see Errors in Formulas.
In the Calculation for Summary Rows section, click Use formula. This enables summary tasks to show graphical indicators that summarize the budget indicators for subtasks.
In the Values to Display section, click Graphical indicators, and then select Non-summary rows in the Criteria for drop-down list.
Create the following tests in the grid:
Test
Value(s)
Image
equals
No baseline
White button
equals
Overbudget by 20% or more
Red button
equals
Overbudget
Yellow button
equals
Under budget
Green button
Click Save to save the Budget Indicator custom field.
Restart Project Professional 2010, and then create a simple test application with a summary task and two subtasks. For example, create a subtask named T1 with a duration of six days and subtask T2 with a duration of two days.
Assign resources to task T1 and task T2, where the resources have a standard rate cost defined. You can use enterprise and/or local resources. For example, use a standard rate of $50.
On the Gantt chart, add a column for the Budget Indicator (Enterprise) field. Right-click the column header, click Field Settings, and then type Budget in the Title text box of the Field Settings dialog box.
On the Project tab of the ribbon, click Set Baseline. In the Set Baseline dialog box, select Baseline in the Set baseline drop-down list, and then click Entire project. The graphical indicators for the summary task and for both subtasks are green.
Increase the duration of task T1 to seven days. Both T1 and the summary task should show yellow indicators in the Budget column, because the cost is now over the baseline budget.
Add subtask T3, set the duration to three days, and set T2 as its predecessor task (Figure 2). Assign a resource to T3. Although T3 is not in the baseline calculation, it increases the total cost of the summary task by more than 20 percent over the baseline. The Budget indicator for the summary task changes to red. The Budget indicator for T3 is white (rather than an empty field) because T3 has no baseline.
In Figure 2, the Budget column uses the Budget Indicator custom field in Project Web App. The Test Budget column uses a local task custom field with a test formula and shows the text value rather than an indicator.
Figure 2. Using graphical indicators in Project
.gif "Using graphical indicators in Project")
For an example of how to use the PSI to read and update a custom field formula, see UpdateCustomFields2. The example uses the same formula previously described.
Custom Field Formulas and Localization
The PSI CreateCustomFields and UpdateCustomFields methods are carried over from Office Project 2007. They enable you to create or edit formulas by using the MD_PROP_FORMULA property in the CustomFieldDataSet, only if you use a procedure to convert formulas to localization-independent values.
The CreateCustomFields2 and UpdateCustomFields2 methods are new in Project Server 2010. They enable you to create or edit formulas by using language-dependent values for MD_PROP_FORMULA.
Note
If you use the CreateCustomFields, UpdateCustomFields, ReadCustomFields. ReadCustomFieldsByEntity, or ReadCustomFieldsByMdPropUids methods, the MD_PROP_FORMULA values in the returned CustomFieldDataSet use a localization-independent string for the formula. Project Professional converts custom field formulas to the localization-independent syntax.
Project Server 2010 adds the CreateCustomFields2, UpdateCustomFields2, ReadCustomFields2. ReadCustomFieldsByEntity2, and ReadCustomFieldsByMdPropUids2 methods, where the MD_PROP_FORMULA values are in a language-dependent format. To see the difference, read a custom field by using one of the previous methods and by using one of the new methods, and compare the values of MD_PROP_FORMULA.
The following procedure is required only if you use one of the older PSI methods to create or modify custom fields. If you use one of the new methods, you can directly use language-dependent formulas in MD_PROP_FORMULA values.
To create a localization-independent formula using the PSI
Create and save an enterprise custom field that includes a formula. Use one of the following methods:
In Project Web App, create the custom field and formula and save it. Project Web App opens Project Professional to validate the formula.
In Project Professional, create a local custom field and formula, by using the Custom Fields dialog box. When the field is complete, click Add Field to Enterprise. Exit Project Professional, and then, in Project Web App, ensure that the enterprise custom field is present and contains the formula.
Create a utility application to read the custom field with the converted formula. You can use any one of the following PSI CustomField methods to get a CustomFieldDataSet that contains the formula: ReadCustomFields, ReadCustomFieldsByEntity, or ReadCustomFieldsByMdPropUids.
Examine values in the CustomFieldDataSet in the Visual Studio debugger, or save the formula string value that you need to a file.
Paste the converted formula string into the code that sets the MD_PROP_FORMULA property for the CustomFieldDataSet in your new application. Set the other properties as necessary.
Use the CreateCustomFields method or the UpdateCustomFields method with the modified CustomFieldDataSet.
Formula References to Resource, Project, and Task Fields
Project Server includes a different set of fields for resources, projects, and tasks, instead of the intrinsic fields in Project Professional. When you create or edit a custom field formula in Project Web App, the Pick Field list on the Edit Custom Field page shows only the fields that are available in Project Server. Project Server can validate only the fields that are also available in Project Professional.
For example, the Pick Field list for a resource custom field in Project Web App shows [Timesheet Manager]. However, the Edit Custom Field page shows the following error when you try to save a resource custom field with a formula that includes [Timesheet Manager]: This formula contains references to one or more fields that do not exist in the system. Correct the formula and try again.
You can use fields from Project Professional that are not shown in the Pick Field list. For example, the [ACWP] field validates in the formula for a resource cost custom field. Some fields have different names in the client and the server; for example, the [Resource Name] field validates in Project Web App, but the [Name] field that exists in the client does not validate in Project Web App.
Following are the resource fields for Project Server 2010 that do not exist in Project Professional and therefore cannot validate:
[Active]
[Checked Out]
[Cost Center]
[Cost Type]
[Earliest Available]
[Last Modified]
[Last Published]
[Latest Available]
[Timesheet Manager]
[Synchronize Tasks] (with Microsoft Exchange Server)
Warning
Field validation and calculation results from the formula engines in Project Professional 2010 and in Project Server 2010 are not always consistent. When you use a formula created in Project Web App and compare it with the same formula created in Project Professional 2010, you should test to ensure the results are what you expect.
As an example of inconsistent validation, the [Base Calendar] field shows in both the Pick Field list in Project Web App and in Project Professional 2010. Project Web App shows an error when you try to create a formula that includes the [Base Calendar] field. You can create a resource text custom field in Project Professional 2010 that includes [Base Calendar], and then export the custom field to Project Server. The enterprise custom field appears to work, but when you examine it in Project Web App, the formula shows [#Error] in place of the [Base Calendar] field.
Resource Fields: Table 1 includes resource fields that show only in Project Professional 2010. A formula-based resource custom field that is defined in Project Web App can reference these fields. Table 1 also indicates which fields are not consistent when exported to Project Server, such as [Base Calendar], [Baseline Budget Cost], and others.
Table 1. Resource fields that show only in Project Professional 2010
[Accru At] |
[Actual Cost] |
[Actual Finish] |
[Actual Overtime Cost] |
[Actual Overtime Work] |
[Actual Start] |
[Actual Work] |
[ACWP] |
[Assignment] |
[Assignment Delay] |
[Assignment Owner] |
[Assignment Units] |
[Available From] |
[Available To] |
[Base Calendar] (not consistent) |
[Baseline Budget Cost] (not consistent) |
[Baseline Budget Work] (not consistent) |
[Baseline Cost] (not consistent) |
[Baseline Finish] |
[Baseline Start] |
[Baseline Work] (not consistent) |
[BCWP] |
[BCWS] |
[Booking Type] |
[Budget Cost] |
[Budget Work] |
[Can Level] |
[Code] |
[Confirmed] (not consistent) |
[Cost] |
[Cost Rate Table] |
[Cost Variance] |
[Created] |
[CV] |
[Default Assignment Owner] |
[Email Address] |
[Enterprise] |
[Enterprise Base Calendar] |
[Enterprise Required Values] (not consistent) |
[Enterprise Team Member] |
[Enterprise Unique ID] (not consistent) |
[Error Message] |
[Finish] |
[Group] |
[Group By Summary] |
[Hyperlink] |
[Hyperlink Address] |
[Hyperlink SubAddress] |
[Import] |
[Inactive] |
[Indicators] |
[Leveling Delay] |
[Linked Fields] |
[Material Label] |
[Max Units] |
[Name] (use [Resource Name] instead) |
[Notes] |
[Objects] |
[Overallocated] |
[Overtime Cost] |
[Overtime Rate] |
[Peak] |
[Percent (%) Work Complete] |
[Phonetics] |
[Project] (not consistent) |
[RBS] |
[Regular Work] |
[Remaining Cost] |
[Remaining Overtime Cost] |
[Remaining Overtime Work] |
[Remaining Work] |
[Request/Demand] (assignment field) |
[Resource Calendar GUID] (not consistent) |
[Resource GUID] (not consistent) |
[Response Pending] (not consistent) |
[Standard Rate] |
[Start] |
[Summary] |
[SV] |
[Task Outline Number] (assignment field) |
[Task Summary Name] |
[Team Assignment Pool] |
[TeamStatus Pending] (not consistent) |
[Update Needed] (not consistent) |
[VAC] |
[WBS] (assignment field) |
[Windows User Account] |
[Work] |
[Workgroup] |
[Work Contour] |
[Work Variance] |
|
Project and Task Fields: Table 2 includes project and task fields that show only in Project Professional 2010. A formula-based project or task custom field that is defined in Project Web App can reference these fields, even though they do not show in the Pick Field list.
Table 2. Project and task fields that show only in Project Professional 2010
[Creation Date] |
[Current Date] |
[Default Finish Time] |
[Default Start Time] |
[Last Update] |
[Project Finish] |
[Project Start] |
[Status Date] |
[Minutes Per Day] |
[Minutes Per Week] |
[Resource Count] |
[Task Count] |
[Author] |
[Project Calendar] |
[Subject] |
[Title] |
For more information about fields in formulas for Project 2010 and for Project Server 2010, see Available Fields.
Errors in Formulas
It can be difficult or time-consuming to debug complex formulas. One approach is to break down complex formulas into simpler sections and test each section for the results. In some cases, you can create a Microsoft Visual Basic for Applications (VBA) function that works correctly, and then port the function to the custom field formula.
Most errors in formulas are a result of errors in syntax, mismatched parentheses, missing field delimiters, or mismatched types. Carefully check the Project Functions for Custom Fields, Guide to Expression Syntax, and Functions (arranged by category) references. For help with specific questions, the Project Developer newsgroup is useful. To find newsgroups in other languages, click All Groups by Language/Topic on the Microsoft Newsgroups page.
Sometimes the function implementation does not cover all cases. Following are two examples:
CStr function: In the Budget Indicator custom field described previously, the [Baseline Finish] field returns a date or NA if no baseline is set. Because the CStr function correctly returns the text value of a date, a developer might assume that CStr([Baseline Finish]) would return the text value NA when no baseline is set, but instead, it returns #ERROR. As described in Creating and Validating Formulas, the solution is to check the date values, rather than convert to the text value.
ProjDateAdd and ProjDateSub functions: To subtract seven days from a specified date, the following formula works correctly on Project Professional 2010: ProjDateAdd("9/24/2010", "-7d"). However, when you run the same formula on Project Server 2010, the result is 9/24/2010, not 9/17/2010. For formulas that work consistently on Project Professional 2010 and Project Server 2010, you should avoid negative parameters for the ProjDateAdd and ProjDateSub functions.
See Also
Reference
Concepts
Local and Enterprise Custom Fields
Rollup and Roll-Down Custom Fields