7 Replies Latest reply on May 12, 2016 6:52 AM by TSPigeon

    FileMaker relationship between local and remote (ODBC) tables not working

    simon.mara@me.com

      Hi all,

       

      For context, I run a news website and our FileMaker CRM solution tracks eNewsletter subscribers, and registered website users.

       

      I have recently connected our Filemaker 14 database (Hosted on FileMake Server 14 on MacOS X Server 10.11) to the back end of our website using the Actual Tech ODBC driver.

       

      I’ve successfully added various ODBC tables to the relationship diagram, and established relationships between some of the local FileMaker tables, and some of the remote ODBC tables.

       

      eg

       

      eNewsletter subscribers (FileMaker)                                    website users (ODBC)

      email address (primary key)                       <——————>    email address (primary key)

       

      This works fine, and from an eNewsletter subscriber layout it’s now easy for me to see if the person also has a website subscription by interrogating the remote database in real time.

       

      Before I ask my question, a little more background is needed….

       

      One of the ways we group eNewsletter subscribers into organisations is via a simple calculation field that extracts the domain name (eg apple.com) from the eNewsletter subscriber's email address, and links this to a FileMaker table called Organisations. Each record in the Organisation table has its domain name stored as static data.

       

      eNewsletter subscribers (FileMaker)                                                       Organisations (FileMaker)

      email address (primary key)                                                                         Name

      domain (calculation on email address — foreign key)          <————>     domain (static data, primary key)

       

      This works fine and allows us to see all eNewsletter subscribers associated with an organisation, without us having to manually match individuals with organisations. So a calculation field *can* be used as a key so long as both tables are FileMaker tables.

       

      HOWEVER….

       

      From our organisation layout (FileMaker table), we also wish to display data from the website users table (ODBC).

       

      website users (MySQL)                                                                                           Organisations (FileMaker)

      email address (primary key)                                                                                      Name

      domain (FileMaker calculation shadow field — foreign key)     <————>              domain (static data, primary key)

       

      This does not work!

       

      ie the website users from eg apple.com are not shown in our organisation layout for Apple / apple.com

       

      So my conclusion is FileMaker can’t relate a local table to a remote MySQL table if the key used in the MySQL table is a Filemaker-added (shadow) calculation field.

       

      I can’t think of a technical reason why this doesn’t / shouldn’t work? Can’t FileMaker perform the calculation on the remote data, then make the link, just as it does when all data is local?

       

      My work around is to join the Website users MySQL table to the Organisations FileMaker table using a cartesian join (X), which matches every Website user to every organisation. I then filter a portal on the organisations layout to display only the relevant handful of contact that apply to this organisation.

       

      For many reasons this isn’t a good solution so I’m hoping someone can help me out?

       

      Thanks for taking the time to review this information and I look forward to your response!

       

      Kind regards,

      Simon