Number fields adding decimal places, changing exact values

[fotobymatt]

This problem has been bothering me for some time, but today it has reached a point where it affects the usefulness of one of my layouts. It is such a strange problem that I don't even know how to search for an answer, so I don't know what to do but post a question.

At any random time, an entry in a number field might change from 2 decimal places to 18 decimal places. For example, a field that I typed 94.23 into two days ago, now shows up as 94.230000000000003979. The data in question was originally entered in Filemaker, then exported to an Excel file as a backup and imported later. The Excel file was not opened in any other program prior to importing to Filemaker, and when I opened it in Excel to check the values, it showed 94.23. I even changed it to display as a number to 18 decimal places and it had no extra numbers after the initial 94.23.

Thus, this appears to be a weird bug when importing Excel files into Filemaker. When I look closely at the import field mapping, I can scroll through the numbers and I see them all at 18 decimal places, even though the Excel file does not contain those decimal places.

I am at a loss for what to do aside from creating a calculation to automatically round the imported data. Has anyone else had this problem? Any luck on fixing it?

[comment]

I have managed to duplicate the issue - but only by exporting to .xls and importing back. Entering 94.23 directly into the spreadsheet file and importing it seems to work fine. Same thing if the spreadsheet is re-saved.

Why do you need this? Excel is NOT a good format for backups. If you want a lean format to backup your text-only data, use the XML format.

[fotobymatt]

Thanks, that explains the problem. I haven't noticed it from Excel files unless they were exported by Filemaker, so I'll try exporting in XML and see if it works for me.

It's not so much a backup as a way to transfer records from one database to a temporary file that I use in a pricebook layout.

[comment]

That should work even better, since XML also preserves field data types (if you use the FMPXMLRESULT grammar).