I am able to reproduce this on multiple occasions now. I have a Filemaker 16 application that is primarily a front end of ESS tables. MS SQL. Introduction. The External SQL Source (ESS) of FileMaker Pro allows you to view and update data in an ODBC database without writing any SQL code. It was decided that the best approach was to move a few primary tables from FileMaker to mySQL using ESS. Of course, we had to maintain all.
|Published (Last):||17 October 2008|
|PDF File Size:||1.74 Mb|
|ePub File Size:||8.50 Mb|
|Price:||Free* [*Free Regsitration Required]|
It uses ODBC import. Have you never been bothered by inconsistent database structures between filemakeg data source and your own application? Have you never cursed the SQL developer who uses as a unicity key a concatenation of several columns? Have you never lost your temper in front of a progress bar during a sync operation on a ESS table? Note that it is not possible, at least to my esx, to read data from an external source without writing to a FileMaker table import.
Execute SQL can also modify the structure of a database. Come back to this tip after reading this article. Could give you ideas.
Enough to re-think fi,emaker stuff! So you will be able to edit your scripts and the server can perform them. Here is a short summary of the situation we had to address. We have to import a massive amount of data stored in mysql. Performance is a key here, and this excludes ESS immediately. By the way, we had really no reason to consider ESS: So we go for ODBC imports, but refinements are to come later.
As you can see, the Import records script steps is talking to a DSN with a query that we define as a variable for the sake of readability. The problem is that the unicity criteria does not fit in a single column.
To decide wether a record matches and must be updated, 3 columns had to be used as matching keys. FileMaker allows this, but with fulemaker poor performance. But although we want to import these 4 columns into 4 FileMaker fields: In our situation it would have been a reasonable option, but very often you have simply no control over the source structure.
Mac ODBC: Setting up a FileMaker ESS table
OK, we solved the performance issue, but while doing so we really took control over the left side of the import order dialog source. In the above esss, I simplified the column names in a, b, c, d and the field names in A, B, C, D, but as you probably expect the real world names were a bit more complex. Still we knew that the SQL developer might want to rename some columns, or even release new views to replace the old ones after some weeks this is a real case, not fantasy So we wanted to build a system that would resist a change on the source side.
Not that the change would be entirely automated, but we wanted to be able sss switch to a new source in minutes, without coding. We saw that the left side of the import order could be managed on the fly.
filemakrr It is therefore up to the right side the database structure to contain the information. Then we need to create a custom function. If for the above mentioned table we evaluate the following expression:.
Accessing the Data Warehouse with FileMaker – Overview – IS&T Contributions – Hermes
Here is the same image again: This will allow importing from joints in the future currently not supported by the function. As you can see, this techniques opens up a lot of possibilities. If you combine this with the fact that an import script step is able to create a esss table, that Execute SQL is able to delete this table DROPthat you can now duplicate a record set without being limited because a table cannot import to itself, etc, etc… the potential is huge!
Fabrice Nordmann – Categories: Have you never felt anxiety before renaming a field because it could break an import order?
FM16 and ESS Issue
This article is for the rest of us. The purpose is here to interact with external data as if they were FileMaker data, through table filemaler, layouts, scripts….
Note that on Mac, a third party driver is required Actual Technologies. And that is a very bad idea. I admit that the fact that you can interact with external data just as if they were FileMaker data is great, but the cost in terms of performance is huge.
Finally, and that is often forgotten -and therefore our today topic— the capability to communicate with a SQL source by script.
What script steps are we talking about? Two script steps allow interaction with ODBC sources: The plot Here is a short summary of the situation we had to address.
By selecting the Matching names options, we end up with this: Imagine that the original request would be: One fillemaker seemed nice for this: As you can see on the image, you can combine a source name and some other comments. If for the above mentioned table we evaluate the following expression: One can also write: This will allow importing from joints in the future currently not supported by the function Finally, this second parameter is optional, so you can inject SQL functions in the query: Third video about FileMaker and graphdatabases now available.