Create a Lookup for a Database Table with Composite Primary Key

The Database lookup supports creating lookups for tables which have more than one column defined as the primary key.   

Consider a Job-Steps scenario from a typical manufacturing process. A work order is a collection of jobs that produce an item. A job is a list of steps or procedures that are executed to produce an item or version of item. 

Let us say, the work order details are stored in the Work_Order table with wo_id as the work order id. Jobs involved for producing an item are stored in the Job table with oper_id as the job id. The various steps that make up one specific job are stored in the Job_Steps table. To uniquely identify any row in the Job_Steps table we need wo_id, oper_id, seq_no and step_no columns. 

In this scenario, we will create a lookup for Jobs and a lookup for Job Steps. The user selects a Job from the Job Lookup. Job Step lookup gives the list of steps involved in completion of the selected Job.  

Before Starting with the scenario, create a database connection. The table schemas for the tables involved are as follows:

Job Table Schema

Job_Step Table Schema

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 MESWorkOrderConnection.

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 icon and then select the appropriate server that you are using, from the drop-down list. In this case, you will select Server1\SQL2008STANDARD.
   Authentication Type	Select the appropriate authentication type from the drop-down list. Since, you are using SQL Server for authentication, select SQL Server 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.

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 in the Connection List page.

   Now, you have created a database connection.

   Next, you have to create a database lookup for Job and Job Steps. 

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. In this case, enter JobLookup.

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 MESWorkOrderConnection, 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 job
Value	Enter the column names to be used as value field in the lookup input control. Example: job.wo_id, job.oper_id, job.seq_no Note: The column specified in the Value field 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: job_desc.

8. Click Validate & Preview. If the validation is successful and the preview has been generated, click Save. The details are saved and the Lookup List page appears.

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

Next, you need to add another lookup for Job_Steps table.

9. Follow the steps above for creating a Database lookup. Enter the details for creating the lookup.

10. Click Validate & Preview. The Preview dialog box for Resolve Parameter appears. Enter valid sample inputs for the parameters and then click Proceed.

The Preview screen appears with the preview of the data fetched by the database lookup.

11. Click Close.

12. Click Save. The details are saved and the lookups are listed in the Lookup List page.

13. Design a new form.

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

17. Edit the properties for the Data Lookup control.

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

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

20. Select JobLookup in the Data Source.

21. Click Save & Continue.

22. Click Apply in the Property window.

23. Drag and drop another Data Lookup control in to the form.

24. Edit properties for the Data Lookup control.

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

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

27. Select JobStepLookup in the Data Source.

    Note: Since, you have set the 'where' condition for the JobStepLookup, the Parameters field is displayed in the Lookup.

28. Click Save & Continue.

29. Click Apply in the Property window.

30. Click in the text box next to the Controls drop-down box.

31. Select wo_id from the tree structure. Also set the other two parameters using values from the SKRootDefinition of JobsLookup.

32. Click Submit.

33. In the Lookup Input Property window, click Save. The JobStepLookup control is added to the form.

34. Click Preview to preview the form.

35. Click the Search button corresponding to the Jobs Lookup field. Select any job by double-click or by the Select button.

36. Click the Search button corresponding to the Job Steps Lookup field. It displays the list of job steps involved in the selected job.  

37. Select a Job Step.

    Note: Multiple values set in the JobStepsLookup allow you to select a Job Step.

38. Click Finish.