automated records import

[bobydick]

hi...

i know this is probably discussed before but can not find it in this forum and google doesn't return anything useful. i need to be able to update a database/table automatically when the data is available. basically need to import tabbed text files into a table at certain hour every day. using cron maybe? this has to happened on the server and the file is being opened remotely (workstations). one other issue is - although that can be resolved externally - the name of the file varies - it has the 'yesterday' date in the name.

is it possible on the server 9 for this to happened while the file is being viewed/opened on any given number of workstations?

any input/direction would be appreciated...

thanks...

[Fitch]

FM Server can only directly run script steps that are "web compatible." Import is not.

So you'll have to run a copy of FM Pro on the server or (preferably) a workstation.

From there you can use cron to launch a FM file that runs a script on startup to do your import.

Or you could use AppleScript and a watched folder to run your import when new text files arrive.

[bobydick]

so in any case i'd have to have client installed on the same machine as the server? even if i use applescript?

can i run it while the file is open on a workstation?

[Fitch]

These days it's OK to run FileMaker Pro client on the same machine as server, although it's not "best practice" to run anything but FMS on that machine.

Whether you use AS or not, you need a client running somewhere, server or not. If you run it on a workstation, it should be one that's not in use, or else the person using it may be rudely interrupted. (If the cron job runs late at night that could be OK.)

While the file is open... not sure what you mean. When you import into a file, that file has to be open. It doesn't matter whether it's open or not when you run your script, because basically what we're talking about is creating a simple FileMaker file with a single script that runs at startup:

Perform Script( "import" from file "target" )

Close File( current file )

[bobydick]

ok. thanks.. i'm writing the applescript now. it needs actually to look into an array of file paths... will see...

[bobydick]

how do i use applescript to tell filemaker to open a file and run a particular script in that file?

thanks...

[Fitch]

tell application "FileMaker Pro Advanced"

	  open "Hard Drive:Applications:FileMaker Pro 9 Advanced:English Extras:Apple Events:Apple Events Reference.fp7" with passwords "" for Accounts "Admin"

	  do script FileMaker script "Introduction to Apple Events"

end tell

[bobydick]

thanks

[bobydick]

cool... so i wrote this applescript.app that i can trigger with cron. now what happens is that the script starts and it opens filemaker pro but it doesn't do anything. the applciation icon fo filemaker in the dock is just jumping. if i click on it the script finishes executing.

so i guess i have to "bring it to front"?! or something like that....

thanks....

[Søren Dyhr]

Well your drive might be called something else, and I then suggest letting Finder open the document instead:

set old_delim to AppleScript's text item delimiters

set AppleScript's text item delimiters to ":"

set tt to (path to desktop) as text

set tt to text items of tt

set AppleScript's text item delimiters to old_delim

tell application "Finder"

	open first item of tt & ":Applications:FileMaker Pro 9 Advanced:English Extras:Apple Events:Apple Events Reference.fp7"

end tell

tell application "FileMaker Pro Advanced"

	do script FileMaker script "Introduction to Apple Events"

end tell

...while this works in my end of the matter, am I pretty sure more elegant ways exists?

--sd

[comment]

I am puzzled why don't you use a real Filemaker file with a script set to run at opening, instead of AppleScript.

[Fenton]

Just for fun, a machine-independent method to find FileMaker's path (if FileMaker is running):

tell application "System Events"

set the_processes to (every process whose creator type is "FMP7")

set theApp to ((file of item 1 of the_processes) as alias) as file specification

end tell

[Fitch]

I am puzzled why don't you use a real Filemaker file with a script set to run at opening, instead of AppleScript.

Agreed. That was my first recommendation. I mentioned AppleScript in conjunction with the watched folder idea, but I think it may have gotten things off track.

[bobydick]

I am puzzled why don't you use a real Filemaker file with a script set to run at opening, instead of AppleScript.

because the script needs to be run only once a day at a certain time to update databases on a filemaker server. since i don't know any other way of automatically updating filemaker databases but using the import function i had to do a small import script in each of those filemaker files and the way to trigger those scripts (again the only way i know) is applescript. so what i did is an applescript (app) that tells filemaker pro to open those files, run the import scripts and then quit filemaker. i can run this applescript app from cron at the time i need. but when i test it on te command line, as i said, the applescript hangs after opening filemaker pro. until i click in the jumping icon. right after i click it, everything happens very quickly and the script finishes its job...

Edited July 9, 2008 by Guest
misspellings

[bobydick]

thing is if i run the applescript in the Script Editor everything works fine but if i make the script into an app and run it on the cli it hangs after opening FileMaker...

any ideas?

thanks...

[Fenton]

Could it be that the (stupid because it is not configurable) dialog comes up asking you what file you want to open?

There is more than one way to activate/launch/run FileMaker. "Activate" is the default. "Launch" (or "run") will start FileMaker without the dialog (but not bring it to the front). I have an AppleScript droplet, with the simple combo of both, to launch FileMaker without the dialog, then bring it to the front:

tell application "FileMaker Pro Advanced"

launch

activate

end tell

[Søren Dyhr]

any ideas?

There are 3 checkboxes in the compilation dialog, checking them all just to be sure - might not be the option here??

--sd

[comment]

the way to trigger those scripts (again the only way i know) is applescript.

Here's another:

Perform Script [ "Import" from file: "MyDatabase" ]

Close File [Current File]

[Fitch]

I already suggested that in the 4th post in this topic. I think maybe bobydick is not seeing the distinction between a FileMaker script and an AppleScript.

- We are talking about a local FileMaker file, not a hosted file.

- This file can be located anywhere on the FileMaker Pro client machine (which may also be the server machine if you can't spare a client computer).

- The timed launch of this file can be controlled in various ways, e.g. iCal or cron. It is NOT controlled by FileMaker Server.

- When launched, the file will run its startup script

- The script will resemble what Michael and I posted earlier. It's a FileMaker script, not an AppleScript.

Does that make sense?