Creating Lookup Tables in Fulcrum

February 15, 2018 by Coleman McCormick

We’re fans of keeping it simple when it comes to data modeling. While Fulcrum is flexible when it comes to making changes, it’s always helpful to start simple and add complexity as you need it when building your apps. One common and powerful use case for data relationships is creating lookup tables for common datasets to be referenced in your apps. Fulcrum’s Record Link data type allows you to establish these relationships across apps to maintain consistent, standardized data for lookup fields.

Lookup Tables

The power of a good database model

A good relational model helps keep your data organized and easier to use, improves reporting, and protects certain data from being edited. Many use cases can call for these data structures, and we wanted to review to some best practices for creating powerful relationships in your Fulcrum data.

In the world of relational databases, these data models can get intricate and difficult to be used by humans directly. Properly setting up a relational database with something like MySQL, PostgreSQL, or even Microsoft Access involves a deep understanding of how those tools work. With Record Links and Fulcrum, we’ve made it more user-friendly to get the power of the relational database without the opaque, confusing setup for the non-database expert. Given the nature of Fulcrum as a field data entry tool, there are also a number of customizable settings that Record Links have to help speed up collection and greatly improve data quality.

Asset inventory: a practical application

A use case we see a lot of, particularly in public works, is asset inventory. Let’s look at a transportation use case: collecting and maintaining an asset list of road signs. This is done all across the country to monitor the quality of signage — are they readable? Are they reflective enough? Do they meet the regulatory standards? Transportation departments everywhere spend big to optimize their capital expense on new or replacement signage.

Sign Inventory

A key regulatory aspect is making sure signs meet sizing, height, and reflectivity requirements. The Federal Highway Administration (FHA) maintains a catalog of signing and marking standards useful for reference during inspections. In this video, we’ll walk through a practical example of how you can use Record Links to lookup an asset type from a standard dataset, and from within that table populate other data into an inspection report.

Step by step, we’ll:

  1. Build a Sign Types app to house the FHA standards lookup table
  2. Build a basic Sign Inventory app for sign inspection, with accurate locations, and a Record Link to reference types from our table
  3. Configure the Record Link to provide the right lookup method for our field inspectors
  4. Collect some sample asset inspections to see Record Links and lookup in action
  5. View our data in Fulcrum Editor on the web to see the established relationships

Do you have your own application for lookup tables in your data collection workflow? Get in touch with us with your ideas or for assistance in adding Record Links to your process.

Coleman

About the author

Coleman is VP of Product at Spatial Networks, working every day with our customers and our design team to bring better data management capabilities to users.

Sign Up Today & Start Collecting Data In Just Minutes!

Start Your FREE Trial