Execute SQL Script Step - File Maker

There’s a script step that allows you to send a SQL command right out of FileMaker and, via ODBC, right into an external data source. While this doesn’t allow you to get data in to a FileMaker Pro database, it can allow you to take action on an external data source. For instance, you could set up a solution such that every time a record is created, edited or deleted in a FileMaker database the same happens to that record which is being replicated in an Oracle database. Or, you could use this script step to archive old records in a FileMaker database into an external data source for later retrieval, if necessary.

This is a good example to use to explicate the use of the Execute SQL script step.

Now you should look at the “Darn Good Security System” solution again. Fire it up (logging in with admin/full access) and go to the main menu. Click the System Administration button and go to the Log tab.

Say that you want to have a button that automatically archives log entries to a MySQL table after they are more than a year old. In order to do this, you’ve added a button to this layout called Archive. Press it and see what it does (assuming you’ve copied the Test folder of databases into your MySQL’s /data folder first). After that, take a look at the “Archive Log Entries” script in Menu.fp5.

Basically all this script does is ask you if you’re sure you want to archive the entries and if so, it runs the subscript in Log.fp5 that finds the right records (ones that are more than one year old) sends the records over to MySQL and deletes each record out of Log.fp5 if there are no ODBC errors.