#ERROR values in custom fields with a formula

dg2358 · April 30, 2025, 3:08pm

We’ve encountered a problem with formula-based extended attributes: sometimes one or multiple fields are saved with “#ERROR” value in every task. I’ve been trying to reproduce the issue, and it turns out that even example code provided here leads to to the same outcome.

Here’s a slightly modified piece of the example code that causes error values to appear:

Code

void FormulaWithProjectFields()
{
    var project = CreateTestProjectWithCustomField();
    var task = project.RootTask.Children.GetById(1);

    // Set formula for extended attribute
    var extendedAttributeDefinition1 = project.ExtendedAttributes[0];
    extendedAttributeDefinition1.Alias = "Custom";
    extendedAttributeDefinition1.Formula = "[Deadline] - [Finish]";

    // Set Task Deadline and save project
    task.Set(Tsk.Deadline, new DateTime(2015, 3, 20, 17, 0, 0));
    project.Save("project.mpp", SaveFileFormat.Mpp);
}

static Project CreateTestProjectWithCustomField()
{
    var project = new Project();
    project.Set(Prj.StartDate, new DateTime(2015, 3, 6, 8, 0, 0));

    // Add new task with extended attribute
    var task = project.RootTask.Children.Add("Task");
    var extendedAttributeDefinition = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Text, ExtendedAttributeTask.Text5, "My Ext Attr");
    project.ExtendedAttributes.Add(extendedAttributeDefinition);
    var extendedAttribute = extendedAttributeDefinition.CreateExtendedAttribute();
    task.ExtendedAttributes.Add(extendedAttribute);

    return project;
}

In MS Project after opening the .mpp file and adding the field to the view:
field_before.png (3.4 KB)

Opening the edit dialog and re-saving the formula causes values to be displayed correctly again:
pressing_ok.png (4.2 KB)
field_after.png (3.4 KB)

Project file (it’s unmodified, so a column with the custom field has to be inserted):
project.7z (13.5 KB)

Aspose.Tasks 25.4.0

Professionalize.Discourse · April 30, 2025, 3:09pm

@dg2358

The issue you’re encountering with formula-based extended attributes resulting in “#ERROR” values can be related to how the formulas are being evaluated in the context of the project data. It appears that the formula you are using, which calculates the difference between the [Deadline] and [Finish] fields, may not be properly resolving due to the state of the task data at the time of evaluation.

Here are a few suggestions to troubleshoot and potentially resolve the issue:

Ensure Valid Dates: Make sure that both the [Deadline] and [Finish] fields are set with valid date values before the formula is evaluated. If either of these fields is null or not set, it could lead to an error.
Check Formula Syntax: Verify that the formula syntax is correct. The formula you provided seems correct, but ensure that there are no extra spaces or incorrect characters.
Update to Latest Version: If you are using an older version of Aspose.Tasks, consider updating to the latest version. There have been fixes in recent updates that address issues with calculated fields and their evaluations. For instance, issues related to differences in calculated custom fields have been addressed in version 22.3 and later.
Re-evaluate After Saving: As you noted, opening the project in MS Project and re-saving the formula resolves the issue. This suggests that the formula might need a re-evaluation after the project is saved. You could try programmatically triggering a re-evaluation of the formulas after setting the task attributes and before saving the project.
Debugging: Add logging to check the values of [Deadline] and [Finish] right before the formula is evaluated. This can help identify if the values are as expected.

Here’s a modified version of your code that includes a check for the values before saving:

void FormulaWithProjectFields()
{
    var project = CreateTestProjectWithCustomField();
    var task = project.RootTask.Children.GetById(1);

    // Set Task Deadline and Finish
    task.Set(Tsk.Deadline, new DateTime(2015, 3, 20, 17, 0, 0));
    task.Set(Tsk.Finish, new DateTime(2015, 3, 18, 17, 0, 0)); // Ensure Finish is set

    // Set formula for extended attribute
    var extendedAttributeDefinition1 = project.ExtendedAttributes[0];
    extendedAttributeDefinition1.Alias = "Days from finish to deadline";
    extendedAttributeDefinition1.Formula = "[Deadline] - [Finish]";

    // Log values for debugging
    Console.WriteLine($"Deadline: {task.Get(Tsk.Deadline)}, Finish: {task.Get(Tsk.Finish)}");

    project.Save("project.mpp", SaveFileFormat.Mpp);
}

By ensuring that both fields are set and logging their values, you can better diagnose the issue. If the problem persists, consider reaching out to Aspose support with detailed information about your project setup and the specific versions you are using.

dg2358 · April 30, 2025, 3:13pm

It’s not related to formula syntax or null/incorrect values.
Even something as simple as this:

extendedAttributeDefinition1.Formula = "0";

Leads to the same result.

vasiliy.sinitsyn · May 5, 2025, 5:19pm

@dg2358 ,
we will investigate the scenario and return with the answer.

dg2358 · May 14, 2025, 12:24pm

@vasiliy.sinitsyn,
do you have any updates on the matter?

vasiliy.sinitsyn · May 14, 2025, 5:20pm

@dg2358 ,
the fix should be done on our side.

As a temporary workaround you could access ExtendedAttribute.IsErrorValue or *Value properties before saving the project:

var project = CreateTestProjectWithCustomField();
var task = project.RootTask.Children.GetById(1);

// Set formula for extended attribute
var extendedAttributeDefinition1 = project.ExtendedAttributes[0];
extendedAttributeDefinition1.Alias = "Custom";
extendedAttributeDefinition1.Formula = "[Deadline] - [Finish]";

// Set Task Deadline and save project
task.Set(Tsk.Deadline, new DateTime(2015, 3, 20, 17, 0, 0));

foreach (var ea in task.ExtendedAttributes)
{
    if (ea.AttributeDefinition.CalculationType == CalculationType.Formula)
    {
        Console.WriteLine("{0} : {1}", ea.FieldId, ea.IsErrorValue);
    }
}

project.Save("project_accessed.mpp", SaveFileFormat.Mpp);

vasiliy.sinitsyn · May 14, 2025, 5:25pm

@dg2358
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): TASKSNET-11478

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

dg2358 · May 15, 2025, 8:29pm

@vasiliy.sinitsyn,
thanks, that workaround fixed some of the fields. Unfortunately, some of them still don’t function correctly. I’m not sure if it’s the same bug, but it’s formula-related nonetheless.

Here’s the code reproducing the issue:

Code

static void FormulaWithProjectFields()
{
    var project = CreateTestProjectWithCustomField();

    WorkaroundFix(project);

    project.Save("project.mpp", SaveFileFormat.Mpp);
}

static Project CreateTestProjectWithCustomField()
{
    var project = new Project();
    project.Set(Prj.StartDate, new DateTime(2015, 3, 6, 8, 0, 0));

    var task1 = project.RootTask.Children.Add("Task1");
    var task2 = project.RootTask.Children.Add("Task2");

    var date1 = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Date, ExtendedAttributeTask.Date1, "Date1");
    project.ExtendedAttributes.Add(date1);

    var date2 = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Date, ExtendedAttributeTask.Date2, "Date2");
    project.ExtendedAttributes.Add(date2);

    var number14 = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Number, ExtendedAttributeTask.Number14, "Formula1");
    number14.Formula = "Fix([Start] - [Date1])";
    project.ExtendedAttributes.Add(number14);

    var number17 = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Number, ExtendedAttributeTask.Number17, "Formula2");
    number17.Formula = "Fix([Start] - [Date2])";
    project.ExtendedAttributes.Add(number17);

    task1.ExtendedAttributes.Add(date1.CreateExtendedAttribute(DateTime.MinValue));
    task1.ExtendedAttributes.Add(date2.CreateExtendedAttribute(DateTime.MinValue));
    task1.ExtendedAttributes.Add(number14.CreateExtendedAttribute());
    task1.ExtendedAttributes.Add(number17.CreateExtendedAttribute());

    task2.ExtendedAttributes.Add(date1.CreateExtendedAttribute(DateTime.MinValue));
    task2.ExtendedAttributes.Add(date2.CreateExtendedAttribute(DateTime.MinValue));
    task2.ExtendedAttributes.Add(number14.CreateExtendedAttribute());
    task2.ExtendedAttributes.Add(number17.CreateExtendedAttribute());

    return project;
}

static void WorkaroundFix(Project project)
{
    var taskCollector = new ChildTasksCollector();
    TaskUtils.Apply(project.RootTask, taskCollector, 0);

    foreach (var task in taskCollector.Tasks)
    {
        foreach (var extendedAttribute in task.ExtendedAttributes)
        {
            _ = extendedAttribute.IsErrorValue; // Temporary fix
        }
    }
}

The values are not “#ERROR” now, but they are incorrect.
after_adding_fields.jpg (8.8 KB)
After setting Date1 and Date2 values the second formula field recalculates its values correctly, but the first one does not.
after_setting_dates.jpg (10.9 KB)
Inserting a new task leads to “#ERROR” values appearing again.
after_insetion.jpg (12.2 KB)

I guess, it has something to do with the way Aspose handles null values in formula calculations because changing DateTime.MinValue to e.g. DateTime.Now eliminates issues #1 and #2. However, our use-cases rely on some of those dates being (possibly) null, and MS Project allows it as well, so it has to be an unintended behavior.

Also, I noticed that changing Number14 and Number17 to Number1 and Number2 respectively magically fixes everything (but in our case those are already reserved for other data).