8 Replies Latest reply on Aug 8, 2016 8:49 AM by GJ1159

    Recurring Imports - Best Method?



      Recurring Imports - Best Method?


      Hi Folks,


      I have a database that works with data from a read only PostgreSQL database hosted on a different server.  The FMP database is hosted on our network using FM Server 11 and is accessed by multiple users throughout the day.  The needed data is imported into the FMP database from the PostgreSQL DB via ODBC using a server side script which runs the "import records" script step.   


      The data in the remote database changes often, and I have been trying to figure out the best method for importing the data so the FMP database matches the remote database exactly.  I finally decided to have the import script first delete all records in the local FMP database, then import all records from the remote PostgreSQL database using the "Add New Records" import action.  My reasoning is that the "Update existing records.." and "Update matching records.." options won't create an exact duplicate.  "Update existing.." doesn't account for situations where there were more records on the last import (for example 10 records were deleted in the PostgreSQL DB since the last import), which leaves extra records in the local FMP database that shouldn't be there.  "Update matching.." doesn't get rid of the records that no longer exist (records in the PostgreSQL DB are often deleted).


      Finally the question!  I have the import scripts running many times throughout the day.  

      1> Can it negatively effect the file to have massive amounts of data constantly being deleted and re-imported constantly?  Currently the import scripts delete and replace roughly 200,000 records with each import.  

      2> There is also the issue of slowness.  The import process takes a couple minutes to complete.  I would love to figure out a faster method of doing the import process if possible.


      Many thanks to anyone who can offer advice!


        • 1. Re: Recurring Imports - Best Method?

          I'm still looking for an answer to the above questions.  I am certain someone in this forum could answer them.  :)

          • 2. Re: Recurring Imports - Best Method?

            I've found problems with the 'Delete all & re-import' using FM9 and ODBC, from an Oracle database.  The FM database would only contain about 20K records, but many times users experienced freezing of the FMP application while the delete and import was taking place.  I solved the issue by using the Update Existing import method, and importing an extra 'flag' field.  Before the import, the flag field is emptied.  Then I import an arbitrary value into this field through the ODBC import.  After the import, I do a find for any fields that don't have the flag field populated, and delete them.


            As for the speed of the import, it all depends on the number of records, number of fields, type of data, speed and load on the SQL & FM database servers.  A couple minutes for 200K records sounds about right to me.

            • 3. Re: Recurring Imports - Best Method?

              Hi Etripoli,


              Thanks for the advice!  The "flag field" method makes total sense.   I see that would make the "delete all records" step unnecessary, and therefore simplifies the import process (and possible makes it faster?).   However, in essence it seems like a very similar thing is happening on the backend.  It is my understanding that "Update Existing" simply replaces all the existing records with the imported records and then adds remaining records as new (assuming you have chosen that option).  


              So one of my questions remains:  Is it an issue to have very large amounts of data being re-written constantly?  Could this potentially affect a file negatively somehow?  When I say constantly, I mean something like 200,000+ records being re-written by the import process every 10 minutes throughout every day.


              I have also been pondering a solution where the import script somehow looks at the remote database and determines if the data actually needs to be updated or potentially just imports portions of the data.  This would make things faster and would avoid constant re-writing of data, however the remote database is VERY complex (around 60 different related tables and hundreds of various fields) and this would probably take me quite some time to figure out and implement the logic.  :)


              I have also been considering including a feature where users wouldn't have access to data during the import process by having the import script set a flag on when it starts and off when it finishes.  Buttons to various layouts could then include a script step that looks for that flag and throws a message to the user saying "import in progress, please wait".  I am thinking this may help to avoid any unforeseen issues with people accessing data while the import is happening.  


              Anyone have thoughts to contribute?    Thanks much!

              • 4. Re: Recurring Imports - Best Method?

                I'm sure you have a good reason for providing users access through FileMakert to data stored in PostgreSQL.  But having the data updated every ten minutes sounds like they want real-time access, which might be better provided through the application based on PostgreSQL.  Because either method you use for updating the data in FM will be disruptive to users.

                Have you considered the alternative, of providing direct access to the PostgreSQL data in the FM database?  I'm not sure of it's terminology, but I would assume you could create a stored query, or 'view' of the data, based on the SQL command used to import the data.  This virtual table could then be added to the FM database through the External Data Sources, and *almost* used like any other FM table.

                • 5. Re: Recurring Imports - Best Method?

                  Hi Etripoli,


                  I agree that the best solution would be to have users directly access the PostgreSQL database through the application that uses that database.  The reason for utilizing FileMaker is due to the following:  We have a custom coded application which was created using "Ruby" which stores data in a PostgreSQL database (the one in question).  This application is very complex and has been a 4+ year project which we have invested over $200,000 into.  It involves very high level coding which we contract out to a skilled Ruby coder.  There are all kinds of requests from local users for reports, specific data views, etc and to create that functionality within the main application would be very expensive and take a fair amount of time (because of the somewhat bureaucratic process we go through to implement code upgrades).  I decided to go ahead and accommodate some of the simple requests by building a FMP database to run queries and provide the reports, etc that people want.  Eventually we hope to get the more important queries/reports/etc implemented into the main application, but having the FMP database in the meantime is very useful and allows be to create new queries/reports on the fly.  


                  I wish I could provide more direct access by utilizing ESS, but unfortunately PostgreSQL isn't supported <sigh>.  I believe the alternative method that you suggest is not possible because many of the users are accessing the database via IWP.  Even if they weren't, an identical setup of the ODBC driver would have to be on every client station (which there are many), but it really comes back to the IWP thing (which doesn't allow queries).  The only solution I came up with is to have the data imported through a server side script on a regular interval.     I suppose another complex solution could be to have a server side script that runs constantly and simply looks for data in certain flag fields which are altered by user actions.  This would be a way to have IWP users be able to trigger a server side script to do a query, but it doesn't seem like a great solution to me.  


                  Hopefully that all makes sense.  :)



                  • 6. Re: Recurring Imports - Best Method?

                    If you are able to import from PostgreSQL to FM, then you should be able to add the tables directly to the FM database.  The proper ODBC driver needs to be on the FM Server, a DSN created, and then the tables can be added through the External Data Sources in FMP when connected to the FM database.  Just to make it clear, add the DSN to the server, not the individual workstations.

                    • 7. Re: Recurring Imports - Best Method?

                      Hello Again,


                      Yes - I am clear on how to set up the server side scripts for importing to FM.  I have already set it up and is working as we speak.  I am importing the records through a server side script that runs a unique SQL query for each table.   The reason for this is that the remote PostgreSQL database is HUGE (literally millions of records) and so I have customized the import process so that it only retrieves a portion of the data which is needed for the specific reports that have been requested.  I have done thing to keep things somewhat simple in the FM Database, as well as to keep import times to a minimum, and also to keep the file size of the FM Database relatively small.


                      This is getting into a slightly different conversation which I have actually already had in this forum.  Check out the following thread if you are interested in more details:




                      • 8. Re: Recurring Imports - Best Method?

                        Anyone ever have an issue when setting up the import for the recurring import, that it doubles your records...as in creates blank entries. I have am testing this function out, but when I save my excel sheet, and go through the import, if I have 20 entries, it adds 40, 20 with data and 20 blanks..

                        I thought this had something to do with how I'm saving my excel sheet, but I've tried many different ways and it still grabs empty fields....