[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1630
  • Last Modified:

Automate Script in FileMaker

I have a DTS in SQL Server Enterprise Manager that runs and gets records from an Oracle database and moves them into a MS SQL database.  Then in FileMaker, I have a Script that IMPORTS RECORDS from the SQL table into FileMaker.  This all works, my only issue is a way to automate the entire process.  I would use the Admin Console from FileMaker Server 9, but the "Import Records" script step isn't supported.  Any ideas?

Thank you,
David
0
davidaarong
Asked:
davidaarong
  • 7
  • 4
1 Solution
 
davidaarongAuthor Commented:
Specs:
   FileMaker Advanced Server 9
   SQL Server 2000
0
 
lesouefCommented:
First, why not import from oracle direct?
Second, fm server 9 allows to schedule shell scripts AND filemaker scripts.
So do the script to import from yr SQL source, and put it in yr fm server schedule using the admin console.
Also, don't forget that fm can work on SQL sources direct without importing records, so you may work on SQL data directly (if suitable for yr app).
0
 
davidaarongAuthor Commented:
Q:First, why not import from oracle direct?

A:The data connection from Orcale to FileMaker is painfully slow.  So to avoid this I just run a DTS daily at night to dump the records from Oracle to MS SQL.  

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
davidaarongAuthor Commented:
"So do the script to import from yr SQL source, and put it in yr fm server schedule using the admin console."

That's what I tried to do.  I tried to schedule the script to run using the FileMaker Server Admin Console, but errors out since the most important step in my FileMaker script is "Import Records", which is not supported (i'm guessing, because the script step is not Web Compatible).
0
 
davidaarongAuthor Commented:
so how would i create a shell script to launch FileMaker and then launch a script within filemaker to run?
0
 
lesouefCommented:
you don't have to, fm server is suppose to do it all, and import is supported in a script.
this has nothing to see with the web limitations, it is run by the server direct.
does yr sql connection need a passwd or is it saved?
0
 
davidaarongAuthor Commented:
Yes, my SQL connection needs a password, and this is saved in the script step of Import Records -- however, i tried to run the script via the FM Server Admin Console but it state, "ScriptMaker Script aborted"
0
 
davidaarongAuthor Commented:
Error      643      FILEMAKER_SERVER      Schedule "Corp_Acct_dump_from_PS" aborted; unsupported script step.
0
 
davidaarongAuthor Commented:
so these are the script steps in the script:

Go to Layout
Show All Records
Delete All Records [No dialog]
Import Records

And that's it...
0
 
lesouefCommented:
F####### hell! you're right, I just read the doc about scheduling filemaker scripts, they are limited to web compatible actions. So > trap.
I have an old way to do this, a bit hairy, but it works (tested):
Have a fake filemaker file with only one script, set as the startup script, which calls your import script in the real database. And put this in the windows schedule of any client machine. drawback: a pc must be on, with an open session to have it work (can be locked though). You can also adapt this method by not scheduling it, and managing to get it triggered by the 1st client who connects everyday (you log it's been done and disable the script for further connections the same day).
Other way (not tested, just an idea): do it full SQL: if you set filemaker as an ODBC source (you have the adv version, so odbc is included), you can write an SQL script to read from the SQL server and write to Filemaker using a command line SQL tool for instance, and put this in the server win schedule. Or use a way you are more familiar with like php.
And about the oracle odbc access, I am surprised this is slow (never used it myself). I guess SQL server is using a direct access instead of using the odbc driver... what a waste, DB2 is the same, incredibly slow, just cannot be used on a regular base, takes ages to dump 100 lines while MSSQL or mySQL odbc drivers are damned fast.
Did you use the filemaker supplied odbc driver or anything else?
0
 
lesouefCommented:
you could also have filemaker to work directly on MsSQL data wihout importing data since fm9
0
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now