The Fulcrum platform has grown tremendously over the past few years, and while we continue to add new features and integrations, the primary task of Fulcrum remains- to enable users to quickly and easily build structured data collection solutions. Our goal is to remove the technical hurdles typically associated with developing a robust mobile data collection solution, so that anyone can collect accurate, timely data, which ultimately provides the foundation for sound decision making.
The Full Data Cycle
While we tend to focus primarily on the data collection challenges, we must not forget about the rest of the data cycle. Once collected, the data must be processed or analyzed, so that it can be turned into actionable information. The standard workflow is to collect your data in the field with Fulcrum, then export it out for analysis in your favorite database, spreadsheet, or GIS application. If you need real-time analysis, we suggest integrating with a service such as CARTO, via data shares or Fulcrum webhooks.
The Fulcrum Export Wizard allows you to export your data in a variety of standard, GIS-friendly formats. If you are working with a simple form and collecting the data all at once, the process is fairly straightforward. However, if you’ve got a complex form, with related tables (repeatables) and ongoing fieldwork, managing your data can become a daunting challenge. We often receive support inquiries regarding best practices for managing exported field data. While there is no silver bullet or standard solution, I’ve outlined a process below that I personally use to manage Fulcrum data for ongoing projects where I have to routinely fetch the latest data and present it in a standard way.
My first recommendation is to use the proper tool for the job. If you are working with tens of thousands of records with several related tables, you’ll want to resist the urge to use Excel to manage this data. Writing complex Excel macros, with logic hardcoded to specific fields can be extremely nerve wracking once your data structure changes. If you find yourself struggling with spreadsheets, do yourself a favor and take some time to learn the fundamentals of SQL.
SQLite
One of the best ways to ease yourself into using SQL, is to become familiar with SQLite. SQLite is an open source, cross-platform “software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine”. SQLite databases are actually simple, standalone files, which are ultra portable and work across platforms. Because of this simplicity, SQLite databases can easily be embedded in software applications, such as web browsers, and mobile apps, making it arguably the most deployed database in the world. The Fulcrum mobile apps utilize SQLite to manage data on both Android and iOS devices.
SQLite ships with a simple command-line utility, but there are also plenty of great Graphical User Interfaces for working with SQLite databases. DB Browser for SQLite is one of the best freely available, open source, cross-platform SQLite utilities I’ve come across. This utility provides a simple visual interface for creating, viewing, editing, and querying SQLite database files. One of the most useful features is the ability to import CSV files as tables (create or append), in addition to exporting existing tables, queries, or views as CSV.
Managing Exports With SQLite
While Fulcrum Small Teams and larger plans include the ability to export directly to SQLite, I prefer to export data out of Fulcrum as simple CSV files and manage the database locally. My typical workflow is as follows:
- Create a new, empty SQLite database file.
- Export your data out of Fulcrum, choosing the CSV format.
- Unzip the export and import your relevant CSV files as new tables in the database. I typically create tables for the parent element, as well as any child elements.
- Execute SQL statements to query your data to find information such as:
Record counts: SELECT COUNT(*) FROM my_table
Filtered records: SELECT * FROM my_table WHERE status = ‘excellent’
Distinct values: SELECT DISTINCT(updated_by) FROM my_table - Build a view that displays the data however you want, with table joins, field renaming or re-ordering, concatenation, sorting, etc.
- Optionally export the view back out to CSV for use in spreadsheets, reporting, or GIS software.
For data updates, simply rename the old table (perhaps appending the date) and import the new CSV file as the main table. With this process, you can retain your historical data while always having your latest data available for viewing, querying, or exporting in your standard format. If the data structure ever changes within Fulcrum, you can simply update your view statement to add, remove, or modify your field definitions. Offsite backups are as easy as uploading the entire SQLite database file to FTP, Dropbox, Google Drive, etc.
Helpful Fulcrum SQL Queries
The following queries demonstrate how the power of SQL can be used to join and query related tables, which were exported out of the standard Park Inventory App. It is important to note the following system fields, which are used to link related records in Fulcrum:
Fulcrum ID: Unique ID for the record. The column header will be labeled fulcrum_id in the exported data. This field is used to maintain a globally-unique reference to that record.
Fulcrum Parent ID: The fulcrum_parent_id is only visible on exported files that contain child records. The parent ID column contains the Fulcrum ID of the parent record that the record is associated with in Fulcrum. It can be used in a database system or GIS to create relationship links or table joins. When you import data back into Fulcrum this field is used to link the child records to their parent record.
Fulcrum Record ID: If you have multiple repeatable sections nested together, the fulcrum_record_id column will always show the root record ID at the top level.
- Get all park features that are damaged or need repair: SELECT * FROM park_inventory_features WHERE status IN (‘Damaged’, ‘Needs Repair’);
- Get count of distinct feature types: SELECT DISTINCT(feature_type), COUNT(feature_type) AS count FROM park_inventory_features GROUP BY feature_type ORDER BY COUNT(feature_type) DESC;
- Join park (parent) fields to feature (repeatable) records: SELECT parent.park_name, parent.operator, parent.type_of_facility, child.latitude, child.longitude, child.feature_type, child.status, child.issue_comment, child.photos_url FROM park_inventory_features child INNER JOIN park_inventory parent ON parent.fulcrum_id = child.fulcrum_parent_id;
- Get a count of features per park (child record count): SELECT park_inventory.fulcrum_id, park_inventory.park_name, features.feature_count FROM park_inventory LEFT JOIN (SELECT fulcrum_parent_id, COUNT(*) AS feature_count FROM park_inventory_features GROUP BY fulcrum_parent_id) features ON park_inventory.fulcrum_id = features.fulcrum_parent_id ORDER BY feature_count DESC;