I’d like to run a design problem by the Community and request ideas for the best approach.
Big Picture. We have written a Microsoft Access Web App (hosted in SharePoint Online), which performs Order Management. Our custom Order Management application currently integrates with a Commercial Dispatching application via an ODBC connection. Everything has been working very well, but because Microsoft is dropping support for Access Web Apps, we are rewriting the Order Management application in FileMaker with a critical requirement that it interface with the same commercial Dispatching application using the same ODBC interface. We are currently developing the FileMaker application in FileMaker Cloud and we would like deploy it to FileMaker Cloud when it is complete.
How the interface works. As you can see in the attached diagram, the interface works by means of Staging Tables, which are part of the of the Order Management application, but which are available to the commercial Dispatching application via a Read/Write ODBC connection. Referring to the numbers on the attached diagram.
- When a user in the Order Management application needs to send the order to the dispatching system, a script copies the Order record to the Outgoing Staging table within the Order Management database. I believe we can replicate this functionality by tying a script to the user interface action of sending the order to the Dispatching system.
- Every 3 minutes the Import/Export Script copies the Order record from the Outbound Staging table to the Dispatching application’s tables. Since the script operates outside FileMaker and relies on ODBC (which FileMaker supports), this should work.
- Every 3 minutes the Import/Export Script copies Delivered Order records from the Dispatching application’s tables to the Inbound Staging Table. Since the script operates from outside FileMaker and relies on ODBC (which FileMaker supports), this should work.
- This is where it get challenging. . . Currently, in the non‐FileMaker solution, an “On‐insert” script detects that a new record has been inserted into the Inbound staging table and copies the record to the Application Tables (and performs other logic).
Question 1 – Since there is no “On‐insert” capability in FileMaker and since FileMaker Cloud does not support On Timer based scripts, how can records be systematically copied from the Inbound Staging Table to the Application Tables throughout the day, say every 3 minutes?
Question 2 – If this is not possible with FileMaker Cloud, is a better solution to use FileMaker Server so that the On Timer script trigger can be used?
Any other thoughts on how to approach the systematic importing of records into FileMaker via ODBC would be welcome. Thanks!