Advanced search - problems with decimals

[mariluzrm]

Hello.

I have a list made from database table (not a fabrik table). The table has a float(7,2) field called
Length. I have a record with value 66.66 but in advanced search with:

Length EQUALS 66.66

Can't find the record

Instead:

Length GREATER THAN 66
AND
Length LESS THAN 67

Find the record

What about decimals? Are there any limitations?

Thanks.

 

[juuser]

What is this element's "Decimal point" in field element settings (assuming it is a field element)?

 

[mariluzrm]

Hello
It is a point.


Thanks

 

[juuser]

Hmm, cannot replicate with field settings:

Format: Decimal
Integer length: 11
Decimal length: 2
Number Format: Yes
Decimal point: .

Does it work with other "native" Fabrik tables?

 

[mariluzrm]

Hello.
It's weird, I'm testing and for 66.66 it doesn't work and for 36.50 it does. It looks like it's the second decimal ... but no, because for 66.60 it doesn't work... For 33.30 it doesn't work

 

[juuser]

That is odd indeed. Cannot come up with any reason. All I can think of you can try to change the field type from float to decimal and see if that makes any difference.

 

[mariluzrm]

try to change the field type from float to decimal

With decimal(7,2) the value 66.66 works!!
I will do more tests. Thanks!!!
For native Fabrik tables, if I choose "Field" as element, where can I set the database field type as decimal(x,y), int(4), char(30), etc.??

 

[juuser]

Great! To change the field element type e.g to decimal(x,y), go to field element settings -> Formatting and change "Format" to "Decimal" and integer / decimal length according to your need. After saving the element, Fabrik asks if you want to change the database field type, just confirm that.

For Fabrik to automatically alter field types when saving elements, "Alter field type" in Fabrik's Global options and List settings has to be set accordingly. Fabrik alters the types by default. So if you haven't changed this settings yourself, you shouldn't need to do anything with that.

 

[troester]

Maybe some internal representation/rounding issue.
What is your "Exact match" setting in element's filter settings?

 

[mariluzrm]

Maybe some internal representation/rounding issue.
What is your "Exact match" setting in element's filter settings?

Exact match = Yes

 

[henk]

My guess:
If you use a float, then your value is not 66.66, but 66.6599999999999999
So if you will not be able to find 66.6600000000000000
The FLOAT and DOUBLE types represent approximate numeric data values.

 

[mariluzrm]

It is possible, thank you very much