This guide will walk you through the steps required to sync a Fulcrum app with your own MySQL database. Having your up-to-date Fulcrum data available in your own database enables countless opportunities for custom integrations and seamless workflows. Databases power most CRM, GIS, and Asset Management systems, so configuring Fulcrum to write directly to these database tables allows you to leverage all the benefits of Fulcrum for field data collection without having to modify your existing systems or workflows.
This guide assumes you have a basic understanding of PHP scripting. You will also need to have a web server with MySQL installed and PHP configured with PDO and allow_url_fopen enabled. Be sure your Fulcrum plan has access to webhooks (available through the developer package) and Data Shares (available on the professional plan) and you have user privileges to administer webhooks.
Simplifying Things with Data Shares
In order to provide an incredibly flexible form schema, while maintaining data integrity, the Fulcrum API has a certain level of complexity. Working with the API can seem daunting, especially for beginning developers. For example, the Fulcrum JSON format for records stores custom form values (user defined fields) as key/value pairs. In order to greatly simplify things, we will use Data Shares, which provide user-friendly data formats, such as CSV and GeoJSON.
Webhook scripts are invoked any time an event happens in your Fulcrum organization. Our PHP script will listen for record events (
record.delete) specific to our app. Any time a record in our app is created, updated, or deleted, our script will fetch that record in GeoJSON format from our data share link. It will parse the GeoJSON feature properties (fields and values) and build a corresponding SQL prepared statement. Finally, the script will execute the prepared statement, either creating a new record in our table, updating or deleting an existing record (based on the
The general steps involved are:
Create your custom data collection form and ensure there is at least one record.
Enable data shares and download the CSV file.
Import the CSV file into your database to create the table.
Download the standard PHP webhook script and update with your specific details.
Upload the PHP script to your web server and configure a Fulcrum webhook to reference it.
Test it out!
Create Your Form
Create a new form or update a current form, adding as many custom fields as you like. Note that this implementation does not currently support repeatable fields, but they could be added if required. Be sure your app has at least one record so we can create the database table later on.
Enable Data Sharing
Data sharing must be explicitly enabled on your app. From the main Fulcrum homepage, click on the name of your app to open the App Dashboard. Next, click on the Data Share tab and then click the blue Enable Data Share button. Note that Data Shares are read-only URL’s for accessing data you have in your Fulcrum account without requiring authentication. These URLs have a unique, 16 character, hexadecimal token which is used to access the app’s data. Don’t share this URL with anyone you don’t want to have access to your data. Our PHP script will not expose this URL to the public.
Once data sharing has been enabled, click on the CSV tab and copy the URL to the CSV share. Paste the link into a browser tab and hit enter to download the CSV file.
Import CSV File to Create Table
Now we need to create the table in our MySQL database. You could manually create the table, but it’s probably quicker to automatically create the table from the CSV file. If you have phpMyAdmin installed on your server, log in to your database and click on the Import tab. Browse out to the CSV file you just downloaded and be sure to check the box that says The first line of the file contains the table column names.
Click the Go button to create the table and import your records. You may want to rename the table and edit the structure to modify the field types. In my experience, many of the columns are created as
VARCHAR types with fixed lengths. If you find that your data is getting truncated, you may need to change the
VARCHAR length or switch to
Download and Modify the PHP Script
Download the fulcrum_mysql_webhook.php script to your computer. Open the file in your favorite text editor and update the following variables with your information:
$shareToken: This is the 16 character token that makes up part of the data share URL.
database connection info: This is the information required to connect to your MySQL database. Be sure that your PHP script can access your database if they are on different domains.
Upload Script to Web Server and Configure Webhook
After updating the PHP script with your information, upload it to your web server and note the URL to access it. Go into your Fulcrum Settings by clicking the red gear in the upper right-hand corner of the page. Click on the Webhooks tab, then click the Add Webhook button. Give your webhook a name, such as My App MySQL Sync and enter the URL to the PHP script you just uploaded. Be sure the Active? checkbox is checked and click the Save Webhook button.
Test It Out
You should now be able to add a new record, update or delete an existing record, and have the changes reflected in your MySQL table. Keep in mind that webhooks only send when the data is synced to the server, which is automatic for edits via the web, but may require manual syncing depending on your mobile device settings.
This guide walked you through the steps required to configure a fairly generic webhook script, which takes advantage of Data Shares to build SQL statements that update a database in real-time. While PHP and MySQL are nearly ubiquitous on most shared web hosts, you could use this guide as an outline for integrating with different databases or web services. The PHP PDO extension supports many databases in addition to MySQL, so this script could very easily be used to sync to SQLite, PostgreSQL, Oracle, or MSSQL as well.