4 Replies Latest reply on Aug 7, 2013 9:28 AM by philmodjunk

    Report of losing parts or all of records when importing new or updating matching records

    TimmyLew

      Summary

      Report of losing parts or all of records when importing new or updating matching records

      Product

      FileMaker Pro

      Version

      11.0v4

      Operating system version

      Mac OSX 10.6.8

      Description of the issue

      Hi, my name is Timmy from IT for Sweetwater County School District #1 in Wyoming.  We use aFileMakerPro 11 database as a means of keeping inventory for all technology items (19000+) in our district.  When adding new equipment to our inventory database, we use the import feature to save extensive amount of time.

      Unfortunately, it appears that records entered into the database by another administrator are believed to be lost and/or deleted.  A possible explanation for these lost records would be a bug in the FMP system activated whenever an import occurs.  To clarify, the records that are reported as lost are not the records that we are adding or updating through the import.  We wanted to check and see if this is a valid explanation for the missing records.

      Does anybody else have a similar issue, or is there a known bug that could cause this type of error when importing??

      Any help you can provide is much appreciated!!

      Steps to reproduce the problem

      This is a fairly new issue that has been brought to our attention.  As of now, the only steps taken have been to no longer use the import feature, and entered/update records 1 by 1.

      Expected result

      Being able to import without compromising data already in the database.

      Actual result

      It is possible that importing has cost records already in the database to be lost.

      Exact text of any error message(s) that appear

      No error messages occur.

      Configuration information

      No additional configuration information to add.

      Workaround

      Only workaround is to enter data 1 by 1.  Not an ideal situation at all!

        • 1. Re: Report of losing parts or all of records when importing new or updating matching records
          philmodjunk

               A more detailed description of how you are importing data would be helpful. From what are you importing your data? csv file, Excel, tab, or ???

               Are you using a script to import data? If so, have you examined the script for possible issues--particularly in the import record step's field mapping dialog? There are numerous options that you can select in this dialog and you have not described what options you are using.

               I can think of a possible issue that can cause a scripted import to fail to fully and correctly import data:

               Design changes made to the table after the import script was created by cause incorrect field mapping.

               For More Information see:     http://forums.filemaker.com/posts/e2da04636d?start=1&stop=10#187742

               This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

               It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

               This bug was finally corrected with the release of fileMaker 12--though it can still be an issue in files converted from the FileMaker 11 file format.

               There are other issues that might come into play, but I would need to have a much more detailed picture of your import process before suggesting them as possible issues here.

          • 2. Re: Report of losing parts or all of records when importing new or updating matching records
            philmodjunk

                 Note however, that a damaged file or a corrupted index can result in the "loss" of data. In the case of a corrupted index, the data may actually be there but relationships, finds and sorts are failing to pull them up as expected by the user due to the messed up index.

            • 3. Re: Report of losing parts or all of records when importing new or updating matching records
              TimmyLew

                    

                   Thank you for responding PhilModJunk! I greatly appreciate it.

                    

                   I apologize for not being more specific about my importing process in the original post.  I import from a normal excel (XLSX) file.  I am running Microsoft Excel for MAC 2011 version 14.3.6.  Additionally I do not use a script to import.  I simply import records in by going under File/Import Records/File. . .

                    

                   I'm not sure if I was clear on this before, but it is NOT the newly imported records that we are having issue with.  I can and do easily verify the accuracy of new imports, as the list of records that pops up after an import is just those records that has newly been updated or added. It is actually records entered in the past (not ones that are updated or are newly added) that have gone missing.

                    

                   Thank you much for linking a possible bug that may have caused these issues.  However, I do not believe that it is this specific bug that is causing this problem, as FMP version 11 is the first version we have used.

                    

                   When it comes to mapping and matching source fields to target fields, I have never changed around target fields.  I have always left these fields in the "default order" and changed my excel to match the default order, thus when I am ready to import records, I do not have to adjust source fields.  I have, on numerous occasions, however, used the don't import this field option for certain fields. I have attached an image of how a "typical" import mapping looks for us.

                    

                   If there is anything else I can add to help you understand my issue, please let me know!

                    

                   Thanks again for all of your assistance!

                   Timmy

              • 4. Re: Report of losing parts or all of records when importing new or updating matching records
                philmodjunk
                     

                          Additionally I do not use a script to import.  I simply import records in by going under File/Import Records/File. . .

                     That makes evaluating whether the import records process could possibly be at fault or not more difficult as there is no way to guarantee that you specified the same/correct options with each import. (We're human, we all make mistakes...)

                     I strongly suggest that you set up a scripted import (Test it on backup copies until you are sure it works correctly) as a way to guard against possible user errors.

                     

                          I do not believe that it is this specific bug that is causing this problem, as FMP version 11 is the first version we have used.

                     That's not a factor in the bug I referred to. This bug affects nearly any version of FIleMaker except FileMaker 12 files. But the fact that you don't use a script is a factor that rules out that bug as this only applies to scripted imports.

                     I think it's very likely that import records is not the cause of your missing records unless you spot something in the affected records that has some link with the records that you are updating. The only possible issue I can spot at this point is that some of the rows in your Excel file lack a value in the AssetNumber column--which is the field/column you have specified as the match field. I have no idea what effect that will have on your import, but I'd check on the results that produces very carefully.

                     I can suggest a few things at this point:

                     Take the most recent back up copy of your file and recover it. See if any problems are reported by the recover process. Then examine the recovered copy and see if any missing records/data can now be found that could not previously be found. Recover rebuilds all indexes from scratch but does not report finding/fixing any index issues so if a damaged index is the cause, you may be able to find the data in the recovered copy. If the recovered copy works, you might try using advanced recover options to just rebuild all indexes and see if the copy that produces also corrects the problem.

                     If you don't already, set up your system to make at least daily backups of your file(s). If you have these already, search back through them to see if you can find a copy that has the data that has gone missing. This can help you determine when the data went missing and might provide a useful clue as to what might be the cause. And with regular backups made and kept in long term storage, if you spot data missing, you can import the missing data from an intact backup into your current copy to repair the damage.

                     And please note that user error and/or scripts with errors can also be the cause of what you report here.