Using Floating Point Values in Calculated Field

(0) Share

Report

Posted on by Community Member

Good day,

For a particular entity, I want to create a calculated field that needs to use two other floating point fields in its calculation.

Unfortunately, the two needed fields for the calculation are floating point data types. Furthermore, they are OOTB fields and depend on business logic behind the scenes; so they cannot be changed to the decimal data type.

As such, it seems like it is impossible to use these fields in the new calculated field. (I have tried converting them on the fly in the calculation but that doesn't work.)

Any solutions or pro tips to solve this problem would be much appreciated. (Or maybe, at least, an explanation as to why this limitation exists.)

Thanks,

BGS

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Sanjaya Prakash Pra... 2,745 User Group Leader on at

Like (0)

Report

Floating point numbers cannot be used in calculated fields. So for this you need to build custom logic using plugin or workflow.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the answer.

Though it seems incredibly unbecoming to have to build a whole plugin or workflow just to add two floating point numbers together...

What is the rationale for this limitation?

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Aiden Kaskela 19,696 on at

Like (0)

Report

Hi BGS,

Floating point numbers have their place but they don't handle base 10 calculations well (it's ComSci, not CRM). It has to do with how the values are stored and treated in calculations.

A floating point number is expressed in binary with enough positions to do the job. Decimal numbers take up more space in the database because they account for more.

Here's a real example that I had a few years ago that put me off floats forever. I was trying to subtract two float fields, and if the difference was .1, do something. If you have a decimal, you can do this:

.35 - .25 == .1 (and it's true)

If you're using floats, you'll get

.35 - .25 == .1 (and it's false)

The reason the float version is false is because it treats the numbers as binary and does the operation. If you pull up VS to calculate the fields, you'd get:

.35f - .25f = .0999999994 (or something close).

Floats suck for calculations we'd normally use because they can't handle decimal places the way a human would.

Hope this helps! I'd appreciate if you'd mark this as Answering your question.

Thanks,

Aiden

Was this reply helpful? Yes No
Verified answer

ashlega 34,477 on at

Like (0)

Report

You might probably create two decimal fields and a simple workflow / entity-scoped business rule to copy the values from those original floating-point fields into the new decimal fields. Then you should be able to use new decimal fields to define a calculated field

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hey Aiden,

That's a good point. But decimal still has the same precision problems that you outlined. (Admittedly, decimal representations run into this problem somewhat less often than binary representations, but both decimal and binary aren't guaranteed to be precise for all equations.)

Here's a quick example of what I mean(all three data types are decimal):

As exemplified above, decimals suck for calculations we'd normally use because they can't handle decimal places the way a human would.

If precision issues are the only reason floating point data types are banned from calculated fields then I suggest we get rid of decimal too in order to be consistent. ;)

BGS

1 people found this reply helpful.

Was this reply helpful? Yes No
Aiden Kaskela 19,696 on at

Like (0)

Report

Hi BGS,

There are cases where floats work fine, but CRM isn't one of them. Check out this write up for strengths of different field types: www.net-informations.com/.../float.html , and here's one replying to the technical differences: social.msdn.microsoft.com/.../can-anyone-explain-clearly-about-float-vs-decimal-vs-double-

Decimals offer greater precision (though a smaller range) than floats do. If you're doing scientific work then a float or double might be the way to go. Since you're using a CRM system though, Decimal would be more appropriate in almost every practical case.

Thanks,

Aiden

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Alex!

The solution was to create a workflow that updated the decimal fields anytime a field was changed or a new record was created. Pretty tedious for something that should be so simple, but it worked nonetheless.

Thanks again,

BGS

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hey Aiden,

Again, I agree decimals do indeed offer greater precision and have precision issues less often than floats do; that is true. (Especially when dealing with money.) But as I exemplified before, decimal data types still do have some precision problems! So for both floats and decimals the user has to deal unexpected results and small round off errors. Sometimes the user may just do nothing and except the issues, or maybe they can add some business logic to deal with it. But the point is that both decimal and floats have precision issues that the user may have to deal with. So I don't understand why Microsoft is picking on just floats.

If anything -- if floats are too imprecise for CRM -- it would be better to not even allow floats to be a data type in the first place. (Rather than allow them and then disable even the most basic arithmatic operations).

I hope you see where I am coming from,

Beckett

Was this reply helpful? Yes No
Aiden Kaskela 19,696 on at

Like (0)

Report

Hi Beckett,

I'm anti-float to a personal degree :) I *think* they're only in the base platform for latitude and longitude exclusively, and originally I don't think decimals supported enough places to fill out that data.

The calculated error you included is a good illustration of rounding issues in computers in general, but I don't see it as indicative of a deficiency with decimals. In your example (repeated here)

val1 = 1;

val2 = val1 / 3; // .33333333333

val3 = val2 * 3; // .99999999999

any primitive datatype will have the same result because there's a finite amount of bits to represent an infinite number (3 / 1).

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

Heh.. From a different standpoint.. If there were no floating type, Dynamics would be unable to represent "floating" data. Which is, mostly, ok.. But I have a feeling if it were not there, a case for floating would show up right away (it's not just CRM.. it's XRM, too. People are using Dynamics for all sorts of applications, so why not to keep it there). But then, again, those fields mentioned in this thread seem to be of the floating type out of the box, so there is no way to get rid of floating in this thread:))

Was this reply helpful? Yes No