Filemaker as a front end for MSSQL2005

[Dali]

Hi, we curently have a database solution consisting of two files (GUI.fp7 + DATA.fp7) i.e. data separation model is in place. Our goal is to move all the tables from filemaker file DATA.fp7 into SQL2005 and still use GUI.fp7 as the front end for the SQL server. Exporting data to SQL and connecting filemaker to SQL is not a problem. The major problem we're having is that filemaker does not preserve field order when an SQL table is referenced. For example, GUI.fp7 file has a Contacts table that is referencing Contacts table in the DATA.fp7 file, the idea now is to change this table's reference to a Contacts table in the SQL server with same field names and field order. However when filemaker makes the reference it changes the field order and now all the relationships, layouts and scripts associated with the Contacts table are wrong. For example, FirstName becomes Address, Phones becomes Email, etc...

Our application has a little over 400 relationships, 400 layouts and 1,300 scripts. I'd hate to go over each one of these to map the fields correctly.

Any ideas?

Thanks!

[Colin Keefe]

I guess my first question is...you know ESS has some limitations, right? Mapping's a problem obviously, but there are some structural ones as well you'll need to deal with...including the fact that we can't access any indexes in SQL tables. How are you going to work around the fact that you can't have table-based value lists, especially with 400 relationships and keys to maintain?

[Dali]

Yes, I'm aware of the limitations, there will be some headache when it comes to calculations, repeating fields and date/time fields since MSSQL2005 uses timestamps only, but I did some testing and was able to solve these. I still haven't figured out what to do with the value lists, perhaps I'll import value list data from SQL into a temporary FM table as needed. Another one is the Container fields, for now I'm thinking I'll be just storing a file path in the SQL and export all container fields from filemaker to a folder. Also, I need to mention that our solution is accessed via Terminal Server and is partly integrated with .NET like FileOpen, FileSave dialogs, sending bulk email, etc...filemaker communicates with .NET apps via a plugin we developed and via xml for data transmition. If something can't be solved by filemaker we just build a small .NET windows or console application to handle it.

Any comments on this or other limitations I should worry about?

Thanks for the reply,

Dali

Edited January 13, 2008 by Guest

[Colin Keefe]

No other comments, really, except that according to the tech brief for ESS, FMI didn't really build this feature as a front end so much as an integration tool:

ESS Design Goals: What It Isn’t

The emphasis with ESS should be on integration. The ESS feature set is not intended

to allow FileMaker Pro to act as a “front end” to SQL data sources. In particular:

• ESS does not allow a FileMaker Pro developer to compose their own SQL

queries and pass them to the server: ESS creates all SQL queries behind the

scenes. (Some such functionality is possible with ESS, though, by defining

views within the SQL data source. FileMaker Pro’s Execute SQL script step, by

contrast, does allow a developer to create and send customized SQL

queries.).

• ESS does not allow a FileMaker Pro developer to alter or extend the schema

of an SQL data source on the server side (though local extensions are

possible with the ability to add supplemental fields to an ESS table within

FileMaker Pro).

• ESS is not designed as a means to allow a FileMaker Pro solution to scale

beyond the limits of a purely FileMaker Pro based solution. The ESS feature

set is designed to emphasize the seamless integration of SQL-based tables

into a FileMaker Pro solution, rather than to take specific advantage of the

high-scalability features of SQL back ends.

Tech Brief URL

You've probably thought through all of the above issues already. The third point is pretty obvious, since pulling the data through FMS naturally imposes a simultaneous connection limit...but I do wonder if there are performance issues throttlingall your data through FMS from your SQL source versus hosting natively in FileMaker.

Nobody's stopping anyone from using ESS as a full-on front end, but since FileMaker makes a point of steering us away from doing so, I'm guessing there are reasons why. I'd be curious to hear some of them, actually, because this would be a very interesting route to take with a project.