7 Replies Latest reply on Jun 25, 2010 1:53 PM by tyt3

    Identical numbers won’t relate (Excel/FM issue)

    LaRetta_1

      Summary

      Identical numbers won’t relate (Excel/FM issue)

      Description of the issue

      FileMaker Product(s) involved:Several versions. I’ve tested 9, 10 and 11 Operating System(s) involved:Windows XP Professional 2002 SP2 Detail description of the issue:This may be similar (or same) as bug posted herehttp://forum-en.filemaker.com/t5/Report-a-bug/Upon-Excel-file-being-created-any-post-formatting-to-the-Excel/m-p/35798/highlight/true#M4281 Exact steps to reproduce the issue:Download this file. I have done the work for you.  There is a New Microsoft Excel Worksheet.xls used for Test2.  I typed the number manually and did not change formatting.  There is an Excel Tests.doc which explains the tests in detail and also points out a pattern I observed (although the pattern makes no logic).  And there is the ExcelTest.fp7.  You can run Test 1 and Test 2 and see the problem immediately. Expected Result:If I SaveAsExcel a decimal number, such as 208.02 and then import it back into FileMaker, the result should be 208.02 Actual Result:The actual result is 208.02000000000001 and it breaks relationships based upon these numbers Exact text of any error message(s) that appeared:None Any additional configuration information/troubleshooting that is relevant to the issue:I have included everything within one easy file.  This fp7 was newly created after I identified the issue in other files. Any workarounds that you have found:None In the attached zip, an Excel Tests.doc explains the testing process and any observed patterns of the results.  The New Microsoft Excel Worksheet.xls was newly created and I manually typed the serial and numbers into the fields.  No formatting whatsoever was applied to this worksheet. This is very serious break.  Our account numbers will not relate.  I’ve been reading posts of people saying they were having problems with relationships based upon numbers and most of the posts never found a resolution to their issue.  This very-well could have been what was happening to them as well.  This error will also make summaries off.  It must be fixed immediately. Neither of the FMI techs I spoke with had heard of this issue even after reviewing the file.  I have opened a case #100623-000214.  Also, I had searched at great length for floating decimal issues or Excel number or decimal issues on Knowledge Base and could find nothing.  No real surprise, unfortunately.

        • 1. Re: Identical numbers won’t relate (Excel/FM issue)
          philmodjunk

          That appears to be the same bug reported here: 

          Import from Excel decimal errors

          • 2. Re: Identical numbers won’t relate (Excel/FM issue)
            LaRetta_1

            Indeed it does.  Another example of three things:

             

            1) Knowledge Base isn't knowledgeable.

            2) FMI techs don't know what they should know and it is another example that they are charging people when they should not.  True, we have Priority Support with Contract but how many have been charged when they should not have been charged.

            3) Things that cause serious issues aren't being fixed - that post was 18 months ago.

             

            I have spent a lot of my time researching what was breaking, why it was breaking and doing all the work for FMI.  And I assume that Gimble and Leontine did as well.  We all wasted our time because FMI will not make the information known ... not even to their own tech support people.  Nor will they put the information up on Knowledge Base.

             

            I read 99% of all posts and I still missed that single post?  GIVE US A BREAK FMI!!!!

             

            What is even more ridiculous is the solution given by TSGal "Until then, use tab-separated text to get exact results."  So we shouldn't use SaveAsExcel either and it doesn't stop Excel worksheets being imported from breaking (as in my example which I hope TSGal will at least confirm and FMI will at least review).  It should be published!! 

             

             

            • 3. Re: Identical numbers won’t relate (Excel/FM issue)
              LaRetta_1

              Well now I've heard it all.  Tech Support called me back after checking up line and referenced this link:

               

              http://help.filemaker.com/app/answers/detail/a_id/7090/kw/7090

               

              They said that it is NOT A BUG ... that it is expected behavior.  He said we should not use Excel if we need decimals to maintain their values.  I explained that if we export 208.02, we should be able to import 208.02 and it most certainly is a bug when it imports 208.0299999999999901.  He said it is not a bug and they have no plans to fix it.

               

              He asked why we were using Excel?  I told him most businesses use Excel!!!  He said we shouldn't use Excel if we are using decimals.  OH dear ... like that makes a lot of sense?  So we can't SaveAsExcel if we are using decimals nor can we import Excel spreadsheets with numeric or decimal values because they will break.  Shame on us for thinking we can use Excel.  After all, businesses NEVER USE DECIMALS ... businesses NEVER use Excel for money or calculations!  Silly us!!!

               

              So just to be clear ... if you have numbers with decimals or money, don't use Excel (either SaveAsExcel or importing from Excel).  Ever.  And I will stop now or I'll say something I REALLY want to say.

              • 4. Re: Identical numbers won’t relate (Excel/FM issue)
                philmodjunk

                LaRetta, I assume you know this "fix" but for those reading this thread that might not...

                 

                One possible "fix" for this issue would be to include a "post import" operation using replace field contents and the round function to clean up these values so that they have the correct digits. This won't work for all possible situtations, but if the values have a fixed number of possible decimal places, it should work.

                • 5. Re: Identical numbers won’t relate (Excel/FM issue)
                  LaRetta_1

                  "but if the values have a fixed number of possible decimal places, it should work."


                  It doesn't. The link Tech Support provided says, "It has been found that, after an import of number values denoted with scientific notation that was created in a Microsoft Excel document, decimal numbers are inconsistent from the original data entered."


                  Open the files I presented.  A brand new Excel spreadsheet (I even gave you one in the folder with all of the numbers to test) - I did all the work for everyone - type 208.003 and import it.  It becomes 208.00299999999999!  208.003 is NOT scientific notation.   So what is scientific notation?


                  Scientific notation, also known as standard form or as exponential notation, is a way of writing numbers that accommodates values too large or small to be conveniently written in standard decimal notation.


                  Does 208.003 fit that criteria?  Think this through, people. If you use Excel at all, download the file and at least protect yourselves by understanding everything involved in this nasty behind-the-scenes issue. 


                  It is not as simple as rounding either, Phil.  Rounding requires specifying the number of decimals.  Direct import from new Excel does not break with 2 decimal but breaks with 3 or more (see my file).  I cannot run a script through all imported records to ‘round’ them because I must test how many decimals each record possesses.  Why would I need to do this anyway?


                  Rounding aside … if I have a general ledger number of 308.111 and I want to import update information from Excel spreadsheets using the ‘update’ match, it will not update the information in FM which will also show 308.111 because FileMaker does not see them as equal.  And if we’ve exported from FM first (using SaveAsExcel) then ALL decimals will break when imported back in when attempting to match (except for every fourth number, as my file shows).


                  Pre-processing?  FMI says that it works so well with Excel they provide 'SaveAsExcel' to encourage we share data between the two.  You and I and everyone knows this.  But then they turn around and say, "well, if there are decimals, don't use Excel."  HUH?  The tech I spoke with, who had the nerve to ask, "why are you using Excel?" has obviously never worked in a business.

                   

                  If this junk does get in our files, we can’t even find it again.  We look for 208.04 as a dollar amount but it’s become 208.03999999999999.  The problem is that most number fields in FM are left on default as ‘General’ and people won’t understand why it is breaking because it will show 208.04.  And even now, people’s summaries will be off and they won’t even know it. Relationships based upon non equijoin will also group improperly.

                   

                  How many people (and Developers and businesses) are importing from Excel thinking they are fine?  THEY ARE NOT.  I cannot give an Excel file to a manager with dollar amounts (SaveAsExcel) and then pull that information back in without re-working it once back in FM.  What comes back in will be broken.  Not use Excel?  Get real, FMI!!

                   

                  This issue isn't simply that a number does or doesn't round (at the 14th position). This is not simple issue if number is scientific notation to begin with.  This bug breaks relationships; it breaks update imports; it breaks summaries, it leaves garbage in the fields and it makes finding impossible. 


                  Attempting to provide duct-tape answers isn't the answer.  Saying it simply needs a bit of pre-processing using Round() is duct-tape.  FMI should AT LEAST provide a warning if a SaveAsExcel identifies a decimal field on the layout.  At least throw an error if an import has decimal data.  But then ... we've been begging FMI to do what is right for quite some time.  The import bug still exists and there is nothing which protects us from it either. 

                   

                  If you will not FIX IT, at least INFORM US.

                  If you will not INFORM US, at least PROTECT US.  We deserve no less.

                   

                   

                  • 6. Re: Identical numbers won’t relate (Excel/FM issue)
                    philmodjunk

                    LaRetta,

                     

                    I think you misunderstood me.

                     

                    I was trying to say that if you know your values will have a known, fixed number of decimal places, you can use rounding to correct the discrepancy after import. If you don't, as I said in my last post, this won't work for you.

                     

                    As far as I know, all floating point numbers are internally stored in binary scientific notation and then displayed to us poor humans in standard decimal notation by the software. I could be wrong here, but I believe that's what the KB article is saying and that the differences in the two system's handling of the binary, scientific notation is the cause of the discrepancy.

                     

                    Since the difference here is quite small, rounding will often serve as an effective work around here--provided you can specify a reasonable level of precision.

                     

                    I wasn't trying to disagree with you here, just offer a potential work around and also to note its limitations.

                    • 7. Re: Identical numbers won’t relate (Excel/FM issue)
                      tyt3

                      FileMaker has greater floating point precision than Excel 2003 so exporting to Excel works as expected, so long as we don't exceed Excel's limits. From Excel back to FileMaker is where we run into the problem. My analogy is reducing a photo produces a smaller but otherwise seemingly identical picture, but enlarge it enough and it will degrade significantly. IMO FileMaker is doing the correct thing by matching the value it recieves from Excel with the greatest precision, but in doing so it must interpolate the best match, much like enlarging a digital photo.

                       

                      To LaRetta's position, using Excel functionally as a temp file like in her example the end result of the subtle transformation in data does seem illogical. I am leaning toward the notion this is a bug because there is no warning or notification when FileMaker interpolates data on import from Excel.

                       

                      Potential Work-Arounds...

                       

                      An ODBC Connection to Excel is sending / receiving decimal numbers of variable lengths correctly so long as the numbers are within the limits of Excel.  So receiving the data via ODBC seems as viable an option as CSV files.

                      Also numeric data in Excel converted to text before importing into FileMaker numeric field work flawlessly.

                      Convert a decimal number field to text in Excel 2003 by selecting Data -> Text to columns..., then follow the dialogue boxes and choose "text" as the data format. Both these implementation essentially resolve us back to text files, so not using Excel 2003 for this type of process if possible seems like the easiest work-around.

                       

                      Following the export to Excel then re-import into FileMaker approach with a column of non-fixed number of decimal places, we could calculate the number of digits after the decimal and export the calc field with the rest of the data, then upon re-import we could round the original number back to it's original precision.

                       

                      Conclusion

                      FileMaker might discontinue support for Excel 2003 importing or program a warning mechanism which is triggered on import of number fields containing decimal or floating point values.