Solution for the 15 Column Limit in the Legacy Builder

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.

15 Column Limit Solution

This solution also 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.

This method works for either situation, but keep in mind that this simplified example uses the parent field as the connecting point and your situation may not be as straight forward.

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 Web Services. 

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 column header you create in the first file matches the column header you use in the second.

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 builder or on the Reference Data & Images tab. If you are using the Builder or Google Sheets, all reference data files must be uploaded on the Reference Data & Images tab. Learn more the Google Sheets integration here.

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 select Create New if you are building one 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 where the first file will be attached to open field settings in the left panel.
2. Scroll down the field settings and expand the Reference Data tab.
3. For the Reference Data drop down select the file you uploaded for the first list.
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 or create the field that will reference the previous field we mapped.
2. Scroll down the field settings in the left panel and expand the Reference Data tab.
3. For the Reference Data drop down select the file you uploaded for the second list.
4. In the Reference Column option, select the corresponding header that this field will reference. In our example, that will be "Item."
5. 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."
6. In the Reference Field drop down select the field mapped to the connecting column, i.e. "Manufacturer."                                       
7. Publish to device to test on your device.