Scheduled Import Script - Going Insane

[plarkin]

I've had Filemaker 10 Server for quite a while now. The reason I updated to it was to do server side imports. After months, I cannot get this feature to work on my existing databases.

I do import/updates based on a matching ID field. If I make a test database with a couple fields and a few records, I can successfully get an import/update script to run on schedule.

My actual production databases just stall. The Filemaker Server admin app just says "Running" and the script never updates anything. I have no idea what is happening. Also, there seems to be no way to end this script once it is stuck in the endless "Running" status.

I think I got over my initial problem of where the data file to be imported needs to go and how to reference that file in the database script.

Is there anyone who has successfully conquered this seemingly quirky, undocumented feature? I really REALLY need this feature as a lot of my projects are dead in the water and stuck in the Filemaker 5/6 world.

[andidale]

Hello,

I am having pretty much the same issue - Server-run FileMaker scripts designed to move data from one data base to another. The Scripts run when manually set off; all of the steps say they are 'server compatible' however, the data never moves. On Nov. 1 it stopped sending 'script completed successfully' messages and when checked and the Script was stuck in "running" status.

Any ideas would be appreciated!

[Steven H. Blackwell]

This article may be of help to you both.

Steven

[plarkin]

Thanks. I actually read your article last year and it got me started in the right direction. Using your directions, I was able to create a simple database with a simple import file and do a scripted/scheduled update. However, when I do exactly the same method on one of my large production databases, it just says "running" and never does anything and never completes.

[andidale]

Thanks Steven, I found your article after my post - I will give that a try and get back.

Best,

Andrea

[Wim Decorte]

Check the event log, does it indicate that the script ran into FM errors? Typical errors that would produce this "stuck" schedule are validation errors.

[plarkin]

Yes, the event log gives me the very informative:

"Scripting error (100)"

The script has been whittled to the bone at this point. It is only two lines:

Set Variable [$varpath; Value:"file" & Get (DocumentsPath) & "import_file.txt"]

Import records [No dialogl "$varpath";Update matching;Windows ANSI]

[bcooney]

It looks as if you've defined your path incorrectly.

See http://fmforums.com/forum/showpost.php?post/318776/

In Help it says that Server returns a path that does not have a trailing "/". I can't test this right now, but that could be it.

Edited November 11, 2010 by Guest

[plarkin]

Still gives a scripting error with the slash.

This is maddening.

Edited November 11, 2010 by Guest

[bcooney]

OK. Can you set a field in a single rec table (not a global) to the $varpath so that you can see it? Does it have "file:" and not simply, "file"?

[plarkin]

It did not have the colon. I added it and now the path is:

file:/Server HD/Library/FileMaker Server/Data/Documents/import_file.txt

But still gives a scripting error. So I tried adding in a "mac:" so the path would be:

filemac:/Server HD/Library/FileMaker Server/Data/Documents/import_file.txt

And that worked BUT the script died with a new scripting error. This time 729 which according to a reference I found online " Errors occurred during import; records could not be imported."

The odd part is that looking at the database, it appears that the proper 2032 records were updated.

Edited November 11, 2010 by Guest

[plarkin]

Also, another issue I'm having is the "Running..." error that never ends. Aside from the import routine being discussed, I had a loop to set a single field in all records to 0. This caused the schedule to say "Running..." and never complete and never do anything.

Show All Records

Go to Record [First]

Loop

Set Field [DB:status;"0"]

Go to Record [Next; Exit After Last]

End Loop

...and then the import part starts.

[bcooney]

As Wim suggested, any validations? What priv set rules do you have--is the account running the script limited? How about checking Run with Full Access?

Remember also, that your loop's Set field will fail if the record is locked.

[plarkin]

I'm not sure if you are addressing the error 729 that appears without the loop or the "Running..." message I get with the loop.

It's impossible for any records to be locked as this is a test database and I'm the only person using the file.

The only field that is validated is a "Required" field that is the primary key to which I'm matching on to do the update.

I am running with full permissions.

The strange thing is that I run this script every night for the last 10 years or so on a Filemaker 5/6 database without issue. I use a script bot to do it.

Edited November 11, 2010 by Guest

[bcooney]

"The strange thing is that I run this script every night for the last 10 years or so on a Filemaker 5/6 database without issue. I use a script bot to do it. "

Comparison between a robot and FMS11 isn't really legitimate.

Re the 729 error, are you certain that your source file does not contain records that aren't matched and therefore not imported. (Your settings are Update Matched with Add New, correct?)

The "Running" error, Wim suggested, can be caused by a validation.

I pointed out the rec lock so that you handle it accordingly when you deploy live.

[Wim Decorte]

How do you know if the records are imported? You wouldn't be able to tell by the record count because you're doing an "update matching". Did you check the option to import records that are not matching?

[plarkin]

"The strange thing is that I run this script every night for the last 10 years or so on a Filemaker 5/6 database without issue. I use a script bot to do it. "

Comparison between a robot and FMS11 isn't really legitimate.

Re the 729 error, are you certain that your source file does not contain records that aren't matched and therefore not imported. (Your settings are Update Matched with Add New, correct?)

Yes there are records that don't match. That's why I Add New Records. It's a database of people and their associated demographics. New people are added and existing people's demographics are changed.

The "Running" error, Wim suggested, can be caused by a validation.

I pointed out the rec lock so that you handle it accordingly when you deploy live.

The Record Lock thing has bitten me in the past on the robot.

[plarkin]

How do you know if the records are imported? You wouldn't be able to tell by the record count because you're doing an "update matching". Did you check the option to import records that are not matching?

A couple of ways. First, yes, there are more records in the import file so I "Add New Records." But more importantly, I change data in the import file and look for that change in the database. This is all in a test environment. I can't afford to use this product yet in production.

[plarkin]

So does anyone know if it is possible to get Filemaker Server to give more information than "Filemaker Script Error?"

I changed everyone's name to "X" in the database, let the schedule run the update, the console displays "Filemaker Script Error" yet everyone's name was fixed. I then DELETED every record, let the script run and all records were restored and did not get the "Filemaker Script Error." I get "OK."

So, it must be something to do with the UPDATE action and not the ADD NEW RECORDS. I turned off the only field which was required and still get the error.

I really wish I knew. The script seems to be running and updating as it should but I'm afraid to go into production with something that won't show a "Last Completed" date and gives an error.

[bcooney]

The only thing that comes to mind is that errors are broadly defined by FM. For example, if you have a server script that includes a loop with go to next, exit after last, you'll get a error (when FM comes to the last record and tries to go to next -- that throws an error).

So, perhaps you have an innocuous script step that throws an "error."

Edit: You can use Set Field myResult , get (lasterror) in a server script. then, check out the field.

Edited November 24, 2010 by Guest

[plarkin]

That's the problem. How to find the error if it doesn't give you any indication of where to look?

I do not have a loop or an exit after last step. But if I delete all records and then let the update script run, it doesn't give an error at all. It gives "OK." If the database has records in it, the update script always ended with "Filemaker Script Error" but seems to do all the updates.

Edited November 24, 2010 by Guest

[bcooney]

Well, when it updates matching and then adds new, perhaps at the last match and before it adds new, FM throws an error? I don't know either.

Just guessing at this point and suggesting that you really don't have an (bad) error.

[Wim Decorte]

So does anyone know if it is possible to get Filemaker Server to give more information than "Filemaker Script Error?"

The event log will tell you exactly what FM error was raised. You can look up the error description in the FM help, they're the same numbers as what get(lastError) returns.