Automatic Queries of PostgreSQL Data
Hello wise forum folk,
We have Filemaker Server Advanced and about 35 client machines (many of which don't have FMP installed, but use IWP to access some of our databases). We also have a lot of our company's data stored in a remote PostgreSQL database which I have read_only access to.
I have been asked to create a new database which queries the PostgreSQL database and provides various reports to the end users. Many of the reports need to be utilizing "up to the minute" information.
I have the Actual Technologies Open Source ODBC driver configured on my own work computer, and also on the machine which is running Filemaker Server Advanced. I am able to run queries locally on my work computer using the "import records" script step. My approach so far as been to import most of the needed data from the PostgreSQL database to local FMP tables and then work with the data to generate various reports. This works great for me using the database locally, but I am trying to create a solution that will be hosted on the server and will accommodate multiple simultaneous users.
So my question is this: How can I create a situation where the data is regularly being updated via ODBC queries such that I don't have to install the ODBC driver on every single client computer? I also want an option for people to view reports via IWP, which doesn't work with the "import records" script step. Unfortunately I can't utilize the ESS feature since the remote database is PostgreSQL (which isn't currently supported).
My ideas so far are as follows:
1> Have a server side script that runs automatically throughout the day which simply updates the local data by running an "import records" which utilizes the ODBC driver installed on the server machine. I can't figure out how to make this work. Is it possible?
2> Add an "import records" step to the script which runs on opening of the file. Then data is current as of when each user logs into the database. I also cannot figure out how to make this work using the server's ODBC driver. Possible?
3> Some other solution that keeps data fairly updated and possibly allows users to manually refresh the data to be current. I am not sure what options exist.
I am open to whatever possibilities exist that would work well for the situation.
Thanks in advanced to anyone who can help in this matter!