SQL Datasource Regions

SQL Datasource Regions


This article describes how to set up a SQL Datasource Region in Forms Designer. A SQL Datasource allows you to pull data from external Form Data Files (Excel or Access files) into your Form. This is a convenient option when you need to make updates on a regular basis because you only have to update the data in the Excel or Access file instead of making any changes to the Form itself. It also allows you to pull data from a single place into multiple Forms across your company.

Formatting the Form Data Files

The SQL Datasource Region in your Form will pull data from your Microsoft Excel (.XLS / .XLSX) or Access (.MDB / .ACCDB) files based on the filename, the sheet name, and the column name. You should have all of these names defined before setting up your Datasource queries. If you change any of these names in your Form Data Files, you will need to update your Datasource queries to match. 
Note: Make sure your Excel sheet names and headers do not have leading or trailing whitespaces. Sheet Names cannot start with a number and cannot include any spaces. The names in your queries have to match exactly and will not work if extra spaces exist. Avoid special characters since some characters interfere with queries.
Note: If the first five cells in a column are numeric, those values will automatically be converted to numeric format and not allow any other types of values in your Datasource region. If some of the values in the column are not numeric, you will need to put at least one of them in the first five cells to avoid errors.

While you are setting up your Datasource Regions in Form Designer on your PC, your Form Data Files should be saved to the Documents or My Documents folder. You will then be able to access the data in Forms Designer's Preview mode to test your SQL Datasource Regions.

Setting Up the Database

Multiple Form Data Files can be used in an Form if you need to pull data from different Excel or Access files but you will need to define each one separately. To define a database to use for your Region:
1. Create a Datasource Region.
2. Select SQL database from the Datasource type drop down.
3. Click on the Databases button to open the SQL Database Manager.

4. Enter a name for the database you want to use. 
5. Leave Use a file path and Uploaded form data selected from dropdowns.
6. Enter the filename including the extension into the Enter just the filename of an uploaded form data file field.

7. Click Apply.
8. Select the name of your database from the list on the left hand side.

9. Click OK to close the SQL Database Manager window.

Setting Up the Query

A query uses syntax similar to simple SQL statements to retrieve data from the database you have defined for your Region. Multiple queries can be defined and reused throughout your Form.

1. Click the Queries button in the Datasource tab to open the SQL Query Manager window.

2. Enter a name for your query.
Note: We recommend using a query name that reflects the type of data you are retrieving. This is useful if you have multiple queries or are reusing a query in multiple Regions.
3.  Write your query in the Command Text field. See the Query Syntax Reference section below for examples.
4. Click Apply.

5. Select the desired query from the left hand list.
6. Click OK to close the SQL Query Manager dialog box.

We recommend that you use Preview mode to check that your SQL Datasource Region is working as intended. Make sure the latest version of your Form Data File is saved to the Documents or My Documents folder on your PC so that your data can be accessed in Preview mode.

Using Parameters

Datasource Regions can pull from a Form Data File using other Region values as parameters. For example, a price Region could pull the price from an Excel file based on the product picked in another Datasource Region.
1. Set up your query as described above but include the required condition(s) in your query in the Command Text. If your condition is a static value, include that value in the condition. If you want to use a value from another Region, use a question mark as a placeholder for the Region value in the query.

2.  Type the name of the Region(s) you want to use as parameter(s) using the {Page#@RegionName} format and click OK.


Query Syntax Reference

SQL Syntax
F2B Syntax
SELECT column
FROM table
SELECT [column]
FROM [table$]
[column] where column is the datasheet column header

[table$] where table is the sheet name
FROM table
FROM [table$]
DISTINCT modifier removes duplicate items from the data
SELECT column
FROM table
WHERE condition
SELECT [column]
FROM [table$]
WHERE [column] = ?
? is the Region parameter defined in the Parameters section of the Datasource tab
? IS NOT NULL with Region as a parameter
A static parameter can also used in the condition
SELECT column
FROM table
WHERE condition
AND condition
SELECT [column]
FROM table 
WHERE [column] = 'text'
AND [column] = ?
Returns data where both conditions are met
Conditions can be static text or Region values
SELECT [DateValue]
FROM [Table]
Where DATEDIFF(dd,[DateValue],GETDATE()) > 4
SELECT [DateValue]
FROM [Table$]
Where date([DateValue],'+4 days') > date('now')
Date comparison: Returns [DateValue] that's 4 days greater than current day
SELECT LTRIM([column],"")
FROM [Table]
WHERE condition
SELECT LTRIM([column],"0"]
FROM [Table$]
WHERE [column] LIKE ?
Returns trimmed data where condition is met.  For this example, trimmed data is everything after "0". Thus, if data was 0456, returned data would be 456.  This also works with RTRIM.

Uploading the Form Data File

In order for your published Form to access the Form Data File, you have to upload the file to the Form Data Files section of the portal for your Company. For more information on managing your Form Data Files, read the article on Form Data Files.

1. Log in to the Portal and go to Admin > Form Data Files.

2. Click on the Upload File button.

3. Choose the file that you want to use as a Form Data File then click Upload.

If you wish to limit the use of a Form Data File to the Form Templates and Users that are associated with a particular Folder, you have the ability to select a specific Folder. Limiting a Form Data File to a Folder will reduce the amount of resources that need to be updated during device sync'ing.  However, this selection may need to be updated if you move a Form Template to a different Folder. 

    • Related Articles

    • Creating Regions

      Overview A Region is a field on your company's Form. Regions are the individual fields you define on top of the Form background image and allow the user to provide necessary and relevant data. Regions come in all shapes and sizes, including include ...
    • Unique ID Regions

      Overview Note: The Unique ID Region Type in available in Forms Designer 6.5 and higher The Unique ID region allows the number initially assigned to increment by one every time the Form is accessed, such as an invoice number or serial number. This can ...
    • How to Clone a Form

      Overview There are situations where you may want to publish the same Form twice for use with two different groups of Users. For example, if you don't want the two groups to be able to see each others versions of essentially the same Form, then you ...
    • Region Types

      Overview Forms Designer offers various types of Regions that to allow you (the Form Designer) to customize each Region for specific content. Each Region Type has unique properties and is designed to accept a certain type of input in the Form. For ...
    • Forms Designer Quick Start Guide

      Overview Field2Base Forms Designer is the proprietary software application that allows your existing Forms to be quickly converted to an active, online, smart Form available to your end users via our Mobile Device and Web-based Applications. This ...