Working with Formulas

Aspose.Tasks for .NET API supports reading/writing formulas to MPP project files. The Formula property of the ExtendedAttributeDefinition provides the interface for reading the formula value. This article describes how to read formulas from local as well as Enterprise Extended Attributes from the MPP file. It also shows how to write formulas to MPP file.

Writing Formulas in Extended Attributes to MPP File Formats

You can define custom formulas and assign them to Extended Attributes in a project. Aspose.Tasks writes these formulas to MPP files while preserving compatibility with Microsoft Project.

Project project = new Project("New Project.mpp");
project.Set(Prj.NewTasksAreManual, false);

// Create new custom field (Task Text1) with formula which will double task cost
ExtendedAttributeDefinition attr = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Text, ExtendedAttributeTask.Text1, "Custom");
attr.Alias = "Double Costs";
attr.Formula = "[Cost]*2";
project.ExtendedAttributes.Add(attr);

// Add a task
Task task = project.RootTask.Children.Add("Task");

// Set task cost
task.Set(Tsk.Cost, 100);

project.Save("WriteFormulasInExtendedAttributesToMPP_out.mpp", SaveFileFormat.MPP);

Reading Formulas in Local and Enterprise Extended Attributes from MPP file

Aspose.Tasks provides access to formulas defined in both Local and Enterprise Extended Attributes. This makes it possible to programmatically extract and analyze custom business logic embedded in MPP files.

Project project = new Project("New Project.mpp"); // Attached test mpp
// Read extended attribute formula
ExtendedAttributeDefinition attr = project.ExtendedAttributes[0];
Console.WriteLine("Attribute Formula: " + attr.Formula);

Read-Only Access to Custom Field Values when Using Formulas

When formulas are applied to Extended Attributes, the resulting custom field values are read-only. Aspose.Tasks ensures that calculated values remain consistent with the formulas defined in the MPP file.

// Create new project and extended attribute definition
Project project = new Project();

ExtendedAttributeDefinition attribute = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Cost, ExtendedAttributeTask.Cost1, "");
attribute.Formula  = "[Cost]-[Actual Cost]";

project.ExtendedAttributes.Add(attribute);

// Add task
Task task = project.RootTask.Children.Add("Task");

// Create extended attribute
ExtendedAttribute extendedAttribute = attribute.CreateExtendedAttribute();
task.ExtendedAttributes.Add(extendedAttribute);

// Display if extended attributes are read only or not
Console.WriteLine(extendedAttribute.ValueReadOnly == true ? "Value is Read only" : "Value is not read only");
extendedAttribute.NumericValue = -1000000M;
Console.WriteLine(extendedAttribute.NumericValue == -1000000M ? "Formula values are read-only" : "Values are not read-only");