CODEWITHSUNDEEP
sqlsql-servermacosfilemaker
Stu412
Stu412

Reputation: 261

SQL and Filemaker Pro 13 working together

I've been handed an interesting question in that an Apple centric user would be keen to run databases on Filemaker Pro and we already have several running on MS SQL.

FM Pro is visually stunning and as a front end to work with customers would look good, but I'm more SQL at heart.

Does anybody use both? Can you easily run tasks between SQL and FM Pro to update data to FM Pro (say overnight)? Has anybody made the change from SQL to FM Pro for any purpose and found it to be ok?

Thanks in advance

Upvotes: 0

Views: 2693

Answers (2)

John Christopher Jones
John Christopher Jones

Reputation: 509

To expand on user4166144's answer a bit, you can add MS SQL as an external data source to FileMaker using ODBC. (See "Using FileMaker Pro, I want to create a live connection to a MS SQL Server, Oracle or MySQL data source.")

This will let you base layouts on an MS SQL table just as though it was a native FileMaker table. That is, the data will be "live", with no need for over-night copying about.

There are some limitations to ODBC connections, which will probably be irrelevant in your case. Mostly, ODBC data sources in FileMaker don't get all the FileMaker goodies in Manage Database. Tables from ODBC sources are "shadow tables". For example, if you delete a field ("column") in FileMaker, it doesn't get deleted in the SQL database. However, creating, editing, and deleting records all work as normal. You can even add tables from ODBC sources to the relationship graph, which is the primary way that you get data from multiple tables in FileMaker.

FileMaker is a little hard to wrap your head around coming from an SQL background. It's meant for rapid application development, and as such it has certain paradigms in mind. Here are a few things to know that I hope will help:

  • Every user interface ("Layout") in FileMaker is based on a table occurrence. The body of a layout represents a single record in that table occurrence. Every script, calculation and related piece of data is calculated from the perspective of that single record in that single table occurrence. That is, a layout is a "cursor".

  • There is no (sane) FileMaker way to do the equivalent of an SQL "OR" when it comes to the Relationship Graph.

  • FileMaker 12 has two features with very similar names. It has a calculation function "ExecuteSQL", which allows you to run SELECT statements on table occurrences in FileMaker; that includes ODBC sources. It also has a script step called "Execute SQL", which is handy for running arbitrary SQL against an ODBC data source. This latter is probably going to be very useful for you.

  • It's somewhat hard to get the results of SQL queries onto FileMaker layouts in any kind of elegant way. Generally, you need to write the results to a global field, a global variable, or a regular field. If you want to display tabular data from an SQL query in a decent kind of way, you will need to generate HTML and spit it into data url in a Web Viewer element on a layout (i.e., prefix the HTML with "data:text/html,")

Upvotes: 2

user4166144
user4166144

Reputation: 251

FileMaker, since version 9, includes the ability to connect to a number of SQL databases without resorting to using SQL, including MySQL, SQL Server, and Oracle. This requires installation of the SQL database ODBC driver to connect to a SQL database. SQL databases can be used as data sources in FileMaker’s relationship graph, thus allowing the developer to create new layouts based on the SQL database; create, edit, and delete SQL records via FileMaker layouts and functions; and reference SQL fields in FileMaker calculations and script steps. It is a cross platform relational database application.

Versions from FileMaker Pro 5.5 onwards also have an ODBC interface.

FileMaker 12 introduced a new function, ExecuteSQL, which allows the user to perform an SQL query against the FileMaker database to retrieve data, but not for modification or deletion, or schema changes.

Upvotes: 1

Related Questions