Welcome back to the Tipping Truckstop, where the tipsters gather to discuss pressing CRM matters. It’s DeCRMber already and the trucks are all festively decorated for the holiday season, so grab a cup of eggnog and pull up a stool.

Today’s question is: In CRM 2013 and 2015, we have a field data type of “Floating Point,” and also “Decimal.” When is it best practices to use decimal, and when should you use floating point?

Joel “Tipping isn’t just for cows” Lindstrom: I’ve noticed that Floating Point cannot be used with calculated fields, so that would mean that if you need a calculated numerical field that is not whole number, you need to use decimal.

Feridun “Bond” Kadir: Floating Point uses a little less space, calculates slightly faster but at the price of loss of accuracy in the last decimal place. Most times this doesn’t matter, but if you need accuracy to the 10th decimal place then use decimal.

Adam “Beach” Vero: Generally floats are fine, use decimal for rocket science, quantum mechanics or quantities that might be very very small (I had a client recording water purity samples in a database once – they might have needed to record even 1 part per million or less of a toxic chemical). Note also if you might use queries based on equality, decimal is preferable. If using floats, try to use less/greater than or equals to avoid errors in that last decimal place. One thing I have always tried to bear in mind is if the field is involved in integration to/from another system I try to match data types to avoid type conversion (especially if it might be subject to round-trips that can exaggerate any inaccuracies).

Shan “Grillmaster” McArthur: The floating point number format was invented so that computers could work with very large and very small numbers (think of astronomical distances and sizes of atomic parts).  Floating point numbers are an approximation because they only have so many bits to store their data in.

The above screen shows that CRM does not know how to handle floating point numbers.  The first problem is that there is a precision here, but the precision here has the wrong implementation.  In SQL, the precision of a float is how many digits are in the mantisa using scientific notation for the number format.  In CRM, it is treating it like a decimal type and determines how many digits after the period.  This is wrong.   The second problem is that the CRM UI limits you to a maximum of 5 decimals for precision. This goes against absolutely everything a floating point number requires.  Then consider that there is a maximum value.  Floating point numbers in SQL have a maximum value of 1.7976931348623158E+308 and have the smallest value of 2.23E-308.

Here is the maximum value in CRM.  Notice that you cannot even record the budget allotment that was just approved in the senate this week for the US budget:

This represents a maximum value of 1.0E11.  That is *much* smaller than 1.79E308.

Then let’s look at the CRM UI.  The CRM UI can handle it if you enter a float in scientific notation, but it will change it to a standard decimal format. So 3E5 will get changed to 300,000.00.  1E-1 will get changed to 0.1.  Unfortunately, the limitations of large and small numbers that are imposed on us in the configuration screen are definitely there.  Entering 1E-5 will change to 0.00  1E15 will trigger an error.

The good news is that the value is still stored in SQL using a float column.  It is good that it is stored in a float because that means that the only bug is all of the display logic and data modelling that goes along with it, but that the underlying database is capable of storing that number.

Here is the connect item that I put in for this issue back in 2011: https://connect.microsoft.com/dynamicssuggestions/Feedback/Details/677405

My guidance is that float has more restrictions than decimal, so use a decimal instead.

David “Straw” Berry: I completely agree on all points, floating or otherwise.

George “Untalkative Bunny” Doubinski: More eggnog please.

Tipp Jarr’s Double Dipp

That’d be all, folks, for 2014. It’s been a fantastic year despite being a Green Wood Horse one. Tipsters celebrated a year in business that overflowed the byte originally reserved for the post count. CRM 2015 is alive and kicking and we can all take a well-deserved break only to spend it dissecting and analyzing the new goodies.

Tipsters will be back on January, 5th, 2015 with another year of tips, tricks and truckstops. Drink safely and drive in moderation.

The Custodians of The Tipping Jar, bringing you tips by a truckload