7 Replies Latest reply on May 20, 2016 1:00 PM by LSNOVER

    Quick setup for FileMaker 15 & Postgres on a local Mac

    OllyGroves

      Quick setup guide for FileMaker 15 & Postgres on a local Mac

       

      • This is a rough quick start guide to help others setup a local development environment with Postgres (via Postgres App) and FileMaker 15 desktop using the new Actual ESS Adapters for FileMaker 15
      • I'm guessing will be fairly similar process for Windows but I'm not over that side of the fence much.

      Download & Install Required apps

       

      Download & Install 'Postgres App' for Mac (Free)

      • For ease of use I'm using the 'Postgres App' rather than full Postgres Server installer.  The 'Postgres App' runs locally as a Mac App (wrapping a self contained Postgres Server) with a Menu Bar icon for quick access.

      • Download here - http://postgresapp.com/

       

      Download the Actual Technology 'ODBC drivers' & new 'ESS Adapter'

      • Actual ODBC Drivers Pack (has trial mode, to buy its $39.95 for local machine or $299 for FileMaker server)
      • Actual ESS Adapter (Free on Mac, $39.99 on Win)
      • Download here: http://www.actualtech.com/download.php
      • If on Windows, believe you use the official Postgres ODBC driver for Windows (which is Free) & licence the 'Actual Technologies ESS Adapter' instead ($39.99)
      • Probably best to install these drivers after you've setup your Postgres database(s) - see below.

       

      Download & Install a Postgres GUI client (to create and manage your Postgres databases)

      • GUI clients make managing Postgres a little easier, however can use Postgres 'psql' Terminal Console to manage if so inclined. (quick access to that via 'Postgres App' Menu Bar Icon)

      • Postico: https://eggerapps.at/postico/

        • native mac app, same guy who maintains 'Postgres App' above, integrates nicely with it out of the box.
        • Simple UI with useful features like copy & paste from excel/csv into Postgres table 'content'
        • $39.99 but fairly open ended trial with a few limitations (you indirectly support his 'Postgres App' project if do buy it).
      • Some Free Postgres GUI clients

       

      Connect your Postgres GUI client to 'Postgres App'

      • 'Postgres App' connection settings are: -

        • Host: localhost
        • Port: 5432 (default)
        • User: (Your OSX Username)
        • Password: blank
        • Database: (Your OSX Username)

        • If your GUI client requires a URL, use: postgresql://YOURUSERNAME@localhost/YOURUSERNAME

      • Tip: with 'Postgres App', you use your OSX username to connect to databases. Will need it when connecting via a Postgres GUI app & when adding ESS source to your FileMaker database.

       

      Add Postgres Database(s) and Tables, Columns & Content to local Postgres Server.

      • Fairly easy once you've connected your GUI client to local 'Postgres App' server. If not worked with SQL database before would recommend using 'Postico' GUI client as I found it the least difficult to get rolling with.
      • See your chosen GUI client / Postgres help for further support using Postgres databases.

       

      Install the Actual Technology ODBC drivers & ESS Adapter on your Mac

      • Note: Best to setup a test Postgres database above before doing this bit, as need to point the ODBC System DNS at it.
      • Install the Actual Tech ODBC Pack Drivers first
      • *Setup up 'System DNS' datasource for Postgres via 'ODBC Manager' app in Mac Utilities.
      • Install the Actual ESS Adapter and point it at the previously created ODBC DNS

      • *the Actual Technology quick start instructions help here.

       

      Add your Postgres database to your FileMaker database with ESS

      • Open the FileMaker database you wish to connect to the Postgres Database
      • Go to File > Manage > External Data Sources
      • Click New, Name it, click the 'ODBC' radio button.
      • 'Specify DNS', should see & choose the 'Actual ESS Adapter' data source you created above when installing (if not create in 'ODBC manager')
      • In File > Manage > External Data Sources 'Specify Username and password': add your username (OSX Username if using 'Postgres App'), you won't need a password unless you've set it up in the 'Postgres App'. [Tip: this stops FileMaker prompting you for a Postgres password each time you open you FileMaker file]
      • Go to FM relationships graph, add a TO, choose a table from your recently added ESS data source
      • That should be it (ie. you should now be able to read, write, add, delete data from your Postgres database tables within FileMaker)
      • Tip: Use 'Refresh Window' in FileMaker to update changes if you add data via your Postgres GUI client.

       

      Please comment below if any errors, additional Windows information, etc.  There maybe a few as I quickly threw together while setting up today.

       

      Thanks

       

      Olly

        • 1. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
          beverly

          This is awesome, Olly! thank you for sharing. I'm going to link this thread with this one (and back):

          beverly

          • 2. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
            BeatriceBeaubien

            Thanks very much for this, Olly.

             

            Here is another installment in the adventures with Actual's product. I hope this complements your useful info.

            http://fmprodb.com/450/fms-15-ess-aws-redshift.html

             

            A major deal for me is Matt's observation it works in version 13 and 14.

             

            Best wishes,

             

            Beatrice

             

            On May 15, 2016, at 10:48, OllyGroves <noreply@filemaker.com> wrote

             

            Quick setup for FileMaker 15 & Postgres on a local Mac

            created by OllyGroves in Discussions - View the full discussion

             

            Quick setup guide for FileMaker 15 & Postgres on a local Mac

             

             

            /snip

            1 of 1 people found this helpful
            • 3. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
              LSNOVER

              Beatrice:

               

              Test carefully, there may be some gotchas using the old versions.  In theory it may work, because my understanding is the native connection being used inside of Filemaker is the MySQL connection.  Actual has basically made a driver that connects to Postgres and DB2 and does the work of translating everything to make it look like a MySQL db to Filemaker.

               

              While I can't verify it, FM has in the past made some specific tweaks for interacting with Actual's drivers.  Things to be careful about are dates and times, unique record indexes.  It may be worth a quick email to Actual to see if there are any issues, as they would know.  If it does work, that is indeed a bonus!

               

               

              Now lets let those of us who are ESS fans PLEA with Filemaker to deal with the serious ESS performance issues in the core of Filmaker, which have gone unaddressed since ESS was released.  If they do not hear from those of us using the feature, it will not get much attention!  I'm a BIG SQL database fan, and I really love using SQL dbs with Filemaker, but the feature is only 70% done in my opinion.

              2 of 2 people found this helpful
              • 4. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
                OllyGroves

                Thanks Beatrice, thats an interesting blog.. and nice instructions for ESS Adapter on Windows too.

                 

                I only have a passing awareness of Amazon Redshift.  A quick dig suggests its was forked off 'Paraccel', which was itself a fork of Postgres 8.2 in 2007.

                 

                So not officially supported but guess core Postgres functions (pre 8.2) should work but specialist functions of Amazon Redshift may not.

                • 5. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
                  IanJempson

                  Thanks for sharing that Olly! excellent stuff...

                  • 6. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
                    BeatriceBeaubien

                    Hi Lee,

                     

                    Yes. Thanks for the qualification. As Jonathan mentioned in a comment to Matt's blog posting, "The adapter won’t stop you from using these untested versions, but it would be worth your while to verify that the features you need are working with your configuration."

                     

                    The bottom line for me is that Actual wouldn't be expected to test legacy versions of FMP (never mind Postgres), so it's up to the developer to confirm the new capabilities work for what their application requires, with the combination of versions of FMP and Postgres used in their production environment. Still, it holds some promise.

                     

                    And thanks for reminding us that ESS performance needs attention from FMI. Perhaps this is a first step.

                     

                    All the best,

                     

                    Beatrice

                    1 of 1 people found this helpful
                    • 7. Re: Quick setup for FileMaker 15 & Postgres on a local Mac
                      LSNOVER

                      They might!  It's more avenues for business for them, and many folks may be reluctant to jump right into FM15, especially with the new licensing rules.

                       

                      We can use at our own risk I suppose.  ;-)

                       

                      Definitely hoping these additions will precede some major enhancements to the ESS core.  But I'm sure it will not happen without some polite pressure.