If you have hit the 15 column limit for your Reference Data and need additional space, there is a solution for connecting the data across files, or linking multiple Reference Data files to each other within one form.

15 Column Limit Solution

This solution applies to the example we will use in this article, showing how a simple list in one file can be connected to a more detailed list in another file.

GoCanvas has several file creation methods available: comma separated value (also called CSV) files, the Google Sheets Integration, integrating with Procore or other database software, or using GoCanvas API v3.

Step 1: Creating your Reference Data Files

Reference Data files are most easily created using Google Sheets. If you use Excel, you will need to Save As a Comma Separated Values (.csv) file type. 

This solution uses two or more files. For simplicity sake, our example singles out the parent field and consolidates the options. Your own file may have up to 15 columns.

You will need to repeat one of the columns in the first spreadsheet in the second spreadsheet to connect the two datasets. In our example, although consolidated in the first spreadsheet, the manufacturer is listed as many times as there are items that are produced by that manufacturer in the second. This is considered Dependent Reference Data.

If you hit the 15 column limit, your connecting column will just be the column that the 16th column is referencing. You’ll need to ensure that the connecting column label from the first file is copied into the second file with the connecting data mirrored from the first file.

Step 2: Uploading your Reference Files to GoCanvas

There are several methods to upload your file to GoCanvas. If you are in the Legacy Builder, a .csv file can be uploaded from within the Legacy Builder or on the Reference Data & Images page. If you are using the Builder or Google Sheets, all reference data files must be uploaded on the Reference Data & Images page. Please visit the Help Center article, "Upload a Google Sheet as Reference Data" to learn more.

Step 3: Mapping the Fields in your Form to your Reference Files

From the Forms page, open the particular form where you will be mapping your reference files OR expand Create New and select Advanced Form if you are starting from scratch.

Mapping your First Reference Data File

First, you will need to map the file that contains the connecting column. In our example, our file containing the connecting column also happens to be the parent field, so it is fairly straightforward. As has been mentioned throughout the article, your connecting column may not be the parent field.

1. Select the field, that will connect to the first file, in the center workspace to open Field Settings in the left panel.
2. Expand the Reference Data menu.
3. For Reference Data File, select the first file.
4. Reference Column is the column header from the spreadsheet that this field is referencing. In our example, the column is "Manufacturer" and is the only column in the file. The remaining drop down fields remain empty, as this is also our parent field.

Mapping the Second Reference Data File

1. Select the field where the second file will be attached to open Field Settings in the left panel.
2. For Reference Data File, select the second file.
3. For Reference Column,, select the corresponding header that this field will reference. In our example, that will be "Item."
4. For Reference Screen, select the screen in your form that contains the field mapped to the connecting column. In this example, the Item list is determined by the selection from the Manufacturer list. So the screen that contains the Manufacturer field should be chosen here, i.e. "Item Details."
5. For Reference Field, select the field mapped to the connecting column, i.e. "Manufacturer."

Finish mapping any remaining fields from the second file until all fields are mapped.

Save and Test to check that all fields are working as intended. Publish to double check on your device.