External Data Columns

<< Click to Display Table of Contents >>

Navigation:  NITRO™ Forms > Left-side Pane > Form Settings >

External Data Columns

External Data Columns allow you to read data from a database into the form. Refer to the info below and also see this FAQ for more information.

 

To set up an External Data Column, go to Form Settings on the left-side pane, expand that, and choose External Data Columns.  

 

external data column

 

 

The Configure External Column Settings box opens up. Go to New Configuration to set up a new column.

 

New configuration of external data column

 

NEW CONFIGURATION

The Configuration screen has several options, as explained below the image.

 

 external column setting 1

 External column setting 2

 

 

 

 Configuration Name: the name you give this configuration.

 Query Output: Either Multiple Records or a Single Record.

 

 Multiple Records

This is used when list of items is maintained in database table and on the Form user needs to make a selection of one item. For example:

oGet all departments and let user select the department for a purchase request

oGet all employees of the department (based on selected department on the Form) and let user select the employee who will be approving the purchase request

This configuration uses two list columns, one for the display and selection of the database record, and other for storing the unique value of the database record

 Single Record

This is used when a single record is fetched from database and one or more values from the record need to be mapped to corresponding SharePoint list column. Typically this record will be fetched based on the data already on the form. For example:

oBased on the selected employee on the form, fetch the employee’s email and phone number and populate these values on the corresponding columns on the Form

In this case multiple attributes fetched from the database record can be mapped to corresponding columns on the form. Supported SharePoint list column types are Single Line of text, Multiple lines of text, Choice, Number, Currency, Date, Yes/No and Lookup

 

Web Service Settings/Service Type: "Default" means using Crow Canyon's Azure services. "Custom" means using your own services.

         When "Custom" is chosen, a box to enter the Service URL appears.

 service type

 

 Database Settings

         Secure Connection String: Yes or No choice.

         Connection String: the SQL Server database connection string.

         Select Query: the Query to run against the database.

 

 Test Connection: allows you to test the connection to the SQL database.

 

Query Parameters: Parameters that can be used in the Query, such as column values from the list item and system settings (day, time, Me). For example, the form might have a place to enter the "Member No." or "Order ID". The value entered in that field can be used in the SQL Query.

 

 Database to SharePoint Column Mapping

 This matches the database column with the SharePoint column so that the values returned by the Query are put  in the right columns in the SharePoint list.

 

Multiple Records

The Multiple Records option will fill in a drop list with the returned values from the Query. If the returned values are too many, "Enable Search" can be used.

         

 Enable Search -- enable this option to search multiple record.

Allow Multiple Values: If this option is enabled, it is used for Multiple Records when the query returns many items and a Search on the NITRO form is used to find the item rather than filling a long drop list of choices.

Value Separator: specify value separator to separate multiple values.

 

Database to sharepoint mapping

 

Single Record

When using the Single Record option, the fields returned in the Query can be matched to multiple columns on the SharePoint list. Each field in the Query is mapped to a column in the list by using the New Mapping option.

 

         Single record

 

 

 

New Mapping

 

New mapping in external data column

 

 

Refer this link for more details.