7 Replies Latest reply on Apr 3, 2014 2:06 PM by philmodjunk

    Import Matching Records Not Working

    dcawrse

      Title

      Import Matching Records Not Working

      Post

           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 rather than deal with that, the first thing we did was to move it into a newly created table in FileMaker.  We 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 = _pkx_AcctNo 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 noticed that about 25% of the records that should have been updated were not touched.  We first thought there might be an issue with the source data, so we did a Replace Field Contents on the AcctNo field to clean up any stray spaces, carriage returns, etc.  We also added a field to check for the required length in term of number of characters.  After doing that, the import still partially failed.

           We then picked out a couple of records where the import failed, copied the field value from the _pkx_AcctNo field and pasted it into the AcctNo field in the source table and ran the import again.  It failed again.

           Finally, we created a relationship between the two fields (AcctNo and _pkx_AcctNo) to see if the relationship would work on those records where the import failed -- it did.  While this will allow us to complete the import process as required, my concern is that there is a remaining issue with the importing of matching records that is causing it selectively fail without reason or warning.

           Has anyone else seen this?  Is there a reported or unreported bug for import matching?  (FYI, we are using FileMaker 12.0.4 for Windows and the database is hosted on FileMaker Server 12 running on Windows 2008 Server R2.)

        • 1. Re: Import Matching Records Not Working
          philmodjunk

               The most common reason for Import Records with the "Match found set" option failing occurs when the found set of the target table does not contain the correct set of records. Do you do a "Show All Records" on the target table before you import these records?

          • 2. Re: Import Matching Records Not Working
            dcawrse

                 We're not doing a Show All but a found set that includes all the records that need to be updated.  As I noted in my original post, we actually isolated one record that should have been updated, ran the import on just that record, and it failed.

            • 3. Re: Import Matching Records Not Working
              philmodjunk
                   

                        As I noted in my original post, we actually isolated one record that should have been updated, ran the import on just that record, and it failed.

                   Sorry, but that was not absolutely clear to me that this was what you meant as it could have meant (to me anyway) that you isolated the record in the source table instead of the target table.

                   The next picky detail to check is that you really have the found set that you think you have. When you select a table as the source or target table in the import, you are actually selecting a table occurrence--one of the boxes in Manage | Database | Relationships. Each table occurrence has it's own found set and you can have multiple table occurrences of the same data source table. Is it possible that you set up the found set (or isolated the single record) on a layout that refers to a different table occurrence than the one that you specified in Import Records?

                   Please note that this could only happen if you are using a script to import the records. When you do this as a manual import via Import Records from the File Menu, the current layout determines the target table and thus a find that you perform on that layout just before selecting this option from the File menu cannot be from any other Tutorial: What are Table Occurrences?.

              • 4. Re: Import Matching Records Not Working
                dcawrse

                     The found set is 8,900+ records in the table itself.  The layout that is used is one that we set up when the database was created that has all the fields in the table and is not part of a created table occurrence.  The source table has 2,300 records.

                     We've run the import both scripted and manually with the same result.

                • 5. Re: Import Matching Records Not Working
                  philmodjunk
                       

                            The layout that is used is one that we set up when the database was created that has all the fields in the table and is not part of a created table occurrence.

                       Sorry, but that is not possible. EVERY layout must refer to a table occurrence or no data can be shown/accessed on that layout.

                       I was going to suggest checking the actual data in the field, but it appears that you have already done that. I will make one observation even though it appears that you already have checked that possibility:

                       If you put this data into the match field of table 1: "XYZ" and put "XYZ¶" into the match field in table 2, the records will match in a relationship even though the data in the fields is not identical. But your check using the length function would seem to rule out this possibility...

                       I manage a Known Bugs List database for the FileMaker community and so keep very up to date on bug reports in Report an Issue. This form of import is used very often and I've not seen any issue reports that match what you've described. So I'd be very surprised to find a bug here for any but the most recent version of FileMaker (13.02 released yesterday).

                       Yet if when you rule out all other possibilities....

                       Let's rule out one unlikely possibility first: Recover the file or a recent back up copy of the file. Test the recovered copy even if FileMaker does not report finding a problem during the recover. I would expect that not to make a difference as I would think that if there were an issue with the file, the relationship would also fail to work, not just the import matching, but stranger things have happened.

                       If you get the same results with the recovered copy (if you are using two files, recover both), all that remains that I can think of is to post a report over in Report an Issue to see if the FileMaker Techs can spot something that I haven't.

                  • 6. Re: Import Matching Records Not Working
                    dcawrse

                         I said that it was not part of a created table occurrence and prefaced that comment with the fact that it was the layout that was created when the database itself was created.  Sorry if there was any confusion between the table occurrence that got created when the table itself was created versus those that are created as part of the development process.

                         Unfortunately, I cannot pull the database offline at the moment.  It will probably have to be done over the weekend, as it is currently in active use by the sales force of a large company.

                         I'll let you know what happens as soon as I know.

                    • 7. Re: Import Matching Records Not Working
                      philmodjunk

                           In my lexicon, all table occurrences are created table occurrences.

                           Surely you have back up copies of this file? You can run a recover on a backup copy to check for issues without needing to pull the active copy down off the server.