scottworld

Import Records script step fails?

Discussion created by scottworld on Oct 27, 2016
Latest reply on Jan 23, 2017 by 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

Outcomes