Error The field has an invalid XAML formula definition - Calculated column with nested IF conditions

(0) Share

Report

Posted on by HMJ_Somerset

118

I am trying to create a calculated column the sets the status of a contract depending on the Contract End Date field.

It is a single line text field

I can enter one condition: If Contract End Date does not contain data then Set Contract Status to "no contract end date set"

But when I add an Else If condition I get the ContractStatus field has an invalid XAML formula definition.

What am I doing wrong?

ApppsApp_XAML error.png

I want to get to the following condition:

If Contract End Date does not contain data then Set Contract Status to "no contract end date set"

Else If Contract End Date <= Today then "Contract expired"

Else If Contract End Date > Today && <= Today + 9 months then "Contract expires within 9 next months"

Else "Contract Active"

I have created calculated columns in the same contracts table for the value of Today, Today() and Today + 9 months, DateAdd(Today(),9, TimeUnit.Months)

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

MarioRing 541 Super User 2024 Season 1 on at

Like (0)

Report
There are two different features of the Dataverse:
Calculated Columns - which use a condition editor, like on your screenshot
Formula Columns - which use PowerFx language
The second option is currently in the preview stage, which means it is not recommended to use it for production environments, and may not work properly in some cases. It looks like you can't combine both types of fields (I've checked a similar scenario on my environment and got the same error).
What you can do:
Create one complex formula field using several nested PowerFx formulas to generate the desired output
OR
Create scheduled power automate flow, which queries every day for Contracts, where "End date" == now(), and changes their statuses.

Was this reply helpful? Yes No
HMJ_Somerset 118 on at

Like (1)

Report

Thanks for the advice.
I have created a new column and used a Power fx formula to set the status:
If(IsBlank('Contract End Date'), "No end date set",
    If('Contract End Date' <= UTCToday(), "Contract Expired",
        If(And('Contract End Date'>= UTCToday(),
        'Contract End Date' <= DateAdd( UTCToday(),9,TimeUnit.Months)
),
        "Contract ends in 9 months",
    "Contract Active")))

Was this reply helpful? Yes No