10 Replies Latest reply on May 16, 2014 9:33 AM by philmodjunk

    Importing with "Update matching record in found set" changes the point to comma

    MarkoRad

      Summary

      Importing with "Update matching record in found set" changes the point to comma

      Product

      FileMaker Server

      Version

      13

      Operating system version

      Windows 8

      Description of the issue

      I am importing data from Tab separated text file to one of the tables in FM file hosted on FM13 server. One of the fields in the table is "Price", defined as the number field.
      When I import Tab text file with "Add new records" option checked out, the "Price" fields are populated with the correct price in form xy.z (example 23.4).
      When I use the same Tab text file and set "Update matching record in found set", "Add remaining data as new records" and import the file, the "Price" field values change the values to xy,z (so 23.4 becomes 23,4 and so on). The system settings on FileMaker Pro side and Server Side are identical and set to use point as decimal symbol and comma as grouping symbol.

      I tried every possible encoding option, but the result is always the same - the point is changed to comma.

      Any ideas why this is happening?  I searched the support forums but was not able to find and similar issue.


      I tried to import Tab text file to Excel, and save it as Excel file, then repeat the same import with Excel file (first import with Add new records, then in second run set "Update matching record in found set", "Add remaining data as new records") -  no issues exist. The "Price" field keeps the form xy.z

        • 1. Re: Importing with "Update matching record in found set" changes the point to comma
          philmodjunk

               Is price a field of type number or type text? (Check in Manage | database | Fields)

               As far as this import is concerned, it shouldn't make a difference, but it may be a useful detail for the TS people to know when they read your issue report.

          • 2. Re: Importing with "Update matching record in found set" changes the point to comma
            MarkoRad

                 The "Price"  is defined as the number field.

                 I agree, it shouldn't make a difference, but it does. I found it out when I updated the production table and all prices got corrupted.

            • 3. Re: Importing with "Update matching record in found set" changes the point to comma
              TSGal

                   Marko Rad:

                   Thank you for your post.

                   I am unable to replicate the issue using FileMaker Pro 13.0v3 under both Windows 7 and Mac OS X 10.9, and the database file hosted by FileMaker Server 13.0v2 on both Windows Server 2008 and Mac OS X 10.9.2.

                   Was this file created in FileMaker Pro 13.0 or a prior version?  Do you know the region settings at the time the database file was created?  Regardless, I would like to see a copy/clone of the database file as well as the tab-separated text file so I can try it here.  Check your Inbox at the top of this page for instructions where to send the files.

                   TSGal
                   FileMaker, Inc.

              • 4. Re: Importing with "Update matching record in found set" changes the point to comma
                MarkoRad

                     Hi TSGal,

                     Please find the info you asked for:

                       
                1.           File was created in Advanced 12.0v1 on Win 8 machine
                2.      
                3.           Regarding the regional settings at the time of creation, I am only sure that the OS number format was standard US. The date format was most likely UK, definitely not US.
                4.      
                5.           The file fmp12 is hosted on Windows 8 machine running Server 13.0V1 x64. Regional setting of server machine are standard US.

                     I will prepare the clone file and tab text file and send it to you

                     Thanks

                      

                      

                • 5. Re: Importing with "Update matching record in found set" changes the point to comma
                  TSGal

                       Marko Rad:

                       I received your files.  Thank you.

                       I was able to replicate the problem with your file on both Windows 7 and Mac OS X 10.9.2 using FileMaker Pro 13.0v3.

                       Since I was unable to replicate the issue using your text file with a new database file, I ran a Recover.  The Recover stopped part way through on a table that contains 190,000+ records with a Container field with external storage.  I then created a clone of your file, and the import issue no longer occurs.  I ran another Recover on the clone, and no issues were reported.  Therefore, I would recommend making a clone of your file, and import the data from each of the tables.  After each table is imported, perform the Import steps to see if the issue returns.  This may help narrow down the data and/or table that is contributing to this issue.

                       TSGal
                       FileMaker, Inc.

                  • 6. Re: Importing with "Update matching record in found set" changes the point to comma
                    MarkoRad

                         TSGal:

                         I understand that you were able to replicate the issue with my fmp12 file using FileMaker Pro 13.0v3. Based on this I assume that upgrading Server to 13.0v3 and placing fmp12 file on the server will not resolve the issue?

                         I tried the recovery of the file - same outcome as in your post, it gets stuck on a table that contains 190,000+ records with a Container field with external storage. By the way, when I prepared the DB file for you, I deleted most of the data by using "Delete all records", but for the mentioned table (190.000 records) I was not able to delete. Try it yourself, seems like the separate issue.

                         I did the clone file of the original fmp12 file- same outcome as in your post, no issues during import.

                         Regarding the solution you proposed (Clone, then import tables one by one) this is serious amount of manual work. Is there some other, more efficient approach?

                         Thanks

                         Marko

                          

                    • 7. Re: Importing with "Update matching record in found set" changes the point to comma
                      MarkoRad

                           TSGal: UPDATE

                           I did the following: deleted the "Item_images_table" (that is a table that contains 190,000+ records with a Container field with external storage) in Manage database menu. After deletion, I run the recovery of the DB file, and recovery passed without issues. I opened new recovered file and tried the same import- again the same issue of changing point to comma is repeating.

                           Any other ideas?

                           Thanks

                           M.

                      • 8. Re: Importing with "Update matching record in found set" changes the point to comma
                        philmodjunk

                             Things to keep in mind about Recover:

                             While Recover almost always detects and fully corrects any problems with your file...

                               
                        1.           The recovered copy may behave differently even if recover reports "no problems found".
                        2.      
                        3.           Recover does not detect all problems
                        4.      
                        5.           Recover doesn't always fix all problems correctly
                        6.      
                        7.           Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.

                              

                             And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).

                        • 9. Re: Importing with "Update matching record in found set" changes the point to comma
                          TSGal

                               Marko Rad:

                               The Delete All Records in the 190,000 record table did progress VERY SLOWLY.  If you look at the counter, you will see records are being deleted at a slow rate.

                               When I cloned the file and imported the data, it worked properly.  The commas did not appear.

                               Again, start with a clone of the file, and then import the data from each table from the original file.  After each table is imported, then try the import of the tab-separated text file.  This should help determine what table data is affecting the import process.

                               TSGal
                               FileMaker, Inc.

                          • 10. Re: Importing with "Update matching record in found set" changes the point to comma
                            philmodjunk

                                 When you import data into a clone, the import process rebuilds all indexes from scratch during the import. This may produce a change of behavior in your cloned file as compared to the original.