Create a Lookup for a List

AVEVA Work Tasks has the provision to create a Database lookup and List lookup to retrieve data. In the Database lookup the data is retrieved from database tables whereas in a List lookup, data is retrieved from List.

In this scenario, you will create a lookup for the Employee Details list. Consider a form which is used for filling in an employee’s annual rating with two fields, Employee and Rating. Employee field will be a drop-down (lookup) only for the active Employees. In this scenario we will try to understand, how to build an active employee lookup.

Before we start with the scenario, create an Employee Details list with three fields Title, Employee Number and Active.

To create a lookup for a list

1. Create an Employee Details list.

2. Run the script mentioned below in the repository database.

   CREATE TABLE [dbo].[EmployeeDetails](

   [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__EmployeeDetails__Id__1E6F845E] DEFAULT (newid()),

   [Title] [nvarchar](250) NOT NULL,

   [EmployeeNumber] [nvarchar](150) NULL,

   [Active] [bit] NULL,

   [Application] [nvarchar](150) NULL,

   [CreatedBy] [uniqueidentifier] NULL,

   [CreatedDateTime] [datetime] NULL,

   [LastUpdatedDateTime] [datetime] NOT NULL,

   [WIP] [bit] NULL,

   [WIPItemId] [uniqueidentifier] NULL,

   [MainItemId] [uniqueidentifier] NULL,

   [ParentItemId] [uniqueidentifier] NULL,

   [RecordParentItemId] [uniqueidentifier] NULL,

   [TableId] [uniqueidentifier] NULL,

   [ItemRowIndex] [int] NULL,

   [IsLatest] [bit] NULL,

   [Version] [nvarchar](25) NULL,

   [VersionHistory] [nvarchar](max) NULL,

   [Status] [int] NULL,

   [LockedBy] [uniqueidentifier] NULL,

   [LockedOn] [datetime] NULL,

   [ListID] [uniqueidentifier] NULL,

   [SecuritySettings] [nvarchar](max) NULL,

   [ItemId] [uniqueidentifier] NULL,

   [ItemType] [int] NULL,

   [SecurityItemId] [uniqueidentifier] NULL,

   [Owner] [uniqueidentifier] NULL,

   [ModifiedBy] [uniqueidentifier] NULL,

   [AuditTrail] [nvarchar](max) NULL CONSTRAINT [DF_EmployeeDetails_AuditTrail] DEFAULT (''),

   [IsDisabled] [bit] NULL CONSTRAINT [DF_EmployeeDetails_IsDisabled] DEFAULT ((0)),

   [WorkflowStatus] [varchar](50) NULL,

   [SecurityCustomizationId] [uniqueidentifier] NULL,

   CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY NONCLUSTERED

   (

   [Id] ASC

   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

   GO

   Note: Add two additional columns to the standard column as follows:

   [EmployeeNumber] [nvarchar] (150) NULL,

   [Active] [bit] NULL,

3. Make an entry in the SKListTables as mentioned below.

   Insert into SKListTables (Application,TableName)

   Values ('<Repository Name>','EmployeeDetails')

   Go

   Note: Replace <Repository Name> with the repository you are using.

4. Create a new List named, Employee Details for the above table.

5. The Title control is of type TextInput to capture the employee name.

6. The Employee Number control is of type TextInput to capture employee number.

7. The Active control is of type BooleanInput to identify if the employee is active or not.

   Note: For simplicity in this scenario we will use the form auto generated by the list.

8. Input the employee records as follows.

   - John (Active Employee)

   - Tim (Active Employee)

   - Mike ( In Active Employee)

   Note: Our next step would be to create a lookup, which will later be used in a Form.

9. In the Enterprise Console, click menu icon, click Settings, and then click Lookup Settings. The Lookup Settings page appears.

10. Click New Lookup List. The New List Lookup window appears.

11. Type the Lookup Name as "Employees".

12. Type the description to identify the lookup.

13. Select the Employee Details list from the List Name drop-down list.

14. Select the "False" option for the Get all records option.

    Note: Selecting "False" will enable the List Query. In this scenario we have selected "False", since we require active users only. If you select "True" all the employees will be selected.

15. Click More button corresponding to the List Query field. The Dynamic Query Builder dialog box appears.

    Note: Our aim is to build a query to select all employees with column Active with value as "1".

16. Click the Table text box. The tables are displayed in a tree menu.

17. Select "EmployeeDetails".

18. Select "Active" from the Column drop-down list.

    Note: Active is the column used in the WHERE condition.

19. Select "=" from the Operator drop-down list.

20. Select "Value" from the Type drop-down list.

21. Type "1" in the parameter field next to the Type drop-down list.

    Note: This is used to specify the value when it is populated from another field in the form.

22. Click Add. The Dynamic Query Builder dialog box appears.

23. Click Save.

24. Select the No option in the Select distinct records field.

    Note: Next we navigate to the Display Columns section. It is used to select the columns to be displayed in the lookup window.

25. Click the Table text box.

26. Select "EmployeeDetails" from the tree menu. The columns in the EmployeeDetails list is displayed in the Available Columns field.

27. Select Title, EmployeeNumber, Active and click the Add button. These fields are selected in the Selected Columns field.

28. Select "EmployeeDetails.Title" in the Display Column drop-down list.

    Note: It is very important to understand the purpose of the fields, Display Column and Value.

    When the user selects an employee from the Lookup, the Employee name is displayed in the field, but the value that gets stored in the database would be the Employee Number.

    • If you select the Auto option in the Value field, the ID column is stored in the database.

    • If you select the Custom option in the Value field, you can select the column to be stored in the database as the value.

29. Select the "Custom" option in the Value field. A text box and a drop-down list will appear adjacent to the "Custom" option.

30. Click on the Text box and select "EmployeeDetails" from the tree menu.

31. Select "EmployeeDetails.EmployeeNumber" from the drop-down list.

32. Select "Yes" for Display value column in grid option.

33. Click Save. The Employee lookup is created and listed in the Lookup List.

    Note: This lookup can now be used in multiple forms. The following section explains, how the lookup will be used in a Form.

34. In the Enterprise Console Menu, click menu icon, and then click Forms. The Forms page appears.

35. Create a new form named "Employee Rating".

36. Drag and drop the Lookup Input control in the Form Canvas.

37. Edit the control properties, type the name as "Employee".

38. Click the Data Source button corresponding to the Data Source field. The Look up list Explorer dialog box appears.

39. Click the Lookup List text box.

40. Expand the tree menu and select "Employees".

41. Click Submit. The "Employee" lookup is selected in the Look up list Explorer dialog box appears.

42. Click Save. The Forms Designer window will be displayed with the "Employee" Lookup Input control.

43. Click the Preview button. The Employee Rating form is appears in the preview mode.

44. Click the Search button. A popup window of all active employees are displayed.

45. Select the employee "John" from the list and click Select. The name is populated in the form.

46. Click Finish. You can view the generated XML.

The following observations were made in a List lookup scenario where the List lookup is created on a List with child table as shown below.

47. Add countries and states to the list. Also, add a country without adding the respective state.

48. Create a list lookup for the above list with Display Column as "State" and Value Column as "StateId".

49. Consume this lookup in the form's lookup control.

In the lookup which has been rendered as Popup or drop-down, if the user selects a country without a State (child record), the pop up window is not getting closed(popup) or on submit the lookup value is being cleared off(drop-down).