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

 

---

Article Sections 

Setting Up the Database

Setting Up the Query

Using Parameters

Query Syntax Reference

Uploading the Form Data File into Admin Portal

 

---

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. 

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

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.

Hint	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 for query syntax 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.

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

 For details of SqLite, please refer to https://www.sqlite.org/lang_corefunc.html.

 

---

Uploading the Form Data File into Admin Portal

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 in Admin Portal, read the article on Form Data Files.

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

 

2. Click on the Upload File button.

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