Skip links

FileMaker and MySQL using the example of xt:Commerce.

FileMaker training, courses, coaching

FileMaker and MySQL using the example of xt:CommerceDue to the many automation options and the high volume of data, FileMaker is an ideal solution for optimizing operational processes, especially for operators of online stores. FileMaker solutions can be quickly and easily adapted to your own needs.

For several generations now, FileMaker has been able to directly access external SQL-data sources such as a MySQLDatabase of an xt:Commerce-based online store and to read and write data there. In this article I describe how you can use FileMaker Pro under Mac OS X or Windows to access a MySQL database of xt:Commerce directly, how you can import data from xt:Commerce into FileMaker and how you can transfer status messages from FileMaker to xt:Commerce.

What is required to access the SQL database?

An external SQL data source is accessed via the ODBC-Interface. In order to set up an ODBC data source, an ODBC manager for Mac OS X is required. This can be downloaded from www.odbcmanager.net can be downloaded free of charge as freeware. The ODBC Administrator from Mac OS X can also be used on the Mac, and the ODBC Manager included in the system can be used under Windows.

In addition, you need an ODBC connector for Mac OS X to access the MySQL database of xt:Commerce. Website of MySQL can be downloaded. For a stable data connection to the xt:Commerce database, you need the ODBC connector in the older version 3.51.27. The DMG version for Mac OS X 10.5 (x86, 32-bit) also works under Mac OS X 10.6 Snow Leopard without any problems.

Installing the MySQL ODBC Connector

Before the data source can be set up in the ODBC Manager, the ODBC Connector for MySQL must first be installed. After the program has been downloaded, the MySQL ODBC Connector installation wizard starts automatically:

Installation of the ODBC Connector for Mac OS X.

After you have continued and accepted the license conditions, you can select the target volume for the installation and install the ODBC connector on your system.

ODBC Connector installation: Selection of the target volume

During the installation process, the ODBC connector is automatically installed in your system so that the driver for this connector is available the next time you start the ODBC Manager.

Setting up the ODBC data source

After the SQL-Connector has been downloaded,

Program icon of the ODBC ManagerIn the following I will describe how to set up the ODBC Connector with the ODBC Manager for Mac OS X - further up in the text there is a link to download this program. In Windows or under Mac OS X with other ODBC managers, the settings must be made in the same way.

After the ODBC Manager has been downloaded and copied to the Mac OS X program directory, start the ODBC Manager by double-clicking on the program icon. The program then starts and opens the main window of the ODBC Manager. ODBC data sources can always be set up system-wide (system DSN), i.e. for all users on the system or for one user (user DSN). The use of the ODBC data source can be recorded in a text log in the [Tracing] area.

ODBC Manager main window

Depending on whether you want to set up the data source for the active user or system-wide, click on the [Add...] button in the corresponding area.

ODBC Manager: Adding a data source

For xt:Commerce, select version 3.51 of the MySQL ODBC driver at this point and click on the [OK] button. In the following window, enter all the data from your MySQL database that contains the data tables for your xt:Commerce online store.

Configuration of the ODBC data source

Once you have entered all the data in the corresponding fields, click on the [Test] button to establish a connection via your newly created data source and test it.

ODBC data source successfully tested.If the ODBC data source test was passed successfully, a corresponding alert box appears and the data source can be set up by clicking on the [OK] button. It is now available for the active user or system-wide as an ODBC source for all programs that support ODBC connections, i.e. also in FileMaker, which we will try out next. Once the new ODBC data source has been set up, the ODBC Manager can now be closed.

Using the ODBC data source with FileMaker

Start FileMaker and open a database with which you want to establish a connection to your xt:Commerce database. If you already have a FileMaker solution for your store orders, it is best to open the version that you want to develop further.

Reading data from the xt:Commerce database

To import data from xt:Commerce into FileMaker via the ODBC interface, all you need is the familiar "Import records" command in Scriptmaker. For the data from xt:Commerce, I recommend that you create an image of the original Table from xt:Commerce in your solution. This makes the import easier due to the same field names, and your solution has its own table with all orders from xt:Commerce.

Create an image of a table from xt:Commerce

To create an image of a table from xt:Commerce in FileMaker, it is best to import the table with field names into your FileMaker solution. In the menu bar of the FileMaker file into which you want to import the table, select the item

File > Import data records > ODBC data source

from. Now select the ODBC data source of your SQL server set up in the ODBC Manager with the databases from xt:Commerce and enter your user name and password of the MySQL database in the following dialog. The Specify ODBC SQL query dialog now appears, in which you can enter an SQL command either from a calculation or as static text.

FileMaker: Specify ODBC SQL query

To import an entire table into FileMaker, simply click on the [Query creation...] button, whereupon a wizard for creating the SQL query opens.

Wizard for creating an SQL query

In the left-hand column you will find all tables that are located on the remote SQL server.Server are located. For example, to import the "orders" table from xt:Commerce, select the corresponding "orders" table in the left-hand column. The available fields of the table selected on the left now appear in the right-hand column. To create the query automatically, simply double-click each field to be imported in the right-hand column. In the lower window you can see how the query is automatically created field by field. At the end, the window should look like this:

SQL query of the 'orders' table from xt:Commerce

Now select the entire text (cmd-A) in the "SQL query" field, paste it into a document in a text editor (e.g. Textedit) and save the text, e.g. as "SQL_xtC_orders.txt".

After clicking on the [OK] button, the dialog for assigning the fields appears. In this dialog, select [New table: "Name of data source"] under "Target". After clicking on the [Import] button, all fields selected in the query are created in a new table and the associated data is imported. After the import, you will find the table in the database administration and can rename it as you wish, e.g. to "xtC_orders".

Create script for data import from xt:Commerce

To integrate a data import into your solution, open Scriptmaker and create a new Script. Add the "Import records" command to your new script. Now select "ODBC data source" as the data source and select the ODBC data source created in the ODBC Manager. Now enter your login data for the SQL database and activate the "Save user name and password" checkbox. After clicking on the [OK] button, you can now insert the SQL query again as a calculated formula or as text. If you only want to import a specific order with your script in the future, you must create a calculated SQL query. In our example, we assume that the order number to be imported in the Variable $orders_id is saved.

Now open your saved text document with the SQL query. Add quotation marks (") at the beginning and the following command at the end of the query:

WHERE `orders`.`orders_id` = " & $orders_id

This command restricts the import to the data record whose field contains the value of the $orders_id variable. Your entire SQL statement should now look something like this:

"SELECT `orders`.`account_type`, `orders`.`afterbuy_id`, `orders`.`afterbuy_success`,
`orders`.`billing_address_format_id`, `orders`.`billing_city`, `orders`.`billing_company`,
`orders`.`billing_country`, `orders`.`billing_country_iso_code_2`,
`orders`.`billing_firstname`, `orders`.`billing_lastname`, `orders`.`billing_name`,
`orders`.`billing_postcode`, `orders`.`billing_state`, `orders`.`billing_street_address`,
`orders`.`billing_suburb`, `orders`.`cc_cvv`, `orders`.`cc_expires`, `orders`.`cc_issue`,
`orders`.`cc_number`, `orders`.`cc_owner`, `orders`.`cc_start`, `orders`.`cc_type`,
`orders`.`comments`, `orders`.`conversion_type`, `orders`.`currency`,
`orders`.`currency_value`, `orders`.`customers_address_format_id`, `orders`.`customers_cid`,
`orders`.`customers_city`, `orders`.`customers_company`, `orders`.`customers_country`,
`orders`.`customers_email_address`, `orders`.`customers_firstname`, `orders`.`customers_id`,
`orders`.`customers_ip`, `orders`.`customers_lastname`, `orders`.`customers_name`,
`orders`.`customers_postcode`, `orders`.`customers_state`, `orders`.`customers_status`,
`orders`.`customers_status_discount`, `orders`.`customers_status_image`,
`orders`.`customers_status_name`, `orders`.`customers_street_address`,
`orders`.`customers_suburb`, `orders`.`customers_telephone`, `orders`.`customers_vat_id`,
`orders`.`date_purchased`, `orders`.`delivery_address_format_id`, `orders`.`delivery_city`,
`orders`.`delivery_company`, `orders`.`delivery_country`,
`orders`.`delivery_country_iso_code_2`, `orders`.`delivery_firstname`,
`orders`.`delivery_lastname`, `orders`.`delivery_name`, `orders`.`delivery_postcode`,
`orders`.`delivery_state`, `orders`.`delivery_street_address`, `orders`.`delivery_suburb`,
`orders`.`hanseaticbank_reference`, `orders`.`language`, `orders`.`last_modified`,
`orders`.`orders_date_finished`, `orders`.`orders_id`, `orders`.`orders_ident_key`,
`orders`.`orders_status`, `orders`.`payment_class`, `orders`.`payment_method`,
`orders`.`refferers_id`, `orders`.`shipping_class`, `orders`.`shipping_method`
FROM `orders` WHERE `orders`.`orders_id` = " & $orders_id

For example, you can now have your script request the desired order number from the user and automatically import the corresponding order into your FileMaker solution via script.

Writing data to an SQL data table

FileMaker can also be used to write or update data directly into a table from xt:Commerce via ODBC. Scriptmaker has the following command for this purpose:

Execute SQL Query [...]

In the following example, we create a new data record in the xt:Commerce table orders_status_history. The status messages from xt:Commerce are stored in this table, which the customer can usually also view in the login area of the store. A new data record in this table is created with the following command:

Execute SQL Query [
"INSERT INTO `orders_status_history` (`comments`, `customer_notified`, `date_added`,
`orders_id`, `orders_status_history_id`, `orders_status_id`)
VALUES ('" &
"Status text'" & ", 0, '" & xtc_date(Get(SystemDate)) &
"', " & xtcommerce::_Order_No & ", " & "'''" & ", " & "2)"]

This command can either be used directly in a script, or it can be used using a new custom function (Custom Function) can be encapsulated. The xtc_date is a Own functionwhich you can either define as a separate function or resolve directly in the script command. The separate xtc_date function converts a transferred date into the format in which xt:Commerce expects a date:

xtc_date (date field) =
ReadAsText(YearNumber ( date field ) & "-" &
CharacterRight( "0" & MonthNumber (date field); 2) & "-" &
CharacterRight( "0" & CalendarDayNumber (date field); 2)) & " " &
ReadAsText(Get(SystemTime))

To change the status itself, the corresponding status ID is set in the MySQL table <>. In the following example, the status ID of the order is set to ID "2":

Execute SQL query ["UPDATE `orders` SET `orders_status` = 2
WHERE `orders_id` = " & xtcommerce::_Order_No]

You can view and edit the status IDs of the statuses you have assigned in xt:Commerce in the backend of xt:Commerce.

The possibilities for integrating data from xt:commerce with FileMaker are practically limitless. Like the order data, article or address data from xt:commerce can of course also be used in FileMaker and vice versa.

Of course, the procedure described in this article works with any table from xt:Commerce and, in principle, with any other online store whose database allows access via ODBC connector. For detailed information regarding the database structure of your store system, I recommend studying the relevant technical documentation.

Leave a comment

Share this page:

ERP software as flexible as your company.
We will be happy to advise you.

Customizable ERP software for Mac, Windows and iOS.

You are here: Connecting an SQL server to a FileMaker database. Step-by-step instructions for an xt:commerce online store | goFileMaker