8 Replies Latest reply on Jan 23, 2017 1:47 PM by scottworld

    Import Records script step fails?

    scottworld

      Has anybody ever seen the "Import Records" script step sporadically fail in spectacular fashion?

       

      I am experiencing a longstanding problem with a FileMaker solution. This problem goes back at least a year through multiple versions of FileMaker Pro and FileMaker Server (13, 14, and 15). And the problem continues to this day with FileMaker Pro 15.0.2 and FileMaker Server 15.0.2.

       

      The problem centers entirely around one script step: the "Import Records" script step, and I believe that this may be a bug with FileMaker Server (as opposed to FileMaker Pro), but I'm not 100% sure.

       

      As we all know, if you use the "Import Records" script step to import records from one of your tables to another one of your tables in the exact same file that you're currently using, FileMaker will import whatever records are in your found set in the source table in your current window. But if you don't prepare the found set of the source table ahead of time in your current window, FileMaker will import ALL records from the source table, because all tables in FileMaker always default to starting off by showing all records.

       

      So, in my solution, users are often importing records from one table into another table in the exact same file. They're basically importing a found set of records (from a Products table) into their Invoice (into an Invoice Line Items table).

       

      It's an extremely simple & small script with absolutely no trickery involved, and it's a script which my users run at least 50-70 times per day. So in a given week, this script is probably run at least 350-500 times per week.

       

      This script actually works perfectly fine over 99.8% of the time! No problems at all! But about once per week, it completely fails for ANY random user in the organization. And when it fails, it always fails in the EXACT SAME WAY every single time. This bug is non-reproducible and it is non-predictable... yet it happens at least 1 time out of 500 times and it has happened at least once to every user in the organization.

       

      The script is extremely simple. It just takes the basic steps that you would expect: set error capture on, freezes the window, switches into the products table, performs the find for the records that should be imported, then switches into the invoice line items table and performs the import, then replaces the ID_Invoice foreign key into the imported records. It's super simple, super basic.

       

      I've also added TONS of error checking along the way... the script will halt with an error (after taking the user back to their original layout) if there are no records found in the Products table, the script will also halt with an error (after taking the user back to their original layout) if ALL the records are found in the Products table, the script will halt if the import returns an error message, etc. Because of this intermittent bug that I'm experiencing, I have now added about 25 different types of error trapping throughout the script. It traps for every error you could possibly imagine. But all the error trapping in the world doesn't help matters at all... the bug STILL persists, and the bug STILL pushes through.

       

      This is the bug:

       

      - During the "Import Records" script step, FileMaker will import ALL THE RECORDS from the source table, instead of the found set of records which the script just prepared a moment earlier! If you switch back to the source table's layout, the found set of records is still sitting there! But FileMaker is importing ALL THE RECORDS instead of the found set of records! It's as if FileMaker is opening up the file for the very first time from a different source, which would default to all records in all tables.

       

      - And here's the real kicker, which is the real clue that something extremely strange is going on here. Not all of the time, but MOST OF THE TIME when this bug crops up, FileMaker Pro will ask the user to LOGIN to the EXACT SAME FILE immediately before doing the import (using FileMaker's standard authentication dialog box that comes up upon first opening the file)! This makes absolutely no sense at all -- we only have ONE COPY of this file in the entire organization, it lives on the FileMaker Server machine, and it is ALREADY OPEN on that user's machine. But FileMaker is asking the user to LOGIN again to the exact same file that they are ALREADY IN! There are NO OTHER COPIES of the files ANYWHERE (outside of the backups in the Backups folder on the server).

       

      So you may be wondering, what am I using as the file path in the "Import Records" script step? For the longest time, I've simply been using the relative file path like this:

       

      file:CottonInvoices

       

      Wondering if the lack of file extension might be the problem, I changed the file path to this:

       

      file:CottonInvoices.fmp12

       

      Same problem. There is only ONE copy of CottonInvoices.fmp12 that exists on any computer in the entire organization. It lives on the FileMaker Server machine, which is only hosting this one individual file. There is no way for FileMaker Pro to be pulling from a different file.

       

      But thinking that the file path might be the problem, I hardcoded the entire file path to the server (all users are on the LAN via Ethernet cables):

       

      fmnet:/192.168.1.150/CottonInvoices.fmp12

       

      Same problem occurred.

       

      Then, I changed the file path to a variable:

       

      Set Variable [$filepath; Get (FilePath) ]

      Import Records [$filepath]

       

      Same problem occurs. Right before it performs the "Import Records" script step, It prompts the user (some of the time, not all of the time) to login to the exact same file that they already have open, and then it imports ALL RECORDS instead of the found set of records which is fully prepared and fully visible in the source table.

       

      You may also be wondering -- are there any External Data Sources? Nope, if you pull down from the menu bar to "File > Manage > External Data Sources", there are no files listed there. This is a single file solution, and there is only one copy of this file.

       

      You might further be wondering -- is there corruption in this file? Running the full File Recovery on this file returns NO DAMAGE to the file. I have even saved a compacted version of this database, and the problem keeps happening.

       

      My final step was one that I took this morning: I completely deleted the "Import Records" script step and re-added it from scratch. This "Import Records" script step was a holdover from when this solution was originally built in FileMaker 8.5, so my only final guess is that there is some hidden corruption or hidden problems with this legacy "Import Records" script step that hasn't been modified since FileMaker 8.5. Because I just deleted the "Import Records" script step this morning and re-added it from scratch, I have no idea if this will fix the problem. I'm hoping that it will. I know that FileMaker Inc. completely rebuilt & finally fixed the importing engine in FileMaker 12, and upon conversion to FileMaker 12, I DID go back into the Import Records script step and toggle the fields around to gain the "newly fixed" import behavior. I'm now assuming that this legacy Import Records script step still has problems in it, which is why I deleted it and re-added it this morning. Time will tell if this morning's fix will actually fix the problem or not.

       

      Now, the ultimate workaround to this bug would be for me to simply use a looping script with several "Set Field" script steps, instead of me depending on the "Import Records" script step. This is actually exactly what I'm planning on doing if this bug crops up again, because it seems like I can't currently depend on this "Import Records" script step.

       

      Any thoughts or ideas?

       

      Thanks,

      Scott

        • 1. Re: Import Records script step fails?
          TSGal

          scottworld:

           

          Thank you for your detailed post.

           

          If you are being prompted to login to the file again, then somehow FileMaker does not recognize the already opened file.  When logging into the file the second time, it is very likely that this second instance will display all records.  In essence, I suspect there will now be two instances of the file open; one with the found set and the other with all records, and this may be the source of the issue.

           

          Assuming this issue occurs again, have the client perform a print screen of the Windows menu and Show Windows sub-menu immediately after the error, as this will show all open files and open hidden files.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Import Records script step fails?
            scottworld

            Thanks, TS Gal. I will have them take a screenshot of the Windows menu and the Show windows submenu, to see how many instances of this file are open. If multiple instances of this one single file are open, that would be a strange networking bug in FileMaker, right? If the complete file path is hardcoded into the "Import Records..." script step, FileMaker should not really be misinterpreting that file path, right? (They're not even on laptops -- they're all hardwired in via Ethernet on desktop machines with WiFi turned off.)

            • 3. Re: Import Records script step fails?
              scottworld

              Oh, I just realized:

               

              There doesn't seem to be a way for me to get that screenshot for you. The problem happens during the "Import Records" script step, but while the import dialogue box is up in the screen, the "Show Windows" submenu is dimmed so you can't see what's within it. Then, after the import dialogue is complete, any files that were open as a source file for import are automatically closed afterwards.

              • 4. Re: Import Records script step fails?
                mathepac

                I have always achieved the result you want by using the product_id from products pasted into invoice_line_item to look_up the required fields in products. If you want to preserve historical data e.g. pricing on date invoice was raised, which may change over time,  in the invoices, copy and paste the required fields into newly named fields in invoice_line_items. Loop through the found set in products creating new a new invoice_line_item for each unique entry in the found set (sort the found set by product_id and check for change of product_id in your script). Commit invoice header record, invoice_line_items, trapping for errors and pass control back to user.

                 

                Have I read your spec incorrectly and is this essentially what you want to achieve? I know from experience this will work with an error rate way less than what you are experiencing, but may be a bit slower. Adding physical memory to servers and work-stations and increasing cache-sizes in FM Server and user prefs will be a big help.

                • 5. Re: Import Records script step fails?
                  scottworld

                  Hi TSGal:

                   

                  I posted this problem onto another discussion forum of advanced FileMaker developers, and several of them have seen this bug before. They gave me a DEFINITIVE workaround to this problem (see #1 below), and they've also come up with some interesting ideas as what might be triggering this bug within FileMaker Pro or FileMaker Server:

                   

                  1. Many of the developers who have seen this bug in the past no longer trust importing from one table occurrence into another table occurrence within the same hosted file. (And yes, our script always ensures that the user is on a layout representing the target table occurrence before trying to import records into that target table occurrence.) So the GUARANTEED workaround and DEFINITIVE solution to this bug is to always EXPORT your desired found set to a temporary file first, and then IMPORT RECORDS from the temporary file. This will always work, and will completely workaround this bug altogether. This is what we have now implemented into our solution, so we will never see this bug again.

                   

                  2. My solution always has multiple windows open at any given time. Many of the developers I've spoken to believe that this bug is a "multiple window" bug within FileMaker... that FileMaker can't remember which window it should be importing from (i.e. it SHOULD be importing from the frontmost window), so it imports records from one of the background windows which always has a found set of ALL RECORDS.

                   

                  3. Changing the file path does NOT fix this bug, so it's not worth it to approach this bug by trying to change the file path. The bug happens under all 3 of these filepaths: (1) relative filepath, (2) hardcoded filepath all the way to the server, (3) variable set with filepath. Since the bug happens under all 3 circumstances, changing the filepath will not workaround this bug.

                   

                  4. Rebuilding the script from scratch did NOT fix this bug, so there was no bad "legacy" stuff within the script.

                   

                  5. In our solution, our users use a "temporary launcher file" to open this database each morning on the server. Several developers believe that this bug crops up when the users open their FileMaker database using a "temporary launcher file" that has more than one filepath to the file embedded into its "Manage External Data Sources..." dialog. This is exactly our situation. I don't truly believe that this is part of the problem, because the "Import Records" script step continues to fail even when the filepath in the "Import Records" script step is relative.

                   

                  6. Another developer believes that the problem might have something to do with multiple users attempting to run this import script at the exact same time. Even though each user has their own found set of records in their own set of windows on their own individual machines, we do often have 4 or 5 users running this script at the exact same time. Maybe THAT is the bug which trips up FileMaker Server on this issue?

                   

                  7. It's also possible that we have some networking problem at this client's location, which is tripping up FileMaker during the "Import Records" script step.

                   

                  So, the end result is that I'm not really sure what could be CAUSING this problem, especially since it works perfectly fine about 99.8% of the time. But at least we now know that the definitive solution is to always EXPORT records before trying to IMPORT them again into the same exact file.

                   

                  Thanks,

                  Scott

                  • 6. Re: Import Records script step fails?
                    TSGal

                    scottworld:

                     

                    Have you seen this error occur with local files?  Or, has this import failure always occurred with hosted files?

                     

                    Since you mentioned you obtained information from "another discussion forum", what is the forum so I can read the information and observations?  This may help pinpoint the cause.

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: Import Records script step fails?
                      mathepac

                      This query is mis-directed. I did participate in the discussion but the attachment is not my email or post.

                      • 8. Re: Import Records script step fails?
                        scottworld

                        Hi TSGal, this import failure NEVER occurs when the file is local. It ONLY occurs when the file is hosted. The other discussion forum is the private email discussion list for FMDiSC (FileMaker Developers in Southern California). I have taken the email thread, turned it into a PDF file, and attached it below.