Expression returns NaN when one of arithmetic operands is missing from data source

D365FO
,
1

When we generate the DDSP file for a certain report, all fields are in the resulting file. However, during report runtime execution, if a field doesn’t have a value and is not instantiated with either empty or placeholder value, the field will be entirely omitted from the data source. This means that conditional statements that reference the omitted field, might in some cases return NaN results depending on how the conditional statement is written.

For demonstration purposes, we have two fields, @Field1 and @Field2, and we use them in the following expression:

iif(@Field1 = ‘’, @Field2, @Field1 + @Field2)

At first, this expression makes perfect sense. When @Field1 is empty, show @Field2; otherwise, perform a simple arithmetic operation. What could go wrong?

The difference between cases when @Field1 has a value of an empty string and when it is omitted entirely from the data source makes a big difference in XPath.

Let’s say that @Field1 is indeed omitted from the data source so this is what happens when the iif expression above is executed:

  1. XPath tries to evaluate whether @Field1 is equal to an empty string, and since it can’t find it to perform the evaluation, it returns false
  2. False means that the expression @Field1 + @Field2 will be executed
  3. Now XPath wants to perform an arithmetic operation and wants to do it with numeric values
  4. Since @Field1 is non-existent and thus does not have a numeric value nor can it be converted to a numeric value, the first operand in this case is NaN (Not a Number)
  5. Any arithmetic operation where at least one of the operands is NaN is equal to NaN therefore this expression outputs NaN

What can we do about it?

It turns out that there is a simple way to account for these scenarios by adjusting the evaluating condition. If we would have the same expression written as:

iif(@Field1 != ‘’, @Field1 + @Field2, @Field2)

Let’s now see what happens when @Field1 is again omitted from the data source but now using this updated expression:

  1. XPath tries to evaluate whether @Field1 is not equal to an empty string, and since it can’t find it to perform the evaluation, it returns false
  2. This time False means that the expression @Field2 is executed
  3. We get the correct output

It is important to differentiate between cases:

  • when a field exists and has a certain value,
  • when a field exists and has an empty value, and
  • when a field does not exist.

These cases need to be considered when creating and troubleshooting XPath expressions.