76366

SQL and Filemaker Pro 13 working together

Question:

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

Answer1:

To expand on <a href="https://stackoverflow.com/users/4166144/user4166144" rel="nofollow">user4166144</a>'s answer a bit, you can add MS SQL as an <a href="http://www.filemaker.com/support/technologies/odbc.html" rel="nofollow">external data source</a> 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:

<ul><li>

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

</li> <li>

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

</li> <li>

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.

</li> <li>

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,")

</li> </ul>

Answer2:

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.

Recommend

  • TypeScript output still uses Array.prototype.reduce even though I target ES3
  • How can i tell SubSonic 2 to use a different .config file?
  • windows store certification kit - c++ library validation issues
  • Generic DB Table
  • Populate ArrayList from Stored Procedure result set
  • How to vertically and horizontally center a div of unknown height
  • adding item to window Extjs 4
  • PHP PDF generation problem
  • How to move comma seperated ids from one row to another row?
  • Migrating row from one table to another with conditions [MYSQL]
  • SQL Server Like Query not case sensitive
  • Sql indexes vs full table scan
  • mysql table locked after php crashes
  • Sort by a column in a union query in SqlAlchemy SQLite
  • css calendar - td background diagonal split - two colors
  • SSIS Designer is running VERY slowly
  • How to add the custom button on google's(device) native application in android?
  • XNU incudes in Kext
  • “mvn clean generate-source” could not resolve dependencies
  • Compare struct to a constant in C
  • Django Haystack Rebuild Index
  • Pycharm: Marking a folder as 'sources root' is not recursive for subfolders
  • Azure table store snapshot/backup capability
  • Bigquery event streaming and table creation
  • Installed module is empty
  • Replace value with Factor in r data.table
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Linq Objects Group By & Sum
  • Getting last autonumber in access
  • How to extract text from Word files using C#?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • Large data - storage and query
  • Delete MySQLi record without showing the id in the URL
  • GridView Sorting works once only
  • KeystoneJS: Relationships in Admin UI not updating
  • Hits per day in Google Big Query
  • Checking variable from a different class in C#
  • How to get NHibernate ISession to cache entity not retrieved by primary key