Connecting Filemaker Pro to a Joomla website for seamless integration

If you, like many small retail businesses, use Filemaker Pro to manage your stock this is a solution that will allow you to synchronise data in real time between your Filemaker stock database and an ecommerce website. The key benefit is any changes that you make either online or to the Filemaker database will be instantly updated in both databases. This means that if someone orders from your website your stock levels will be automatically adjusted in Filemaker – so you’ll only sell what you have.

Please note that this is a pretty sophisticated solution and you'll need a decent understanding of how a Content Managed Website works to be able to set this up.

Whilst this solution isn’t limited to Joomla (it’ll work with any MySQL database) I created this solution using Joomla so have used this as an example. The tools I used are as follows:

• Joomla! Version 1.5.23
• Virtuemart (the Joomla ecommerce plug-in) version 1.1.8
• Filemaker Pro 11
• MySQL ODBC connector 3.5.1

All of these tools (except for Filemaker) are open source and so free of charge. This solution assumes you already have a Joomla CMS website with Virtuemart set up. If not take a look at our Cambridge web design website for information on how we can help you.

Here’s how to make the connection:

Initially you need to allow external sources to have access to your Joomla MySQL database. This is key as otherwise the connection simply won’t connect. To do this go to your Cpanel web host dashboard and select ‘Remote MySQL’. Now either enter in the relevant domain or IP address where the Filemaker database is. Please note you can only specify the domain or IP address if you have a fixed internet connection like a leased line. For most people with a dynamic IP address (ADSL, Cable Modems, etc) enter a % which is a wild card and will allow anyone with the correct password, user name and database name to connect. This is still extremely secure if you use strong passwords.

Next you need to download the MySQL ODBC connector. It’s very important that you ONLY use the MySQL ODBC version 3.51 and the 32 bit version. No other connector will work with Filemaker. You can find the ODBC connector online by searching Google. Install the connector.

Now you need to set up the database connection to connect to your Joomla MySQL database. From Windows Control Panel > Administrative Tools > Data Sources click on System DSN (it must be the System DSN – User DSN won’t work) and click add.

In the list of connections you’ll find the MySQL ODBC connector. Open the connector.

You’ll see a series of boxes to add your connection details to. Add a name (it can be anything you like), the domain you’re connecting to (for example http://www.swaysearch.com), the database name, the database user name and the database password. Please note that you should have all this information from when you installed Joomla. If you don’t it’s in your website control panel.

Click on ‘Test Connection’. If it doesn’t work you’ve done something wrong so check the steps above for errors. Let’s assume it’s connected up.

The rest of this guide takes place in Filemaker Pro. Click on File > Sharing and click the ODBC/JDBC radio button. Select all users (you can limit this if you have a lot of Filemaker users). This turns on the ODBC connection.

The next step if to define the data source. Go to File > Manage External Sources > New > and type in a name for the connection.

Select the ODBC radio button and specify the data source. This will show your MySQL ODBC connection. Type in the authentication details – your user name and password – and click OK.

If you’ve followed the steps you’ll now have a live connection between Filemaker and Joomla but we’re not finished yet. Although you have a connection it’s of no use unless you specify which fields you want to use.

To do this click File > Manage > Database > Relationships

In the box will be any existing tables that you have in Filemaker. You need to click ‘Add Table’ and from the ‘Data source’ drop down select your MySQL connection. Now all of the tables in the Joomla MySQL database will be displayed. You can now select the appropriate Joomla tables (I used the ‘jos_vm_product’ and the ‘jos_vm_product_price’ tables as I wanted to connect an online Virtuemart shop to the Filemaker database. You can of course use any Joomla fields you need).

The graphical relationship viewer in Filemaker will show the new Joomla tables side by side with the existing Filemaker tables. You need to make a single connection from a field that contains unique data (such as a Product ID or an email address) and connect the field in Filemaker with the field in Joomla. You might have to create a new field in Filemaker specifically for this purpose.

When you go into View > Layout you can now add in new Joomla fields as you would normally and simply select the Joomla table rather than an existing Filemaker table.

If you got it all working correctly you’ll now have instantaneous synchronisation between Joomla and Filemaker. When you update either system it’ll be represented in the other in real time. Pretty cool for open source software!