5 Replies Latest reply on Jan 26, 2015 4:00 PM by Polarpro

    ODBC / ESS: Layout based on external SQL source




      Our FileMaker solution accesses data from two different MS SQL databases (the external databases are on the same server). For a few reasons in this case I would like to base a layout on one of the external tables. My question: Is this advisable? FileMaker allows me to do this, but I wonder if there are any caveats.




        • 1. Re: ODBC / ESS: Layout based on external SQL source
          Mike Duncan

          Yes, there are considerations to be made when using ESS. I would review the documentation and the chapters in the training series regarding ESS. For example, value lists based on field indexes aren't available with ESS fields.


          That isn't to say you shouldn't use ESS to base a layout on, that is perfectly fine and can depend more on your solution needs.

          • 2. Re: ODBC / ESS: Layout based on external SQL source

            Agreed with Mike: the "Integration" chapter in the Advanced FTS book does a very good job of listing all the caveats.


            Database Skills, FileMaker Pro Training | FileMaker

            • 3. Re: ODBC / ESS: Layout based on external SQL source

              You need to look at the details like Mike and Wim suggested.  ESS is a way to extend FileMaker to see external data sources and is pretty slick.  However, it is not meant to replace local tables.  In other words, you will have to do more work on performance optimization with external tables because they will not perform as fast as local ones.  Using ESS tables from a database on the same server or in the same local area network generally works pretty well.  It becomes a lot more of a challenge if you have to access an external database over the wide area network (internet) because bandwidth and latency kill performance. 


              Make sure there is a specific reason to host those tables outside of FileMaker.  Usually this happens because the data already exists on another server.  But if the data does not already exist there, it is  better for FileMaker solution performance if the data is hosted on FileMaker.


              Probably the most common reason I use ESS is a company has a production server FileMaker, but their web development team does not know FileMaker and wants to develop a cart or client input web pages with MySQL as the database.  While I would prefer them using FileMaker, this is often a compromise because FileMaker can play well with MySQL via ESS. 

              • 4. Re: ODBC / ESS: Layout based on external SQL source

                I currently do this with several databases. One big drawback is managing more than a couple of hundred recordsets. ODBC driver is extremely slow when interacting with MySQL databases. But doing simple finds, managing data in "form view" you'll be fine. Handling a lot of data will make it CHOKE. If you absolutely need to run reports or pull large lists, you should consider some type of syncing solution where you mirror the data into local tables.

                • 5. Re: ODBC / ESS: Layout based on external SQL source



                  And Thanks for your very helpful input. (Reading FTS again helped a lot, too ).


                  I will in this case definitely not use the external SQL table as base for my layout; I easily understand that external tables do not perform as fast as local ones. The reason why I thought of using an external SQL table as base for a layout was that I wanted to avoid a longer row of buoys, but in this case it is just 1 (FileMaker) anchor that leads to an (SQL) buoy that would lead to another (SQL) buoy.


                  Thanks again!