1 2 Previous Next 15 Replies Latest reply on Jan 15, 2013 1:17 PM by Kj

    Import update matching records issue



      Import update matching records issue


      FileMaker Pro



      Operating system version

      Mac x.7

      Description of the issue

      We load data to filemaker all of the time and 'update matching records' based on a key.
      The first import of the year however is not an update matching records, it is just add records. (we start fresh annually). Filemaker 12 TURNS OFF the import arrow for the key field on the first import. I have verified that filemaker 11 did this properly and imported the key field on the first import.

      Karen Johnson


      Catch the error and turn on the import for the key field on the first import.

        • 1. Re: Import update matching records issue

               Are you manually importing records or using scripts?

               One of the reasons for using a script to import records is that your field mapping details are saved as part of the import records script step.

          • 2. Re: Import update matching records issue


                 Could not do all of this work without scripts. It is all scripted. FMP 11 worked as one would expect. FMP 12 has this little bug.



            • 3. Re: Import update matching records issue

                   It may help the FileMaker techs trying to replicate the issue if you share your script for importing records and the precise details you are selecting for that import.

                   To post a script to the forum:

              1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
              3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
              5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
              7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
              • 4. Re: Import update matching records issue
                /files/5d232d7393/fmpreport.jpg 791x1024
                • 5. Re: Import update matching records issue

                       How do you use the same script to do a matching records import?

                  • 6. Re: Import update matching records issue

                         Huh? That is the bug! 

                         That is the same script (matcing records). On first import to empty db it turns off the import to the key field as I said in first post.. FMP 11 did this properly.

                         Please let someone know about the bug so that it can be fixed. Thanks,

                    • 7. Re: Import update matching records issue

                           I do not work for FileMaker so I cannot make any different bug report that you have already posted here. But recreating a bug--something a TS person from Filemaker will want to do eventually, requires being able to repeat exactly the same steps that you have.

                           Your screen shots appear to contradict each other. Import 'matching' and Import 'add new' are two different import records options. Your script step shows:

                           Import Records [Update Matching

                           but your screen shot of the field mapping dialog shows that the "add new" option has been selected. These are mutually exclusive options. You cannot select both for the same Import Records action at the same time.

                           So how did "Update Matching" shown in the script step become "add new" in the field mapping dialog where you show the missing arrow on the match fields? Does your script halt with that dialog open and you manually select which of the two options that you want?

                           I recommend that you set up two scripts--each with a different Import Records step or one script with an If step that selects between one of two Import Records steps--one set up with "Add New" and one set up with "Update matching".

                           Either that or select the "add remaining data" option as part of your Update Matching set up if you can get that to work with how you need to use your database. That option will add any records that do not match as new records so it may enable you to use one Import Records step for both purposes.

                           Either approach provides you with a way to avoid this particular issue and will be faster as you won't need to modify any of the field mapping settings in order to import records.

                      • 8. Re: Import update matching records issue

                             I have confirmed Karen's report about a difference between what was seen in FM 11 when a matching import was processed into an empty found set, and what is seen under the same circumstances in FM 12. There is no actual change in functionality though, only the very disconcerting impression of a serious ommission. (This is not a conversion issue. The same misleading results can be created entirely in FM 12.)

                        Pre-FM12: When performing a scripted import originally defined and mapped to "Update Matching" into an empty found set, with dialog, the Import Field Mapping dialog displayed like an "Add" import that included the original matching fields. This is expected, because you can't match to records that don't exist.

                        FM12: When performing the same scripted import into an empty found set, the Import Field Mapping dialog still displays an "Add" import — but now the original match fields ARE NOT INCLUDED in the mapping. Contrary to what the displayed mapping indicates, however, the actual import WILL include the missing match fields, and future imports (into found sets) will reflect the correct, original mapping (as will the script once there are records present for matching).

                             This is not an issue that actually changes existing functionally (if the import were performed without dialog, it would go unnoticed), but the mapping dialog no longer provides an accurate representation of what will occur, which I would definitely consider a bug. This is potentially quite dangerous because the impression of an error could lead to a real error. Prompted to cancel the import and look at the script after seeing what apears to be a mapping error, it's too easy to click OK (instead of Cancel) after looking at the mapping (even without having changed anything), thereby committing the impression of an issue into an actual one. Alternately, someone might re-map the script to include the missing match field, permanently changing the import from an update to an add.

                        • 9. Re: Import update matching records issue

                               Thank you both, but....

                               1. In fmp 12, on the first import to an empty file, it does not import the key field (OS x.7). The other fields are imported.

                               On subsequent imports, records that should be updated are not because the first set imported did not have a key so update

                               matching cannot take place of course. FMP imports never worked this way in several past years/versions. 

                               I only have to catch this on the first run and then we are fine but this is a bug.

                               2. I tried to find where to report bugs and this is what I found. Where do I report a bug? It is not easy to find.





                          • 10. Re: Import update matching records issue

                                 When I tried this, if I ignored what the import dialog showed during the initial import (i.e., no key field being imported), the import results DID include the key field. The resutls were the same for both the 11-12 converted script as well as an import script added post-conversion. The only functional difference noted during my attempts to reproduce the behavior was that in FM 12, the mapping dialog no longer shows the match fields as being included. The import results were exactly as expected based on the original mapping, which was not consistent with the mapping dialogs presented.

                                 My initial testing included two separate scripts, both created in a copy of the FMP Bugs.fp7 file and identical in every way except that one performed the import with dialog, the other without. To support this script I created a copy of the FMP Bugs table to which I added one temporary record that was deleted after the matching import had been defined. I wanted to see if this was propogated by the conversion (as some layout issues have been), so I then then built a new import script in the .fmp12 file. This behaved exactly like the converted scripts.

                                 When I verified the script's import mapping post-import (with records present in the receiving table), it was exactly as it had been originally defined. When I attempted to verify the import mapping pre-import (no records to match), it definitely looked wrong, just as Karen reported and like what I saw during the import. If I then selected Cancel, the erroneous mapping was not retained and the import performed as expected. If I selected OK, the erroneous mapping was retained and the import did not execute as originally defined. (I know this is expected behavior, but I tried it in anyway, perhaps hoping that FM 12 would read my mind and know that I didn't really intend for it to replace my original matched mapping.)

                                 Although I couldn't reproduce Karen's results (not without having committed the erroneous mapping), my testing used an internal table while Karen's import used a .xlsx file.

                            • 11. Re: Import update matching records issue

                                   You have spent so much time on this Heather. Yes, all of our imports are external files imported into our dbs.

                                   Do you work for filemaker? I want filemaker to know of this bug so they can spend time on it and fix it. I cannot fix the bug no matter how mcuh time spent on it.

                                   Thank you,



                              • 12. Re: Import update matching records issue

                                     FileMaker reps have a TS at the start of their forum names and their forum graphics include the Filemaker Logo.

                                     I'm repeating a suggestion that the two of you seem to have missed:

                                     Select the "Add Remaining records as new data" option in addition to selecting the "update matching" option. Then no changes to the field mapping should occur when your found set is empty.

                                     Of course this method may not apply to all possible import scenarios. It may be that when you do an import matching type of import there are records that don't match that you want to exclude.

                                     In which case, using different Import Records steps for each type of import would seem a better option to work around this issue.

                                • 13. Re: Import update matching records issue

                                       Sorry Phil, but I'll have to disagree with you. First, your suggestion was not missed. Karen's original import mapping may not have included "Add Remaining… ", but my screen shot clearly indicated that this was selected in my script.

                                       More importantly, the "Add Remaining …" option simply isn'tt relevant to the problem:

                                  1.           The reported issue was specific to the failure of an initializing import to behave as it did prior to FM 12, and for Karen's purposes the subsequent matching imports may in fact be limited to updating existing records, with no expectation of adding (other than, as she described, when run for the first time each year, after the prior year's records have been removed).
                                  3.           Regardless of the intended action for subsequent imports, the "Add Remaining …" option has no impact on the reported behavior. 
                                  •           In the absence of a found set with which to match imported records, FileMaker automatically changes the Import Action being performed from "Update matching records…" to "Add new records". So, for the purpose of verifying Karen's reported issue, which was specific to the initializing import, this option is of no consequence.
                                  •           It doesn't matter whether this option is selected or not selected. In my testing the import results were precisely the same either way — all of the originally mapped fields, including the match field, were imported as intended.

                                       The issue that I confirmed was not with the import results, which in my case worked as expected. What I found instead was a disturbing change in how the import mapping is being represented to the user when importing to an empty table.

                                  •           FM 11 — the import dialog accurately indicates that the match field will be imported
                                  •           FM 12 — the import dialog inaccurately suggests that the match field will not be imported
                                  • 14. Re: Import update matching records issue


                                         Thank you for the post.


                                         I was able to replicate this behavior in a test file after converting a .fp7 to a .fmp12 on my work station running Mac OS 10.7.5 during a scripted import with an .xlsx file or another FileMaker Pro file. 


                                         Seemingly, deleting all the records from a file that containing a scripted import using "update matching" modifies the script from "update matching" to "add new" and drops the import of the key field. 


                                         Suggestions for two possible work arounds: 

                                         1. Modify the script after conversion and prior to the no records import.

                                         2. Write a second script for the no records import for adding new records instead of using the update matching option.


                                         FileMaker Pro is attempting to do what was asked of the program via the script, which is to update matching records in a found set. When the program realizes there is nothing to match on and no found set at all, FileMaker modifies the script to "Add new records" and completely drops the import of the match field. The script can be edited to bring the import back in the dialogue menu or by editing the script. Also, to avoid the need to remember to edit the script, another script could be used for first import set to add new records instead of importing based on a nonexistent match.


                                         This is changed behavior from FileMaker Pro 11 to FileMaker Pro 12, so I additionally forwarded a report with your research to our Testing and Development department for review.



                                         FileMaker, Inc.

                                    1 2 Previous Next