Create a Lookup for a Database Table

AVEVA Work Tasks allows you to create a database lookup and list lookup to retrieve data from a database. This allows the user to select from a set of options, for example list of states, countries etc. To create a database lookup, you need to connect to a database.

Before starting with the scenario, create a database connection.

To connect to a database

1. In the Enterprise Console, click the Menu button, click Settings, and then click Database Connections. The Database Connections page appears.

2. On the Ribbon bar, click New. The New Connection dialog box appears.

3. In the Title box, type the appropriate name for the connection. In this case, enter formslistscenario.

4. In the Description box, type any description. This is not mandatory.

5. Select the Connect to repository datasource check box to connect to the current repository database. A database connection for the repository datasource is successfully created and is displayed in Connection List page.

   Or

   Clear the Connect to repository datasource check box to connect to an external database. Perform the steps mentioned in the following tables.

   Field	Description
   Database Type	Select appropriate database type from the options. In this case, select SQL Server.
   Connection Type	Select the appropriate connection type from the options. Available options are Connection String and Connection Provider. Select Connection String.
   Server Name	Click the Refresh button and then select the appropriate server that you are using, from the drop-down list. In this case, you will select Server1\SQLEXPRESS.
   Authentication Type	Select the appropriate authentication type from the drop-down list. Since, you are using windows for authentication, select Windows from the drop-down list.
   User Id	Enter your user Id, if you have selected any other authentication type than Windows.
   Password	Enter your password, if you have selected any other authentication type than Windows.
   Advanced Settings	Enter any advanced settings, if required.
   Database	Click the Refresh button and then select the appropriate database that you are using, from the drop-down list. In this case, you will select FormsScenarioDB.

6. Enter the details as mentioned in the field description table.

7. Click Test Connection.

   A message appears indicating that the connection is successful.

8. Click Close.

9. Click Save.

   The database connection is listed the Connection List page.

Now, you have created a database connection. Next, you will create a database lookup for Countries. The Country lookup will retrieve the data from the database table and display the list of countries along with the other details like country code. 

To create the lookup

10. In the Enterprise Console, click the Menu button, click Settings, and then click Lookup Settings. The Lookup Settings page appears.

11. On the Ribbon bar, click New, and then click Database. The New Database Lookup window appears.

12. In the Title box, type a name for the database lookup.

13. In the Description box, type a description for the database lookup.

14. In the Command Type drop-down list, select Query.

15. Click Continue.

16. Enter the details as mentioned in the field description table.

    Field	Description
    Database Connection	Select an appropriate database connection from the drop-down list. You have created the database connection formslistscenario, so select that database connection.
    Query	Enter the query to execute and display the lookup input control. Data will be retrieved based on the query from the database. Example: SELECT * from Country.
    Value	Enter the column name to be used as value field in the lookup input control. Example: Country.CountryId. If required, you can enter multiple column names separated by commas. Please ensure that the column names are always preceded by table names. For example, TableName.Id, TableName.title, TableName.description. Also, the column name(s) should identify a unique row in the table.
    Display Name	Enter the column name to be used as display field in the lookup input control. Example: CountryName.
    Display value column in grid	Show or hide the value column in the grid. Select True to show the value column in the grid. Select False to hide the value column in the grid. The default value is True.

17. Click Save. The details are saved and the lookup is listed in the Lookup List page.

    Note: You have created the Country lookup. This lookup can be used to retrieve data in the form.

To design a form

18. Design a new form. For more information about designing a form, see Designing Forms.

19. Drag and drop the Data Lookup control in the form.

20. Edit the properties for the Data Lookup control.

21. Click the Lookup Source icon. The Lookup Source Configuration page appears.

22. Select the Database Lookup in the Data Source Type.

23. Select Country in the Data Source.

24. Click Save & Continue.

25. Click Apply in the Property window.

26. Click Preview.

27. Enter the first three characters for the Country in the Data Lookup control. The countries whose title contains the specified characters are displayed in the drop-down list.

28. Select the appropriate country from the drop-down list.

29. Click Finish.