Calculations & DataSources

Using DataSources in a Form Template: Database and Barcode Fields

Last Updated: Jan 22, 2019 12:44PM PST

Note: This article assumes a basic understanding of GoFormz DataSources. See here to learn more about DataSources.

GoFormz supports two main use cases for DataSources within a form template:

  1. Auto-populating fields. Use a DataSource to auto-populate fields in a form based on the value of another field.
  2. Option filtering. Use a DataSource to present a limited set of options to a user based on the value of another field.

Before you can configure your template to take advantage of these use cases, you must have a DataSource set up. See here for more on creating a DataSource. Once you have a DataSource 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, or scans the customer's barcode. To do this, you need a DataSource that has the customer name as its key column, and has address / phone number columns for each customer. See here for more on DataSources 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). Typically, either a Database or Barcode field is used as the key field, letting a user choose a customer name from a dropdown (Database) or scan their barcode (Barcode) — but you can use a regular Text field here as well. Once the key field is set up, you can add auto-populated fields that reference the value 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.

Key field setup: Database

If using the Database field as your key field, drag and drop it from the Template Fields panel on the left side of the Template Editor, then do the following in the Properties panel:

  • Select your DataSource in the Data Source property. In this example, we’ve selected "AXIS Customer List".

  • Select the DataSource 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. You will typically want to select the Key column here to ensure uniqueness, but can use a different column as well.
          

  • Set up other field properties as you would for any other field.

Key field setup: Barcode

If using the Barcode field as your key field, simply drag and drop the field from the Template Fields panel into your template. No additional setup is necessary. However, make sure that all expected barcode values are contained in the Key column of the DataSource you plan to use.

Auto-populated field setup

Creating an auto-populated field based on your key field works the same way regardless of whether you used Database or Barcode as your key field:

  1. Drag and drop a Text field into your template.
  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 DataSource. 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 DataSource 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 or scan in the customer's barcode — and GoFormz will automatically populate your Address field with the Address from your DataSource that corresponds to the selected customer. You can continue by adding auto-populated form fields for other DataSource 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 or scans in their barcode, 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 DataSource 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 DataSource is indexed on the manufacturer column, since GoFormz cannot display non-indexed columns in a database drop-down. See here for more on DataSource indexing.

Setting up the initial field is exactly the same as setting up a key field for our first use case:

  • When using the Barcode field, simply drag it from the Template Fields panel into the template, and make sure your DataSource contains all expected barcode values.
  • When using the Database field, make sure to set up its Data Source and Display Column properties appropriately. In the example below, we've chosen the "Parts" DataSource and the "Mfg." column.

 
To set up a filtered field, do the following:

  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 DataSource.

  3. In the Filter property, enter the filter for this field. In this example, we use ="Mfg."=[Manufacturer]. This indicates that we want to only display the part numbers associated with DataSource 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.

Addendum: Database filter 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 DataSource has the following 4 columns, and our form has the following 4 corresponding fields:

DataSource 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://assets3.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