How can I join tables and read values to collection controls?
Collection Controls – INNER JOIN-SQL Tables
The INNER JOIN selects all rows from both tables as long as there is a match between the columns specified in the relationship tables. In the below example, we will be joining two SQL Tables namely, CustomerDetails & CustOrderDetails in order to list out all the orders completed by a particular customer.
Below are the steps to achieve that.
Step 1: Create a form as shown below
Drag and drop a ComboBox control from the group ‘Standard Controls’.
Design a form as shown below that contains a ComboBox control.
The below shown form is designed to view the order details of a customer.
The drop down named ‘cmbOrderNo’ has to be configured such that the drop down list outs all the Orders No’s placed by the logged on customer .
Further steps to configure the drop down has been explained in Step 2.
Step 2: Configuring the drop down ‘cmbOrderNo’ with SQL table
Select the ComboBox control in the designed form.
Quick menu gets open up next to the ComboBox control upon selection.
Click on the link ‘Create New DataSource’ from QuickMenu.
4. A new window ‘DataSource’ gets open up upon clicking the link ‘New DataSource Configuration’.
5. Select the source type as ‘MS SQL/SQL Azure’ for configuring the control with SQL.
6.Click on button ‘OK’ to proceed with the configuration.
7. Another window gets open up to select the data source.
8.Select the data source to which the control has to be configured from the data sources listed in the window.
9. Click on button ‘Next’ to proceed with the configuration.
Step 3: Select two SQL Tables from the available listed Tables (CustomerDetails & CustOrderDetails).
Click on the button ‘Next’ to proceed with the configuration upon selecting the two tables from the window.
Step 4: Select the criteria
Select the criteria based on which the drop down has to be filled.
Here ID of the user logged in is given as the criteria to fill the drop down.
Click on button ‘Next’ to proceed with the configuration.
4. Set a column in the table as ’Value Member’ another column in the table as ‘Display Member’
5. A ‘Both’ option is also available to set a same column as both the’ Value Member’ and ‘Display Member’.
6. Click on ‘Next’ button to proceed with the wizard.
Step 5: Configure the ‘Relation’ wizard
Select the primary table and the column name from the respective drop downs for which inner join is performed.
Select the child table and the column name that has to be mapped with the column in primary table from the respective drop downs.
Here in this context, the ’CustID’ column from parent table ‘CustomerDetails’ is mapped with the column ‘ID’ from Child table ‘CustOrderDetails’.
The data type of both the columns should be of type numeric.
Note: The INNER JOIN selects all rows from both tables as long as there is a match between the columns specified in the relationship tables.
5. Click on button ‘Add’ so that the selected relationship among tables get added to the wizard.
6. The user can view all the relationships added among tables in the wizard.
7. In order to delete an added relationship among tables, click on the button .
8. Click on button ‘Next’ to proceed with the configuration.
Step 6: Set a sort order and add trigger
Select the column that has to be sorted from the drop down. Also select the order by which the column has to be sorted from the drop down.
Add a trigger to populate the ComboBox control using the button ‘Add Trigger’. Here the hyper link button, ’hlEditOrder’ is set as the trigger to populate the ComboBox control.
Click on button ‘Finish’ to complete the configuration. The pop up gets closed automatically.
Step 7: FormPreview
Upon viewing preview, you shall get the output like the one below, in which the two Tables are joined together based on the ID.
Click on the hyper link ‘Edit Orders’ which acts as a trigger to populate the drop down.
The drop down gets populated with result on inner joining two tables.