1 2 Previous Next 18 Replies Latest reply on Jul 17, 2017 4:48 AM by wimdecorte

    How to Systematically Retrieve Records from an Integrating Program via ODBC?

    kmrphyrenascent

      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.

      1. 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.
      2. 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.
      3. 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.
      4. 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!

       

        • 1. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
          wimdecorte

          kmrphyrenascent wrote:

           

          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?

           

          The full (non-Cloud) server supports server-side schedules.  You don't want to rely on the on-Timer script trigger for any server-side activity.  The On-Timer is client-side only and not really a good idea to perform tasks like fetching data on a schedule.

           

          FMC (FileMaker Cloud) supports ESS connections to the supported back-end databases, but does not support regular ODBC connections server-side.  I think you'll need to forget FMC for your deployment unless you can use ESS.

          1 of 1 people found this helpful
          • 2. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
            wimdecorte

            kmrphyrenascent wrote:

             

            1. 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.

             

             

             

            If you have full control over the coding in that other system you may consider pushing data into FM instead of having FM pull data.  FM has an excellent XML API that can be used for this.  It sounds like the events are already taken care off in that other application so if you can add code there then that may be easier for this.

             

            If the code in the other app is in .NET then have a look at fmDotNet on GitHub, it's a .NET wrapper around that FMS XML API.  Saves you from having to install FM's ODBC drivers too.

            1 of 1 people found this helpful
            • 3. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
              kmrphyrenascent

              Thank you for your response.  I'm obviously rather new to the FileMaker space.  I wasn't aware that FM Cloud did not support regular ODBC connections.

               

              I do remember reading about the scheduler that's available in FileMaker Server.  My understanding is that it can be used to execute a script every X minutes.  In our case, we could set it to run the same script to check the Inbound Staging Tables for new records every 3 minutes.  Do you know of any reason that would not work?

               

              In our case, the external application is pushing the data to (what will be) the FM Inbound Staging Table.  So, we are already getting data pushed to us every 3 minutes, but only to the Inbound Staging Table.  The FM script that we need to run every 3 minutes will read new records inserted to the Inbound Staging Table, apply logic and copy the data into our main Application Tables.  So, in this scenario, I don't believe the ESS functionality will help us.

               

              The integration to our Dispatching application is the standard method that our Dispatching application vendor recommends and supports.  I wouldn't want to change it.  FM Cloud has a lot of nice features, but if we can't integrate with it using this method, then deploying to FM Server (either on premise or with a hosted solution) would be options for us.

               

              Thanks again for your very helpful response.

              • 4. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                kmrphyrenascent

                Thanks for your response.  I will keep it in mind for the future.  For the particular problem that we're trying to solve, we don't have control over the Dispatching application's code.  It's a packaged app (although it is written in .NET).

                 

                Their recommended method of integrating applications to their application is via ODBC and Staging Tables.  If we build our (what will be FileMaker) app to their specifications for the Integration, they will continue to support it.  That's the direction we have to go in this case.

                • 5. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                  planteg

                  Hi,

                   

                  regarding 4. This is where it get challenging, one way would be to write a small application that would do what your actual application do. This would be some kind of bridge between the FileMaker database and your custom Order Management application. Since it doesn't seem to be related to any other functionality of the entire system, this would run independently. This could be written in any development system you know like .Net and event in Python. You could even make it a service.

                  • 6. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                    kmrphyrenascent

                    Thanks for the response.  Just for clarity, the Order Management system is being re-written in FileMaker.  And the Staging tables that the commercial Dispatching application reads and writes are tables that are also part of the same FileMaker database.  So, what the FileMaker script needs to do is read one FileMaker table (Inbound Staging table), apply logic and move the data to a number of other FileMaker tables (the main Application Tables).

                     

                    Since both the source and destination tables are all within the FileMaker database, I would like to do it via FileMaker scripting, rather than via an external application.  In our current, non-FileMaker version of the Order Management application there is an "On Insert" script trigger which executes whenever a new record is inserted into the Inbound Staging table.  If FileMaker had an equivalent script trigger, I would use it.  So, I'm looking for a way to replicate the same functionality as the script trigger.

                     

                    At this point, I believe my best bet is to switch from FM Cloud to FM Server and use the FM Server scheduling functionality to execute the script every 3 minutes that moves data from the Inbound Staging table to the main Application tables.  Unless you or someone else is aware of a better solution.  Thanks again.

                    • 7. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                      planteg

                      Both the source and destination in FileMaker. Got it.

                       

                      The I think the best way is to run a script on the server. FMC may get the the possibility to run scripts, but that's no the case at this moment. Maybe the next version coming up later this year.

                      • 8. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                        kmrphyrenascent

                        Do you know when a new version of FM Cloud will be available later this year (which month)?

                        • 9. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                          taylorsharpe

                          Only this past year did FileMaker even announce a roadmap for the first time, but they never announce a head of time when releases will be out.  However, the annual FileMaker Developer's conference is in 2 weeks and that has a high possibility of being announced then.  But it is a total guess and I have no inside information. 

                          • 10. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                            taylorsharpe

                            By the way, ESS really is a pretty slick way to "sync" data.  Forget the 3 minute script sync, just do it live with ESS.  But you could sync with ESS.  Also, why are you using FMCloud?  Syncing data between servers is so much faster if done on the same LAN.  I would be putting the FileMaker Server on a server on the same LAN that your Order Management System server is on.  Plus, you wouldn't have the ODBC limitation of FM Cloud.  But then again, as a FileMaker professional, I would first be trying to get ESS working over the manual sync you are doing in a script. 

                            • 11. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                              wimdecorte

                              The release notes for the version of FMC that was released this week states that a new version, based on FMS 16 will be available in October this year. No indication that it will support server-side schedules.

                               

                              Get Outlook for iOS<https://aka.ms/o0ukef>

                              • 12. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                                monkeybreadsoftware

                                Well, if you use MBS FileMaker Plugin with the SQL functions, you could connect via ODBC or directly to your db server in a script. Than copy the records in one or other direction as needed.

                                 

                                We support Centura SQLBase, DB2, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase.

                                 

                                and of course this could work on FileMaker Cloud if you install the right linux driver packages.

                                • 13. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                                  wimdecorte

                                  Christian Schmitz wrote:

                                   

                                  and of course this could work on FileMaker Cloud if you install the right linux driver packages.

                                   

                                  Which is not something you should do on FM Cloud.  The only things that you configure on FM Cloud should be the things that are in the FMS admin console.  Anything else that you install bypassing the admin console may get wiped out at any time without any notice because of the nature of the FM Cloud instance management.

                                  Not what you'd want to rely on in any kind of production setup.

                                  1 of 1 people found this helpful
                                  • 14. Re: How to Systematically Retrieve Records from an Integrating Program via ODBC?
                                    kmrphyrenascent

                                    Thanks to all for sharing your thoughts.  It really is very much appreciated.

                                     

                                    ESS is interesting and I think I see how it could enable us to integrate with the Dispatching application.  Unfortunately, it would mean a redesign of our interface, which we would prefer not to do.  It would also mean that the software vendor for the Dispatching application would have to be willing to give us a view to their tables, which I'm not sure they would do.  Today, we give them ODBC access to our Staging Tables, which we should be able to do with FileMaker Server. 

                                     

                                    Since this is a migration effort from one platform to another, I believe the best approach is to keep the interface to the Dispatching application unchanged, which means that new records will be inserted into our Inbound Staging table once every 3 minutes.  The scheduling functionality in FileMaker server should enable us to run a script that checks the Inbound Staging tables every few minutes and pull the data into our main tables.

                                     

                                    Thanks to all who have commented.  I have learned some very valuable information through this exchange, especially the important limitations in FM Cloud.

                                     

                                    1 2 Previous Next