Writing Formulas in Custom Fields

Aspose.Tasks Product Family
1

Hi,



I’m trying to add a formula to a custom field as outlined in Aspose documentation sample. The formula is added, however, when the project file is opened the field (Text1) where the formula was attached is showing “#ERROR”. It remains this way until I alter the cost value or recalculate the project. Is there any way to prevent “#ERROR” in the custom field as I would not want the end user to have to recalculate the project when first opened? See code below:



Project _project = new Project(“C:\WBSTemplate1.mpp”);// Create a new project task

_project.Set(Prj.NewTasksAreManual, false);



// create new custom field (Task Text1) with formula which will double task cost

ExtendedAttributeDefinition attr = new ExtendedAttributeDefinition();

attr.FieldId = ExtendedAttributeTask.Text1.ToString(“D”);

_project.ExtendedAttributes.Add(attr);



//

attr.Alias = “Double Costs”;

attr.Formula = “[Cost]*2”;



// add a task to see the result in MSP

Task task = _project.RootTask.Children.Add(“Task”);

// set task cost

task.Set(Tsk.Cost, 100);





// The result of opening of saved project in MSP2010 is in attached screenshot

_project.Save(“C:\WBS\LIVE\1116_16\Project1.mpp”, SaveFileFormat.MPP);



I’ve downloaded a trial version of 9.2.1 of which I have a temporary license for evaluation. I’m using VS 2010 and MS Project 2010.





Thanks, Calvin

2

Hi Calvin,


Thank you for posting your inquiry.

We have tested this issue at our end and it seems to be a bug to us in the API. We have logged it as TASKS-34480 in our bug tracking system for further investigation by our Product team and shall update you here once there is some information available about this problem.
3

Hi Muhammad,


Are you able to tell me if TASK - 34480 will be addressed in the upcoming release (9.3.0)?

Thanks, Calvin
4

Hi Calvin,

We have investigated this issue and would like to share with you that Microsoft Project automatically calculates
extendedAttribute fields by formula,when you add Custom Fields. The same can be achieved by adding ExtendedAttribute to the task in Aspose.Tasks. Please try the following code sample for your kind reference.

Sample Code:

Project _project = new Project(“C:\WBSTemplate1.mpp”);// Create a new project task



_project.Set(Prj.NewTasksAreManual, false);

// create new custom field (Task Text1) with formula which will double task cost



ExtendedAttributeDefinition attr = new ExtendedAttributeDefinition();


attr.FieldId = ExtendedAttributeTask.Text1.ToString(“D”);


_project.ExtendedAttributes.Add(attr);


attr.Alias = “Double Costs”;


attr.Formula = “[Cost]*2”;


// add a task to see the result in MSP

Task task = _project.RootTask.Children.Add(“Task”);


// set task cost

task.Set(Tsk.Cost, 100);


//add ExtendedAttribute to the special Task

ExtendedAttribute taskExtendedAttribute = attr.CreateExtendedAttribute();

task.ExtendedAttributes.Add(taskExtendedAttribute);


// The result of opening of saved project in MSP2010 is in attached screenshot

_project.Save(“C:\WBS\LIVE\1116_16\Project1.mpp”, SaveFileFormat.MPP);

Thus, every field used in a formula needs to be initialized before saving to avoiderror in MS Project. For example,
if the formula looks like this:

myTaskDurattr.Formula = “[Number1]+10”;

then the value of Number1 needs to be added to the project to avoid error in MSP.

Sample Code:

ExtendedAttributeDefinition myTaskDurattr = new ExtendedAttributeDefinition();

myTaskDurattr.Alias = “New Duration”;
myTaskDurattr.CfType = CustomFieldType.Duration;
myTaskDurattr.FieldId = ExtendedAttributeTask.Duration10.ToString(“D”);

//using Number1 in fomula
myTaskDurattr.Formula = “[Number1]+10”;
project.ExtendedAttributes.Add(myTaskDurattr);

ExtendedAttribute taskAttrDur = myTaskDurattr.CreateExtendedAttribute();
task.ExtendedAttributes.Add(taskAttrDur);

ExtendedAttributeDefinition myNumber1 = new ExtendedAttributeDefinition();
myNumber1.Alias = “New Number1”;
myNumber1.CfType = CustomFieldType.Number;
myNumber1.FieldId = ExtendedAttributeTask.Number1.ToString(“D”);
project.ExtendedAttributes.Add(myNumber1);

ExtendedAttribute Number1Task = myNumber1.CreateExtendedAttribute();

Number1Task.Value = “10”; //set value
task.ExtendedAttributes.Add(Number1Task);
5

Hi Kashif,


I’ve tested using the sample code you’ve provided and the fields are calculating as expect. However, I’m still receiving the #ERROR value in the Start10 Extended Attribute that I’ve added.

I’ve attached the sample code (the addExtendedAttributes routine is only called for newly created nodes) along with the template file I use. When the output file is first generated the Start10 field is calculated correctly (showing a valid date for non summary rows - ensure the project path is set to Blank2010.mpp). When I then read the output file (rather then the template because at this point I’m updated the output file based on user changes and not starting from scratch with the template = ensure the project path is set to result.mpp) and re-save, the Start10 field value is set to #ERROR.

How should I handle subsequent updates to the output file to prevent the #ERROR value from appearing?

Thanks, Calvin
6

Hi Calvin,

Thank you for writing to us again.

I have checked the result.mpp file and observed that it shows proper Start10 value in MSP. Later I opened this result.mpp file and saved it again using Aspose.Tasks. This resaved-result.mpp file also displays proper Start10 value and no #ERROR is observed.

Could you please let us know that what steps do you perform after opening the result.mpp file in Aspose.Tasks before saving it again? Please send us the code which you use to update the file, so that we may test it here and provide assistance accordingly.


7

Hi Kashif,


See my steps below:

1) I generate the result.mpp file by clicking a create button which runs the code I provided.
2) I open the result.mpp file (via MSP not Aspose.Tasks), right click on the header and add the Start10 column so I can view the output.
3) I then close the result.mpp file without saving any of my changes.
4) I then change my code to read the result.mpp file. At this point because all task already exist in the result.mpp file the code only updates the Assignment Actual Work value.
5) I then open the result.mpp file again (via MSP not Aspose.Tasks), right click on the header and add the Start10 column so I can view the output.

At this point the Start10 is showing #ERROR rather then an actual date.

I’ve attached a word document showing what I see in MPP corresponding to the steps above. I’ve also included a screen shot showing the version of MSP that I’m using and the code I’m using was attached in my previous post (I have attached again, in case you were unable to access it).

I hope this helps you recreate the issue.

Thanks, Calvin
8

Hi Calvin,

We have thoroughly examined the code and observed that there is some issue with the formula for Start10 field while using duration in it. If you test the code with the following change, you will also observe the problem. I have logged it under Id TASKS-34536 for further investigation by the product team and you will be automatically notified once any update is received in this regard.

// NOT WORKING CODE

// myTaskStartattr.Formula = “IIf([Summary]=Yes,“NA”,ProjDateAdd([Project Start],[Duration10],“Standard”))”;

// WORKING CODE

myTaskStartattr.Formula = “IIf([Summary]=“Yes”,“NA”,ProjDateAdd([Project Start],“20d”,“Standard”))”;

9

Hi Kashif,


Will Task 34536 be addressed in the next release? If yes, any idea when the release will be available?

Thanks, Calvin
10

Hi Calvin,

The issue has been fixed and will be available in the next version of Aspose.Tasks for .NET API. We have requested an ETA from our Product team about this issue and will notify you here as soon as the information is available.

11

Hi Calvin,

This is to share with you that the fix version of API will be available at the end of this month i.e. April 2016. We’ll update you here as soon as the information is available in this regard.

12

Hi Kashif,


You mentioned in your previous post that the next release was scheduled for the end of April. Just wondering if you’re able to provide the exact date now that the end of April has come and gone.

Thanks, Calvin
13

Hi Calvin,


I am afraid that no new ETA is provided and new release is expected within few days. I shall write here as soon as any information is received in this regard.
14

The issues you have found earlier (filed as TASKS-34536) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.