When using FileMaker databases in the Network there may be a requirement for processes to be executed on demand or automatically time-controlled by a client in the local network. The application areas of a process engine can be very diverse. If your FileMakerDatabase access from outside via WebDirect you can use a process engine to create documents, for example, and make them available for download as PDF files. As the process is executed on a client in your local network, the files created can easily be saved on a network drive within the local network. Another area of application is so-called cron jobs - i.e. processes that are executed by the process engine at a predefined time and possibly at certain intervals. This article explains how you can implement a simple process engine with time-controlled cronjobs and an execution log in FileMaker.
Table of contents
Virtual employee with own FileMaker client
The starting point for a process machine is your own Mac or Windows PC with an installed FileMaker client. In FileMaker, only one layout (aut.tasks) is called up, which is created with a Script trigger was provided. As soon as the Table of the layout aut.processes a new data record is created, the operation machine starts processing the operation supplied in the data record.
Activity table and log as a basis
In order to execute processes and save them as a log, you first need two new data tables:
- Processes (1 data record = 1 operation for processing for the operation machine)
- Process log (1 data record = 1 operation processed by the operation machine)
The table Processes does not normally contain any data records and is filled with a data record under script control at the moment when the process machine is to carry out a specific process. As soon as the process has been executed, a new data record is created in the log and the data record is saved in the Processes deleted again.
The process machine is listening...
In order for the process engine to know in real time that a new process is to be executed, we need a corresponding layout that contains data records from the table Processes is displayed. In our example file, this layout is called aut.processes. This layout has a layoutScript-trigger (BeiDatensatzLaden), which executes the script for processing the operation as soon as a new data record is created in the Processes is located.
If a new data record is added to the Processes is added, the process engine automatically executes the script lay. process machine with the script trigger trigger.layout off. The prerequisite for this is that the process machine remains on the layout for the entire period. aut.processes as only this layout has the script trigger set up.
On the process machine, you can automatically ensure that the client accesses the layout directly after starting your FileMaker database. aut.processes changes. To do this, set a separate user for the transaction machine as the standard user in FileMaker Pro (File > File options, "Log in with"). If your solution has a start script, you can now simply intercept the user for the process engine and use a construction such as:
If (Get(AccountName) = "TransactionMachine")
Go to Layout [ "aut.operations" ]
End (If)
Please note that printing and creating PDF files is also possible in WebDirect since the FileMaker 16 platform. So if you use FileMaker Server 16 and FileMaker Pro 16 and only want to create printouts or PDF files in WebDirect, the solution presented here may no longer be necessary for your solution.
Three scripts to control all processes
Our FileMaker sample solution contains three scripts with which all processes can be easily controlled:
lay. process machine - This script is executed by the process engine as soon as the table Processes a new data record has been created. The call is made via the layout aut.processes with the layout script trigger mentioned above. The part for executing processes has been added to the script with the parameter trigger.layout so that it is possible to use this script with other parameters for other tasks as well.
Since the command that the process engine is to execute is located directly in the database Processes you can define any process commands and have them processed in this script. The basic principle is always the same:
If [ Get(ScriptParameter) = "trigger.Layout" ]
Script is only executed if it has been defined with the script parameter trigger.layout is called.
If [ Operations::Command = "Send data" ]
The following script is only executed if the transmitted command Send data reads.
Your process
You can program any operation here
Delete data record/query [With dialog: Off]
Deletes the processed data record from the table Processes
Execute script [ "ts.process log"; Parameter: ... ]
Inserts a new data record in the process log, taking into account all variables in the script.
End (if)
End (if)
ts.enter_process - Use this script to enter any operation in the Processes in. You can therefore call this script from anywhere in your database. All commands used can also be stored in FileMaker Go or executed in WebDirect. In our example, the script accepts four script parameters: Command, e-mail, data ID and the user name. These four parameters are simply added as a new data record to the Processes entered. Of course, you can extend this script individually and, for example, transmit other keys such as an offer ID, invoice ID or customer ID. In this case, you must enter the table Processes and also add these fields to the transaction log.
The script has a very simple structure: In the first part, the script parameters (separated by an apostrophe) are written in variables. For this purpose, we use the Custom Function "StringColumns". You can also use a different technology for this and transfer the parameters as a normal list, for example, which you can read in the script using the "GetValue ()" function.
ts.transaction log - As soon as the script ts.enter_process a new data record in the table Processes was created and the process machine is on the layout aut.processes the process is automatically started by the script lay. process machine is processed and the data record of the table Processes deleted. The script ts.transaction log with identical variables to create a new data record in the process log. The structure of the script is largely identical to the script ts.enter_processwith the difference that the data record is stored in the log and not in the table Processes is generated.
Master data for processes
In some cases, you may need additional master data for a process, for example to be able to send documents by email. You will then want to predefine, for example, which subject and text a corresponding e-mail should contain. For this purpose, we have created the data table Transaction master created. Each data record in this table corresponds to the master data for an operation. The value in the field Name_Process corresponds to the name of a process as it is also defined by the script ts.enter_process is used.
Optional placeholders for emails
If required, you can define placeholders in the template texts "Email subject" and "Email text", which you can replace with real data in the sub-scripts of the "lay.process.machine" script. In our example script, both texts are created using SQL-query in the variables $email_subject and $email_text written. For example, if you want to replace a placeholder {name_customer} in a text template, add the 'Replace ( )' command to the SQL query in these two variable definitions:
Variable set [$email_text; value: exchange ( SQL query; "{name_customer}"; table::name_customer) ]
In this way, you can use any number of placeholders in your email templates, which you can define in the variable definitions for the variables $email_subject and $email_text can easily be replaced by real data.
Time-controlled processes with cronjobs (plugin required)
We have also equipped our sample file for a process engine with the option of processing time-controlled cron jobs. The start and end date, the desired time and the interval can be defined in the process master. To use this function, it is necessary to use the MBS FileMaker Plugins as the command 'FM.RunScript' is required. Alternatively, the command 'BE_ExecuteScript' from the free BaseElements Plugins can be used.
Setting up the monitoring script on the process machine
All cronjobs are executed by the script cron.operation machine which you can call up via a timer script every ten minutes, for example. In our sample solution, we have included a script cron.timerscript integrated, which controls the execution of the script cron.operation machine every 600 seconds, i.e. every 10 minutes. You can integrate this script into the start script of your solution by querying the user name, for example, so that the timer is started automatically when the user of the process machine is logged in.
The script cron.operation machine first switches to the layout Transaction master and calls up all active cronjobs. An execution loop then begins, in which a check is made for each cronjob found to see whether an execution is due. If so, the corresponding cron job is executed.
For cronjobs, you can define the execution of any scripts and, if necessary, corresponding script parameters. A cronjob therefore does not execute any sub-scripts of the script lay. process machine but you can define any script including parameters for a cronjob in the process master. A cronjob can therefore theoretically execute any scripts from your FileMaker solution on a time-controlled basis.
Download sample file for FileMaker 13 to 16
Our sample file contains all the scripts mentioned in the article for integration into your own FileMaker solutions. The "Data table" table contained in the sample solution is for demonstration purposes only and should correspond to data tables from your own FileMaker solution.
Integrate process engine into gFM-Business
As part of an open license, you can easily integrate this example of a transaction machine into the gFM-Business ERP system. Owners of a gFM-Business Custom license can have us integrate the functionality of a transaction machine into their solution.
How to transfer scripts from the sample file to your solution
Scripts can be easily transferred from one FileMaker solution to another via the clipboard. Simply open the dialog Manage scripts, select the desired script with the mouse and copy the script to the clipboard via the menu [Edit - Copy] or key combination [Ctrl/Cmd-C]. Then open the same dialog in the target solution and simply paste the previously copied script using the menu command [Edit - Paste] again.
To transfer tables from the example file to your solution
Tables can be inserted into an existing solution in several ways. The easiest way is to import the table using the [Import...] in dialog Manage database. All tables to be imported from the source solution can then be selected in a subsequent dialog. This method is therefore particularly suitable if several tables are to be imported at the same time. If only one table is to be imported, this can also be done via the clipboard like a script. If a table including all data is to be imported, this can be done with the command [File > Import data records > File] to be carried out. In the import dialog, the target table must then be New table must be specified.
How to transfer value lists from the example file to your solution
Value lists can be easily imported from one FileMaker solution to another via the clipboard. Simply open the dialog under [File > Manage > Value lists]select the desired Value list with the mouse and copy the Value list to the clipboard via menu [Edit - Copy]or key combination [Ctrl/Cmd-C]. Then open the same dialog in the target solution and paste the previously copied value list using the menu command [Edit - Paste] again.
How to transfer your own functions from the example file to your solution
Click in the dialog [File > Manage > Custom functions...] on the button [Import] and then select the sample file. Select the desired custom functions in the following dialog and confirm the dialog. Please note that this function is only available in FileMaker Pro Advanced, but not in FileMaker Pro.
Markus Schall has been developing individual databases, interfaces and business applications based on Claris FileMaker since 1994. He is a Claris partner, FMM Award winner 2011 and developer of the ERP software gFM-Business. He is also a book author and founder of the M. Schall Publishers.
