7 Replies Latest reply on Sep 14, 2016 11:27 AM by StephenJK

    Problem with data import to FileMaker Pro 14

    StephenJK

      Hi,

       

      We setup a hosted database on a file server to keep track of available parts for mobile equipment.  It can be accessed with WebDirect or with FileMaker over our network by opening the hosted version.

       

      The guys at the shop don't always have good access to WebDirect using a laptop or iPad in the yard because the Wi-Fi connection isn't that great.  In that case, we recently started entering the data into a spreadsheet that was essentially a blank file exported from the FileMaker project so the column and field names would be identical.

       

      Then, all we had to do was import the data from the spreadsheet.  A couple of weeks ago that stopped working.  I've been doing a lot of testing with the Local and Hosted versions of the file, and can't seem to pick up on any pattern.  The database currently has a total of 6,254 entries.

       

      I exported all of the records and imported them to the local copy that was used to develop the hosted version so I can test without any danger to our data.  Also, I know that by using the original file locally and importing the hosted file records all of the calculations and field formatting is identical.

       

      If I delete all of the imported records (6,252 make it, 2 have errors), then I can readily import another file in either .xlsx or .fmp12 format that has another 44 records.

       

      If I allow all of the 6,252 records to remain in place, an import will fail every time, even if it's only a single record.  The popup box shows:

      Total records added/updated:  44

      Total records skipped due to errors:  44

      Total fields skipped due to errors:  0

      Tables created:  <none>

       

      Note that this happens with either the local or hosted version.  At first I thought it was the hosted version only but that doesn't seem to be the case.  It's almost as if there is a limit to how many records there can be.

       

      But, if I import the data into another table in either the local or hosted version then the import works every time, regardless of how many records are already in the file.  Of course, that new table doesn't have the same formatting as the original.  We don't want to have more than one table, as we have given a number of users rights to read only and search for parts remotely.

       

      Any assistance would be greatly appreciated.  We have a number of people entering data from hard copy into spreadsheets that will need to be imported into the main database file, and we do need a solution.  I'm confident that I can't be the only person who has experienced this.

       

      We are running with FileMaker Server 14, Build 14.0.4.412

      My development version of FileMaker Pro Advanced is 14.0.6

       

      The file is pretty straightforward, it's a total of 28 fields that are either text or numbers with a few basic calculations for auto-entry of data or to prompt for data entry.

       

      Stephen

        • 1. Re: Problem with data import to FileMaker Pro 12
          philmodjunk

          Those "skipped" results are usually due to the imported data failing to pass a validation test set up on one of the fields. An example that matches what you report would be if you specified "unique values" on one or more of your fields. Starting with an empty table would then work because your 44 records likely do not fail that unique values test, but doing the same import into a copy with all your current data could trip that unique values test and cause the imported data to be skipped.

           

          I'm not saying that this is the only possible validation test that could cause this, but it does happen to fit what you report...

          • 2. Re: Problem with data import to FileMaker Pro 14
            StephenJK

            Phil,

             

            I'll follow up on that - the issue is that it all worked well until a couple of weeks ago, and now suddenly does not.  There are very few people who have the rights to change or modify the file, I'll do some testing from that perspective.

             

            Thanks for the response.

            • 3. Re: Problem with data import to FileMaker Pro 14
              philmodjunk

              It may not be due to a recent change in your file. This setting may have been there all along and it is only now after enough records have built up in your table that you have data that fails the validation.

               

              If you can't find a validation rule that's the source of the trouble, try importing the data into a recovered copy of the file to rule out issues with damaged indexes and the like.

              • 4. Re: Problem with data import to FileMaker Pro 14
                StephenJK

                It looks like that may be the problem.  I have a Tag No. field that has to be filled out and has to be a unique value.  If I change that to simply a numeric that has to be filled out I can do the import.

                 

                I thank you for your help and for a quick response.

                 

                Much appreciated.

                • 5. Re: Problem with data import to FileMaker Pro 14
                  StephenJK

                  For anyone else who may have this problem, the specific issue was a field that had been configured to validate data Always.  Simply changing that to Only during data entry has resolved the issue.

                  • 6. Re: Problem with data import to FileMaker Pro 14
                    philmodjunk

                    That solves the problem in terms of allowing the import, but enforcing unique values is often a critical aspect of ensuring data integrity in your table. You may need to rethink why you would need to enforce this during data entry and not when importing.

                    • 7. Re: Problem with data import to FileMaker Pro 14
                      StephenJK

                      Phil,

                       

                      I suspect that on data entry, there may be a duplication of the Tag No.  That would cause the problem we're having.  The Tag No. is a physical inventory tag with a pre-printed number that's used as a key identifier.  We find that a lot of the data entry needs to be verified, another reason to go through a spreadsheet process where the data can be reviewed once imported as a found set.

                       

                      I'll keep an eye on the process and make changes to the validation rules as needed.

                       

                      Stephen