Create Dependent Lookup from Database Tables

AVEVA Work Tasks allows its users to create dependent lookups, i.e. the second lookup will display the values based on the value selected from the first lookup. This functionality is very important in forms and list.

In this scenario, you will learn how to create a dependent lookup with the help of two tables; Country and State. The Country table consists of two columns, CountryID and CountryName. The State table consists of three columns, StateID, StateName, and CountryID.

We will then create two lookups, Country Lookup and State Lookup. The Country lookup is similar to the one we have created in the scenario, Create a Lookup for a Database Table.

In this scenario, we will use the 'where' condition in the query builder while creating the State lookup.

To create a dependent lookup from database tables

1. Create a lookup with the name State with the details as mentioned in the field description table.

   Field	Description
   Database Connection	Select appropriate database connection from the drop-down list. For more details on how to create the database connection, refer to the scenario, Create a Lookup for a Database Table.
   Query	Enter the query to execute and display the lookup input control. Data will be retrieved based on the query from the database. Here, you need to specify the 'Where' condition also. Then the value will be retrieved based on the where condition. Example: SELECT * from State where CountryId=@CountryId
   Value	Enter the column name to be used as value field in the lookup input control. Example: State.StateId Note:  - If required, you can enter multiple column names separated by commas. - 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: StateName

2. Create another lookup with the name Country with the details as mentioned in the field description table.

To design a form

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

3. Drag and drop the Data Lookup control in to the form.

4. Edit the properties for the Data Lookup control.

5. Click the Lookup Source icon in the Data Source field. The Lookup Source Configuration page appears.

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

7. Select Country in the Data Source.

8. Click Save & Continue.

9. Click Apply in the Property window.

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

11. Edit the properties for the Data Lookup control.

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

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

14. Select State in Data Source.

    The Parameters field is displayed.

    Note: In the case of parameterized lookups, when the parent lookup selection is changed the value selected in the child lookup is not getting cleared off.
    In this scenario, consider that the user selects a Country and then selects a State for that Country. When the user changes the Country selection, the State lookup value remains the same. The value selected in the State lookup is not being cleared off.

15. For the parameter @CountryId, select the first Data Lookup control from the drop-down list.

16. Click Save & Continue.

17. Click Apply in the Property window.

18. Click Preview.

19. Enter the first three characters for the Country in the Data Lookup control. In the drop-down list, the countries with the title containing the three characters are displayed.

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

21. Enter the first three characters for the State in the Data Lookup control. In the drop-down list, the states with their title containing the three characters and belonging to the selected country in the Country Data Lookup control, are displayed.

22. Select the appropriate state from the drop-down list.