Reference Data is a handy automation tool that uses data uploaded to your account to automatically populate fields. The very first step in using Reference Data is formatting your spreadsheet.

Reference Data

Reference Data makes it simple to keep your forms up to date, consistent, and error-free. Automating this on the back-end will save you loads of time in both the field and the office.

With Reference Data, users will simply choose the right option from a list, rather than try to memorize it. Reference Data can be implemented for many use cases, such as:

• Customer database
• Product and pricing information
• Inventory
• Employee database
• Other data specific to your business or industry

This is not an exhaustive list and there are many creative ways that Reference Data can be utilized. We recommend applying it to your forms early and as often as possible.

Three Purposes for Reference Data

Before we start formatting our spreadsheet, we should consider how this data will be used in the form and which of these three functions are needed for the use case:

Dropdown List

You can use a Reference Data file to provide the values for a dropdown field so you don't have to type them in manually. For example:

• Select from a list of customer names.
• Select from a list of weather conditions.
• Select from a list of employee names.

Auto-populate

Autofill a field based on a value chosen in a previous field. For example:

• Select from a list of parts and autofill price, manufacturer, warehouse location, and item number.
• Scan a barcode and use Reference Data to populate all of the info about that item or product. 
• Select a customer’s name from a list and automatically populate their address, phone, email, etc.

Dependent Reference Data

The parent field filters the next field to choices specific to the first and the second dropdown filters subsequent fields to even more specific choices and so on. For example:

• Select a car make from the first dropdown. The next dropdown will then show only the models associated with that make. The third dropdown will only include trims associated with that model. Subsequent dropdowns can continue to filter out features, years, colors, etc. associated with previous choices.
• Select an apartment complex from the first dropdown. The next dropdown will show only the buildings from that complex. The third dropdown will only list the floors of that building so that in the fourth dropdown, the user will only see the unit numbers of the floor chosen. Finally, each unit can automatically populate the correct layout.

Formatting the Spreadsheet

In order to get to the good part, automatically populating fields, building the Reference Data file properly is the very important first step. Luckily, it’s the same format whether you use Excel or Google Sheets. For this example, we will use Customer Information as it is an incredibly common use case and demonstrates this feature well. 

Our first field is "Customer Name," which will be called the Parent Field in future steps. When building either the form or the file, the parent field should be a unique identifier that will be obvious to your users. This field should be what your users could confidently pick from a list. For example, a user will more likely recognize a customer's name over their email.

After determining the parent field, the rest of the field labels are filled in across the first row. These labels determine not only the data that will fill down the column but the order is also relevant for Dependent Reference Data.

We recommend labeling the column headers either exactly as the fields are labeled in the form or with distinguishable information, like "Billing Address" and "Shipping Address" if there are multiple Address fields within the form. You will see in future steps that this attention to detail will make it easier when associating the columns in the file to the fields in the form.

Once the labels are added, fill in the corresponding data down the column so that related information goes across one row.

Each file can have up to 15 columns of data. If you need additional columns, another separate file will be needed. Check out the article on how to properly format and connect these files for seamless data population in the form.

If you used Google Sheets, your file will automatically save and be in the format that GoCanvas can read. If you use Excel, you will need to Save As a Comma Separated Values (.csv) file type.

The next step is to upload your file to the Legacy Builder or to the Reference Data page under Data Sources in the left navigation, as is required if you are using Google Sheets, the Advanced Form Builder, or the Basic Form Builder.