Add a New Database Lookup Using Query

Add a Database Lookup using query to use the result set in the lookup.

• Follow the steps for Add a New Database Lookup till you a get the New Database Lookup dialog box.

To add a Database Lookup using a Query

1. Select the Database connection. Enter the required query. Data will be retrieved based on the query from the database.

   Note:
   - You can write queries to fetch data from tables, synonyms and views.
   - The use of aliases, aggregate functions, joins in database lookup query is supported through the use of views.
   - Click here for a scenario on handling aliases, aggregate functions, joins in the database lookup query.
   - The Database Lookup only supports the dbo schema by default. However, you can create views to support any other custom schemas.
   -The database lookup query supports the use of CASE expression. Click here for a scenario on using CASE expression in the
   query.

2. Click Load Parameters to load parameters from the query, and then select a data type for each listed parameter.

   Note:
   - For SQL database, the supported data types include Big Integer, Boolean, Byte, Char, Date, DateTime, Decimal, Double, Guid, Integer, Single, Small Integer, and String..

3. Enter the column name to be used as Value field in the lookup input control. The value field column(s) must be one of the selected columns in the Query field. Example: Employee.EmployeeId denotes Employee table and EmployeeId column.

   Ensure that the case of the table name used in the Value field matches with the case of the table name in the query. For example, if the table name given in the query is EmployeeDepartment, then an entry employeeDepartment in the Value field, is considered invalid.

   Note:
   - If required, you can enter multiple column names separated by commas.
   - Please ensure that the column names are always preceded by table names.
   - Also, the column name(s) should identify a unique row in the table.
   For example, TableName.Id,TableName.title,TableName.description

4. Enter the column name to be used as display field in the lookup input control. Example: FirstName.

5. Click Validate & Preview. It validates all the entries made for the creating the database lookup. If all the entries are valid, a preview of the data fetched by the database lookup is displayed.

   Also, if the database lookup query is with parameters, you can preview the data after providing valid sample inputs. Click here for a database lookup scenario based on query with parameters.

   Note:
   Following are some of the important checks to be done to ensure that the entries for the database lookup are valid:
   - The Title of database lookups cannot have the following special characters: ~! @#$%^&*()+|`-=\\{}:\"<>?[];',./ - (Including blank space). The only allowed special character is _ (underscore).
   - The Title of database lookups cannot begin with a number. For example, 12EmployeeDetailsDBLookup is an invalid entry.
   - The query entered in the Query field must be syntactically correct.
   - Also, the query validation will fail when the query is executed on a invalid database connection or the table name or column names used in the query are invalid. For example, if the query entered is "Select * Employee Where Id=@id", on click of Validate & Preview, an error message is displayed as the query is syntactically incorrect.
   - All the columns entered in the Value field must belong to the same table. For example, Employee.EmployeeId,Department.DeptId is an invalid entry.
   - The columns entered in Value field must not repeat. For example, Employee.EmployeeId,Employee.EmployeeId is an invalid entry.
   - The columns entered in the Value and Display Name fields must be a part of the query. For example, the query is "Select Id, Dept, Designation From Employee". Value field entered is Employee.DeptId and Display Name is EmpName. This is an invalid scenario as the columns entered for the Value and Display Name fields are not a part of the query.
   - The column specified in the Value field should identify a unique row in the table.

6. Click Close in the Preview screen. Click Save in the database lookup screen to save the changes to the database lookup.

Please note the following important points about Database Lookup:

• If Lookup fails to resolve the Display Column value based on the Lookup value, then the Lookup value will be shown as the display text for that particular Lookup.

• For lookups created on case sensitive databases, ensure that the queries entered are also case sensitive.

• Do not use an image field (type: Byte Array) as the value field for the Database lookup

• Display Name of database lookups which have been created on external (i.e. non repository) databases, cannot be resolved when viewed from the Lists grid.

• The parameters used in the database lookup query should not have any special characters, should not be blank and should be in columnname=@parametername format.

• Also, note the following important points while creating Database lookups to be associated with the Grid Configuration:

• If any of the columns selected in the database lookup used in a Grid Configuration are image fields of type Byte Array, the Data Grid on rendering does not show the images. The image columns will be shown as System Byte. Also, these columns cannot be used in workflows.

• If the column names in the selected data source lookups have any special characters other than _, the user will not be allowed to select them in the Persist Columns field. Also, if the column names have space or begin with numbers the user will not be allowed to select them in the Persist Columns field.

• If the value field in the lookup which is consumed in a Grid Configuration has a special character, space or begins with a number, the Grid Configuration will be considered invalid.

• Filter on Display Value for Data Lookup control is through SQL Like operator. Therefore, we recommend setting the Display Name column, which is of string data type. For more information, refer to Supported Data Types.