1 2 Previous Next 17 Replies Latest reply on Apr 5, 2014 5:17 PM by disabled_winfried

    Import Matching Records Not Working

    dcawrse

      We have a unique situation that we haven't been able to figure out in FileMaker 12. We needed to update a set of records in an existing database to reassign accounts to new account managers. The source data was in an Excel file, so the first thing we did was to move it into a newly created table in FileMaker. We then then set up an import script to handle the update of the target table using the "Update Matching Records in found set" parameter and AcctNo = _pkxAcctNo as the match fields in their respective tables. (Note: All fields involved in the import are defined as Text, are not calculations, and are indexed.)

       

      After running the import, we discovered that about 25% of the records that should have been updated were not touched. We thought there might be an issue with the source data, so we did a Replace Field Contents of the AcctNo field to clean up any stray characters like a carriage return, etc., but the import still partially failed as it had before. We then picked out a couple of records where the import failed, copied the field value from the _pkx_AcctNo field in the target table and pasted it into the AcctNo field in the source table and ran the import again. The import on those records still failed.

       

      We then set up a relationship between the source table and the target table using the two fields that were used as the match field in the import -- AcctNo and _pkx_AcctNo. Even though the import failed to recognize the match, the relationship set up between these two fields worked 100% of the time. While this will allow us to update the records, my concern is that there is an issue with the importing of matching records that is causing it to sometimes fail without reason or warning.

       

      Has anyone else seen this? Is there a reported or unreported bug for import matching?

        • 1. Re: Import Matching Records Not Working
          BruceHerbach

          Is it possible that there is a space or other hidden character in the Excel ID field?  I ran into something like this once and that was the issue. The solution was to import the data into a new table.  Then do a replace field contents with the current value using the filter function.  The filtertext should be just the allowable characters.  This should remove any characters you can't see and then should match on import.

           

          You should check the existing data just to be sure there is nothing funny in the ID field there,  but you never know.

           

          HTH

          • 2. Re: Import Matching Records Not Working
            dcawrse

            Hi Bruce,

             

            I thought of that; that's why we did a Raplce Field Contents on the data source after it was moved into its own table in FileMaker -- to remove any spaces, carriage returns, etc.  Even when we copied an entry from the target table and pasted it into the matching field in the source table, the import failed.  That's what's got us concerned.

             

            Thanks for the idea though.

             

            David

            • 3. Re: Import Matching Records Not Working
              ch0c0halic

              No bug that i am aware of.

               

              However, there are reasons for a failure that have nothing to do with the key values matching.

              if you have two or more records in the destination that matches the Key field in the source only the first will be update.

              If there are field validation rules on any field that fail the that record will not import.

               

               

              As a test, and since you already have the relationship built, try using a looping script and a set field to do an import like operation but on a record by record basis and see if you get an error on any of them. Be sure the script sets every field imported into in the import script step. Any of the fields receiving data could fail validation and prevent the import from occurring.

              • 4. Re: Import Matching Records Not Working
                BruceHerbach

                Hi David

                 

                If it is consistent about the records being skipped. Check the ID field in

                the target table.

                 

                Last thought,  possibly an index issue. Turn index off on the field in the

                target table. Close the file. Reopen it and turn indexing back on. This

                should create a new index for the field.  Then try the import.

                 

                Bruce

                 

                Sent from my mobile device... Please excuse typos.

                • 5. Re: Import Matching Records Not Working
                  dcawrse

                  There are no duplicate primary key records in either table.

                   

                  There are no validation rules on the fields in question.  Nevertheless, I went ahead and created and ran a script that would update each record individually in a loop using the Set Field script step and monitored it in Script Debugger and used the found set of records that were not updated in the import.  There were no errors when the script ran.

                   

                  Thanks for the ideas, though.

                  • 6. Re: Import Matching Records Not Working
                    dcawrse

                    Hi Bruce,

                     

                    The fact that we were able to create a relationship that works using the ID fields tells me that these are OK.  I tried re-indexing the fields to see if that would work, but no luck.

                     

                    Thanks again for the ideas.

                     

                    David

                    • 7. Re: Import Matching Records Not Working
                      BruceRobertson

                      In the target file you are starting with a found set of all records?

                      • 8. Re: Import Matching Records Not Working
                        dcawrse

                        No.  The found set is 8,900 records out of a possible 10,400.  The update table has 2,700 records.

                        • 9. Re: Import Matching Records Not Working
                          BruceRobertson

                          More to the point, the starting found set does include all the IDs from the update table?

                          • 10. Re: Import Matching Records Not Working
                            dcawrse

                            Yes, all of them are in the found set.

                             

                            As I mentioned earlier, we tried this with a found set of 1 record that we knew was skipped, running the import both as part of a script and manually.  The update failed both times.

                            • 11. Re: Import Matching Records Not Working
                              Stephen Huston

                              dcawrse wrote, in part:

                               

                              There are no validation rules on the fields in question.

                              Any validation rules on any field in a record can keep an import from working, not just on the fields being affected by the import process.

                               

                              If the relationship you created to test the keys is working OK for all records, then I would guess that something as simple as an empty field that isn't supposed to be left empty might be the validation failure that stops the update (missing zip, city, etc. which might have a validation setting that allows user overrides but the import catches on it).

                              • 12. Re: Import Matching Records Not Working
                                dcawrse

                                I would say that could be possible were it not for the fact that the script that uses the Set Field update ran without error.  If there were a validation issue, when the script ran while being monitored by Script Debugger, wouldn't it show an error in the 500-509 range?

                                • 13. Re: Import Matching Records Not Working
                                  Stephen Huston

                                  If the validation is set to allow user override during data entry, the scripted process will run just fine on existing records because you aren't editing the field with validation, but the import process is pickier than a script--it's trying to avoid creating invalid records, and it's probably testing the same issues during an import/update as in an import which only creates new records.

                                  • 14. Re: Import Matching Records Not Working
                                    dcawrse

                                    We update the database all the time by importing data as new records and have never have an issue then.  Also, there were more than 1,000 records where the import worked.  If this were the indeed the issue, I find it hard to believe that it would selectively work on some records and not on others.

                                    1 2 Previous Next