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.
The Configure External Column Settings box opens up. Go to New Configuration to set up a new column.
NEW CONFIGURATION
The Configuration screen has several options, as explained below the image.
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.
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.
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.
New Mapping
Refer this link for more details.