What do I do if I hit the 15 column limit in Reference Data?

If you have hit the 15 column limit and need additional space, there is a solution for connecting the data across files. 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 Webservices. 

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. 

Note: Do not use the CSV UTF-8 (Comma Delimited) (.csv), or any specialty .csv file (i.e. Macintosh Comma Separated, MS-DOS Comma Separated, etc.) as these file types may introduce random characters or not work as expected.

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

Note: If you are adding reference data to a Department, click My Department.

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 click on 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.

Although the layout of the Advanced Form Builder and the Builder BETA are slightly different, the mechanics of connecting two files is the same. For the sake of this example, descriptions and screenshots will be taken in the Advanced Form Builder, but the concept can also be applied to Builder BETA using its own layout.

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. Click Publish to device to test on your device.

Learn about creating dependent Reference Data using a single file.