Unanswered

Validation Error for Calculated Column in Dataverse – Exceeding Value Range

Like (0) Share

Report

Posted on 10 Mar 2024 22:00:18 by FarmaVIP

I'm facing a validation error in Dataverse when trying to store values in a calculated column named 'Margem'. The error indicates that the value is outside the valid range. Here's the error message I received:

The 'Margem' column is calculated with the following formula:

Margem = [Valor_Venda_S_IVA] - [Preco_Custo_S_IVA_produto]

To prevent values from exceeding the maximum allowed range, I've attempted to introduce an if statement to limit the output of the formula. Despite this, the error suggests that the calculated value is reaching 2.42E+17, which is significantly higher than the maximum permitted value of 1,000,000,000.

Additionally, I've sorted the 'Margem' values in both ascending and descending order to identify any abnormal values that might correspond to the error message's value size, but this has not clarified the issue.

This seems to be a straightforward calculation, yet I am encountering this error on four different columns that perform similar calculations. I'm at a loss as to why this is happening.

Has anyone encountered this issue or can anyone advise on a solution? Any insights or suggestions to resolve this validation error would be greatly appreciated.

Categories:

Microsoft Dataverse

Tables

I have the same question (0)

All responses (5)

Answers (0)

Drew Poggemann 9,278 Most Valuable Professional on 12 Mar 2024 at 11:14:26

Like (0)

Report

Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

Hi @FarmaVIP ,

The issue looks to be with your import process in Power Query if the number is coming in without the decimal separation. The setting with the comma should be fine if you have your environment set with that configuration. I would focus on your dataflow (if that is what you are using) and see if it is an error in the logic.

Also, your calculated field is setup with 0 as a lower bounds, based on your first error screen, you will want to set this to a negative value since you have negative values coming in from your source.

Was this reply helpful? Yes No
Jonathan Manrique 2,682 on 12 Mar 2024 at 07:48:31

Like (0)

Report

Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

Hi @FarmaVIP

As you mention, it depends on the regional configuration and must be set by default to the creation language of the environment, so if the environment the language is Portuguese and the regional configuration is also Portuguese, it should have that behavior.

However, each user can change that configuration, you must validate that the users are using the corresponding configuration.

It is more an issue of data manipulation than of the system, if they are placing , or . It makes a big difference. I am in Spain and we use a comma and I have never had that problem

Was this reply helpful? Yes No
FarmaVIP on 11 Mar 2024 at 21:40:19

Like (0)

Report

Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

I've tried the approaches suggested by@dpoggemann @Jmanriquerios
After checking the original columns and checked the values in the dataverse table, I confirmed there are no nulls/errors or excessively large values. Following some tests, I believe the issue may be related to the commas.

Here's an example demonstrating the problem in Power Query.
The error number in my Power Query is twenty-one thousand one hundred fifty-two point fifty-two (21152,52 ). However, it becomes twenty-one million one hundred fifty-two thousand five hundred fifty-two ( 2115252) when it enters the Dataverse.

I am from Portugal and my setting for my environment are these.
In Portugal, we use commas as the decimal separator in numbers.
Any way to fix this?

Was this reply helpful? Yes No
Drew Poggemann 9,278 Most Valuable Professional on 11 Mar 2024 at 11:27:59

Like (0)

Report

Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

HI @FarmaVIP ,

Have you looked at the values for the Valor_Venda_S_IVA and Preco_Custo_S_IVA_produto in Dataverse? I see you are show the values in the image above from it looks like Excel. Wondering if you could show the values in Dataverse that are causing this failure? I am expecting it has to do with the data import process where a field is getting set to a value you are not expecting...

Was this reply helpful? Yes No
Jonathan Manrique 2,682 on 11 Mar 2024 at 08:36:34

Like (0)

Report

Re: Validation Error for Calculated Column in Dataverse – Exceeding Value Range

Hi @FarmaVIP

Hello, maybe you could change the approach you are using and in this way validate the same but on the side of the columns that you use for the calculation. When you create the columns you can limit the minimum and maximum value, something that you cannot do in the calculated ones.

So what I would do is limit the values in the columns that I need for the operation and this way when I do the operation through a calculated column I will no longer need to validate because I did it previously

Was this reply helpful? Yes No