Calculations & Data sources

Configuring Database and Barcode Fields in your Form Template

Last Updated: Apr 17, 2017 03:19PM PDT
 

GoFormz supports two main use cases for database fields:

  1. Auto-populating fields. Use a data source to auto-populate fields in a form.

  2. Option filtering. Use a data source to present a limited set of options to a user based on the value of another field.

We will go over each use case separately below.
 
Before you can configure a database field in your form template, you must set up your data source. For more on data sources and how to create them, see the links below:

Once you have a data source set up, open your template in the Template Editor, and follow the appropriate use case below.

Use Case 1: Auto-Populating Fields

The first use case is auto-populating fields in a form. For example, your form might have fields for customer name, address, and phone number. You may want the address and phone number to fill automatically when a user selects the customer name. To do this, you need a data source that has the customer name as its key column, and has address / phone number columns for each customer. See here for more on data sources and key columns.
 
To set this up in your form template, you must first create your key field (the Customer Name field in our example), then add auto-populated fields that reference the value selected in the key field. In our example, we will want to auto-populate fields like Address, City, State, Zip Code, and Phone Number based on the Customer Name selected by the form user.
 
To create the key field in your template: (Note: If using a Barcode field instead of a Database field, please make sure that all values are added to the key column of your datasource. The steps below apply to Barcode fields and Database fields)

  1. Add the Database field to your template by dragging and dropping it from the Template Fields sidebar on the left side of the Template Editor, as you would with any other field type.
  2. In the Field Properties area:
    • Select your data source in the Data Source property. In this example, we’ve selected "AXIS Customer List".
    • Select the data source column that you want to display in this field using the Display Column property. In this example, we’ve selected the "Key" column, which is the customer name.
            
  3. Set up other field properties as you would for any other field.
    This is all you need to do for the key field.

To create an auto-populated field based on your key field, do the following:
  1. Drag and drop a Text field into your template, as you would with any other field.
  2. In the Default Value property, specify the value that GoFormz should place in this field, using the following syntax: =database("Datasourcename", [KeyField], "Column Header"), where:
    • Datasourcename is the name of your data source. In our example, this is "AXIS Customer List"
    • KeyField is the name of the key field in your template that should be used for the database lookup. In our example, this is "Customer Name"
    • Column Header is the name of the column in your data source that represents the desired entry for this field. In the example below, this is "Address".
    In our example, the Default Value should be =database("AXIS Customer List", [Customer Name], "Address"). Note that you MUST use double quotation marks in the syntax -- single quotes will not work.
  3. Set up other field properties as you would for any other field.
 
Now, when a user fills out a form based on this template, they will be able to select a Customer Name from a drop-down menu -- and GoFormz will automatically populate your Address field with the Address from your data source that corresponds to the selected customer. You can continue by adding auto-populated form fields for other data source columns, like City, State, Zip, and Phone -- as shown below.
address-beverly.jpg

 

Use Case 2: Option Filtering

The second use case is filtering the list of options presented to the user based on the value of another form field. For example, your form might have fields for the manufacturer and number of an electronic part used in fulfilling a work order. Once a user selects the manufacturer, you will want to present them with a list of parts for that manufacturer only -- so if they select Cisco, they should only see Cisco parts; if they select Juniper, they should only see Juniper parts, etc. This is what we call option filtering.
 
For this example we would need a data source of parts that has columns for the part’s manufacturer and number; the part number can be the key column as it is in the examples below, but it does not have to be. We would want to make sure that the data source is indexed on the manufacturer column, since GoFormz cannot display non-indexed columns in a database drop-down. See here for more on data sources and indexing.
 
Setting up the initial field is much like setting up the key field in the first use case:

  1. Add the Database field to your template by dragging and dropping it from the Template Fields sidebar on the left side of the Template Editor, as you would with any other field type.
  2. In the Field Properties area:
    • Select your data source in the Data Source property. In this example, we’ve selected "Parts".
    • Select the data source column that you want to display in this field using the Display Column property. In this example, we’ve chosen the "Mfg." column, representing the part manufacturer.
  3. Set up other field properties as you would for any other field.
    This is all you need to do for the first field.
 
Next, set up the filtered field:
  1. Add another Database field to your template via drag-and-drop.
  2. As before, select the Data Source and Display Column in the Field Properties area. In this example:
    • Data Source = "Parts"
    • Display Column = "Key", which is the part number column in this data source.
  3. In the Filter property, enter the filter for this field. In this example, we use . This indicates that we want to only display the part numbers associated with data source rows where the "Mfg." column has the value that the user entered in the "Manufacturer" form field. So if the user selects Juniper as the manufacturer, only Juniper part numbers will be displayed. This is the simplest form of filtering. See below for more on the filters supported by GoFormz, and the associated syntax.

The Filter property supports equality and inequality operators (= and <>), as well as the logical operators And, Or, and Like. The logical operators can be nested using parentheses. We give some examples of how you might use these below.
 
For the purpose of these examples, let’s suppose our Parts data source has the following 4 columns, and our form has the following 4 corresponding fields:

 

Data Source Column Form Field Description
Key Part Number The unique number associated with this part.
Mfg Manufacturer The part’s manufacturer (e.g. Juniper, Cisco, etc.)
Device Device Type The type of device this is (e.g. router, server, etc.)
Color Color The part’s color (e.g. black, white, etc.)

Given this setup, the examples below represent some possible filters for the Part Number field in your form template. These examples should be sufficient for you to construct your own filters.
 
And operator =And("Mfg"=[Manufacturer], "Device"=[Device Type]) Return all part numbers for a given manufacturer and device type -- e.g. all Cisco routers.
Or operator =Or("Mfg"=[Manufacturer], "Device"=[Device Type]) Return all part numbers that match either the manufacturer or the device type -- e.g. all Cisco parts as well as all routers.
Like operator =Like("Mfg", [Search Field]) Return all part numbers starting with the text that is in the Search Field. For instance, if the letter J is entered in the Search Field, then you will see all part numbers starting with the letter J.
<> operator ="Mfg"<>[Manufacturer] Return all part numbers for manufacturers other than the selected one.
Nested Ands =And(And("Mfg"=[Manufacturer], "Device"=[Device Type]), "Color"=[Color]) Return all part numbers for a given manufacturer, device type, and color -- e.g. all black Cisco routers.
Nested And/Or =Or(And("Device"=[Device Type], "Color"=[Color]), "Mfg"=[Manufacturer])) Return all part numbers that match the device type and color, or the manufacturer -- e.g. all black routers as well as all Juniper devices.
f96791875af3f069482b7b5788b9b70b@goformz.desk-mail.com
http://assets2.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete?b_id=13874