I have a Filemaker solution for a school which includes an iPad sign-in-sign-out system. It’s tied to all sorts of other student data, this all works locally in their building and is firewalled off from the rest of the world.
They also have a Learning Management System (LMS). We currently pull various data from the LMS nightly (attendance, credits, enrollment, etc) as CSV files and import them into Filemaker.
They are building a new LMS from scratch. We need to pass the Sign-in information to the system. We’ve been planning on using HTTPS POST to push every Sign-in interaction to the system…. but I’m wondering if we’d be smarter instead adding their SQL table to our Filemaker solution and have Filemaker make sure that table stays in sync with our Filemaker table. There would be subtle differences (i.e. the SQL table would use SQL timestamps, not Filemaker timestamps).
My gut feeling is writing directly to their SQL table would be less fragile, but am I wrong about that? If I made a table in Filemaker that was an exact match of their SQL table, is there some super stable way to just have them stay in sync (Filemaker being the master record)? Plugins are fine.
This isn’t a mission critical sync, so maybe we just stick with the HTTPS / POST solution knowing that it will fail sometimes and that’s OK… but wondering if there’s a “Better way”? Also wondering about replacing our nightly CVS pulls with just connecting to their SQL database, so we had instant attendance for example (the sign-in is when students sign into a building, the attendance is per class, so they are different FWIW).
We've had a FileMaker system linked to a MySQL database web backend as an external external data source for about 10-years using the Windows server ODBC connector. Operationally, it has been pretty much 100% reliable subject to the SQL server not going offline for maintenance or upgrade. The FileMaker files are hosted online in the UK with the SQL tables hosted in Germany (originally Ukraine, but for obvious reasons that had to changed). Over the Internet there is a performance penalty, but the client has been very happy with it, and if both were on the same network we wouldn't expect this to be an issue.
You are correct in that you can reference and interact with the SQL data directly using all of the standard FileMaker tools such as layouts, relationships and scripts, subject to access privileges on the SQL side.
Our biggest frustration with this approach has been the behaviour of the ESS references when it comes to needing a development copy on another server. This was all setup using a much older version of FileMaker and we haven't had cause to investigate whether this is still the case, but swapping over from the live ESS source to a test one required every table occurrence to be edited. This wasn't practical with this solution due to the number of TOs within the solution and most changes have been carried out on the live version, or a copy without the external data. Just swapping the external data source didn't work.
Nowadays it is our preference to use APIs rather than have direct links to other data sources, but that is not to say that ESS doesn't remain a viable option.
1 Like
This is the target for Claris Studio. Have you looked at that option?
Idk what that means. We have a Mac mini with file maker pro server and their LMS is on an Amazon web server I believe.
It looks like it’s just a bunch of web forms? It says “enhanced data integration” but I don’t see anything about connecting to SQL databases. Is there something I’m missing. I’m not doing anything web, except possibly calling out to the Amazon Web Server API. No incoming web calls to my Filemaker solution.
This is good information. Esp thinking about switching between live and dev servers… Thanks
Studio is based on Mongo, rather than SQL. It's pitched at people using web forms because that's a common requirement. The aspect that I thought might suit you is that it integrates an external data source with FileMaker and provides an IDE for that. I haven't experimented much but I assume that the Mongo database could used by any web page that you want to setup.
Oh. Gotcha. I’m pretty sure the LMS is a SQL database. Thanks though.
You could also use JDBC that’s a free and open standard. FileMaker’s JDBC driver isn’t that fast, or even complete, but it’s a viable alternative to ODBC and JDBC is what I use since I use JDBC with enterprise-level DBs.
Do not be intimidated by the simple “boilerplate” code for JDBC. JDBC is actually quite simple.
You can also extract metdata (field names, table names, …) and do DDL commands like creating tables fields, etc. (using ALTER TABLE).
JDBC is awesome.