SQL Datasource Regions

SQL Datasource Regions

Overview

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

Tutorial Video

A tutorial video is also available and covers most of this material.



 

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 file name, 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 the Datasource queries to match. 
Note: Make sure your Excel sheet names and headers do not have leading or trailing whitespaces. 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.

Using Forms Designer Preview Mode to Test SQL Datasource Regions

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 Preview mode to test your SQL Datasource Regions.

Uploading the Form Data File for Published Forms

In order for your live, published Form(s) to access the Form Data File, you have to upload the file to the Form Data Files section of the Portal. For more information on managing your Form Data Files, read the article on Form Data Files.

Setting Up Databases in Forms Designer

Multiple Form Data Files can be used in a Form if you need to pull data from different Excel or Access files but you will need to define each one as a separate database.

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. Enter the filename including the extension into the Enter just the filename of an uploaded form data file field.

6. Click Apply.

7. Select the name of the database you want to use for this Region.


8. 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 selected database 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 Query Name.
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 the same 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 for your Region.


6. Click OK to close the SQL Query Manager dialog box.

Note: 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 and that the file is closed so that your data can be accessed in Preview mode.

Using Parameters

Datasource Regions can pull data from a Form Data File using Region values or hard-coded values as parameters. For example, a Region could pull the price from an Excel file based on the product selected from another Datasource Region.

1. Set up your query as described above but include the required conditions for your query in the Command Text field. 

2. If you want to use a value from another Region, use a question mark as a placeholder for the Region value in the query. If your condition is a static value, include that value in the condition. 


3. Type the name of the Regions you want to use in the Parameters field using the format {Page#@RegionName}. If you have multiple parameters, each one needs to be on a new line. 


4. Click OK.
 

Query Syntax Reference

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

[table$] where table is the sheet name
SELECT DISTINCT column
FROM table
SELECT DISTINCT [column]
FROM [table$]
DISTINCT modifier removes duplicate items from the data
SELECT column
FROM table
WHERE condition
SELECT [column]
FROM [table$]
WHERE [column] = ?
? is a placeholder for the Region parameter defined in the Parameters section of the Datasource tab

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.


    • Popular Articles

    • Forms Designer Quick Start Guide

      Overview Field2Base Forms Designer is the proprietary software application that allows your existing paper forms to be quickly converted to a smart E-form available to your end users via our mobile and web-based Mobile Forms applications. This ...

    • Portal 11.28.2023 Release Notes

      Overview Our release notes offer brief descriptions of product enhancements and bug fixes. We include links to the current articles for any affected features. Those articles will be updated shortly after the Portal release to include new ...

    • Integration Service Configuration Guide

      How To Configure Integration Services To Allow Read/Write Access on a Network Path All of our Integration Products, including the DIM, DUU, and EDM have respective Windows Services responsible for communicating with our server. Occassionally, ...

    • Data Integration Module (DIM) Migration Guide

      Overview This article provides the information necessary to migrate the Field2Base Data Integration Module (DIM) over from one server to another. Please refer to the DIM Install Guide for the initial installation of the Field2Base DIM. Once that's ...

    • How to Check the Version of Integration Products Running on a Windows 10 Machine

      Right-click on the Start menu button. Click on Apps & Features. In the Apps & Features search bar type in the Integration Product you are looking for, eg. F2B Data Integration Module, F2B Data Upload, or F2B Enterprise Dispatch Module. Click to ...