Share via

Decimal Managed property-- Issues and workarounds

  • Article

There are two types of columns that are of type numeric

  • Number
  • Currency

Both these can be configured with Number of decimal places (Automatic, 0 or more). Irrespective what you chose, the crawled property that is created for this column is of type decimal. This can only be mapped to Decimal managed property.

 

This works out fine when you have no decimals in the numeric column value.

Following table shows the value in SP and its corresponding value in FAST Index. The column is defined with automatic number of decimal places.

 

Value in SharePoint

Value in FAST Index

812.24

812.24000000000001

812.98

812.98000000000002

814.1

814.10000000000002

815.9

815.89999999999998

816

816.

442.45

442.44999999999999

 

What does this mean?

This means you will not be able to run exact value queries on numerical columns. Let us say you have a SharePoint column called Amount Paid which is of type Currency. Assume that this is mapped to decimal managed property called "AmountPaid" .

Let us say you want to find all items where Amount Paid(Currency type column) is $21.25, you will not be able to get that with query AmountPaid=21.25

How to resolve this issue?

  1. FAST indexed values could be of the following format
    1. For value in SP Column 144.8
      • 144.8300000000000       OR  
      • 144.82999999999999
  2. When there is no decimal or has only zeros as decimal(all that it does is to convert as follows)
    1. For value in SP Column 820
      • 820.

 

 

Based on this we need to convert the queries as follows to resolve the issue

  1. Query to get documents with 120.24 as Amount Paid
    1. (amountpaid >120.235 AND amountpaid <120.245)
  2. Query to get documents whose Amount Paid is  greater than 120.24
    1. amountpaid >120.245
  3. Query to get documents whose Amount Paid is   lesser than 120.24
    1. amountpaid <120.235
  4. Query to get documents with 120 as Amount Paid (with no decimal places)
    1. amountpaid =120

 

Note:
Obviously this changes if the requirement is to get documents with 120.244 as Amount Paid. Then the query would be

(amountpaid >120.2435 AND amountpaid <120.2445)

 

 

What is DecimalPlaces setting on Decimal managed property? How does it affect this?

 

DecimalPlaces is setting on Decimal managed property. This is set to 3 by default.

This affects the behavior of Query related to decimals and not the value stored in FAST Index for decimal managed property.

 

Let us take above example.

Property name: DecimalProperty

Value: 144.123456

Corresponding FAST Index values: 144.123455999999999 OR 144.12345600000001

 For the purpose of this discussion, assume it is 144.123455999999999

 

Now let us look at the OOTB behavior

 None of the following queries return the above item

DecimalProperty >144.123455999999999

DecimalProperty >144.12345599999999

DecimalProperty >144.1234559999999

……..

DecimalProperty >144.123

 

But the following will return this item

DecimalProperty >144.122

This is because of the decimalplaces setting.As it was set to 3, we see this behavior. So once you change this to 4 you would see the following queries work.

DecimalProperty >144.123

DecimalProperty >144.1238

 

So you need to change this DecimalPlaces setting based on your requirement. This becomes increasingly important when dealing with geo spatial queries.