Rounding issues from SQL to FileMaker

[bungi]

I am having rounding issues with MS SQL Server tables linked as an External Data Source. The decimal(18,4) field in the SQL table contains "118.4897", but in the FileMaker layout, it displays "118.49". I checked the data formatting settings of the field, set the format to Decimal and the set the "Fixed number of decimals" to 4, but it still rounds up and only displays "118.4900".

Also, if I enter a new value into the FileMaker layout, say for example "123.4567", it will save "123.4567" into the SQL table and it will display "123.4567" in the FileMaker layout. But if you switch layouts and then go back, or refresh the page, it displays "123.4600".

I know one workaround would be to change the field type from decimal to varchar, but I would rather not.

Can anyone help with this rounding problem?

 

 

some background on my system:

FileMaker Server 11 Advanced (version 11.0.3.309) on Windows Server 2008 R2 (64-bit)

MS SQL Server 2008 R2 on Windows Server 2008 R2 (64-bit)

[Trey Caliva]

What ODBC driver are you using to connect?

 

I'm not to familiar with MS SQL.  If I were going to try to debug this on MySQL I would flush my General Query Log and use that to see what commands FileMaker/ODBC driver is sending.  Might help you determine if there's something you're overlooking.

[mr_vodka]

I have tested it on both MSSQL and MySQL ESS tables. They both work as it should.

 

By any chance, in your ODBC settings do you have it to use the regional settings for currency, number, dates, etc? Turning this option on will truncate to 2 decimals.