How to set up Dependent Reference Data in the Advanced Form Builder

Another way that reference data can be used in your forms is with dependencies. Think of Dependent Reference Data as a filter within your form. The parent field filters the next field to choices specific to the first and the second drop down filters subsequent fields to even more specific choices and so on.

A great way to understand how powerful dependent reference data can be is to think of a vehicle database. First, the make of the vehicle, or brand, is selected. Then in the next drop down, you will only be presented with the models available from that brand. You can then choose the body style appropriate to that model. There may then be details specific to that body style that we only want to see when associated with the appropriate model. 

Hopefully it is clear that using dependent reference data to filter information in a form would be incredibly useful for data integrity and smoother form filling experience.

Step 1: Creating your Reference Data File

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.

The example we will use today is an apartment complex. If you can imagine a complex of apartment buildings, zoom into one of those buildings, then a floor within that building, and then a singular apartment. Each unit has its own layout and unit 101 in one building may be a studio but a 2 bedroom/2 bath in another building. So using dependencies, we are able to select the building, floor, and unit in order to pre-fill the correct layout.

A sample of that spreadsheet may look like this:

Step 2: Uploading your Reference Data 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 your Reference Data to your Form Fields

Although we are using Advanced Form Builder for this article, the mechanics are the same in Builder BETA. 

Mapping the Parent Field

1. Find or create a field that will be attached to reference data. In most cases, it is easiest to use the drop down field type for your parent field. Select it to open Field Settings in the left panel.

2. Expand the Reference Data tab.

3. Select the correct reference data file from the first drop down menu.

4. The Reference Column is the column of data from your file that you want to appear in this field.

This completes mapping the parent field.

Mapping the Second Field

1. Find or create the next field. A drop down menu is still the easiest field type for the filtering effect we are trying to achieve.

2. Expand the Reference Data tab in the field settings.

3. Select the same reference data as the parent field.

4. Select the column this field will be referencing. Remember, if you name the fields the same thing as the column header, this should be easy.

5. In order to have this field know automatically what field values are appropriate based on the selection in the parent field, we need to tell it where the parent field is located and what it is called.

Reference Screen - This is the name of the screen where the parent field is located. The parent field could be located either on the same screen or on an earlier screen.

Reference Field - The name of the parent field. 

Once you have filled in all four drop down menus, this field is complete.

Mapping the Third Field

Now we are reaching the point in mapping that veers from standard reference data. If we wanted all fields to automatically populate off the parent field, we would repeat the last set of steps until all fields were mapped. However, we are trying to create dependencies so that each drop down menu is filtered based on the choice in previous fields. So the next field needs to reference the second field and not the parent field.

1. Find or create the next field. A drop down menu is still the easiest field type for the filtering effect we are trying to achieve.

2. Expand the Reference Data tab in the field settings.

3. Select the same reference data file.

4. Select the column this field will be referencing.

5. Select the screen where the previous field is located.

6. Select the name of the previous field. The parent field may automatically populate here so just select the previous field from the drop down menu.

Repeat these steps until all fields are mapped, remembering to always reference the field that influences the choices within the field you are currently mapping. Take a look at the following screenshots to see how this plays out with a fourth and fifth field.

If "Apartment Unit" is the last field that contains more than one choice, any remaining fields with only one possible value will reference "Apartment Unit." Remember to publish and test your form to make sure all drop down menus are populated correctly.

Note: remember that all possible choices and combinations must be accounted for in the spreadsheet. This means there will be a lot of repeated values in the spreadsheet but showing them in the form will be complicated. For best results, leave "Show Duplicates" unchecked. If you are using a loop screen with dependent reference data, remember to turn on "Allow Duplicates" in the screen settings.