Introduction
A sub query executes when a user submits criteria from the sub query's table. Otherwise, it does not execute. You need a sub query if you want to enable users to search for records from the main query table that also have matching attributes from another table. For example: customers from a certain city.
You can also join related tables in the main query (different process: see Add a Query with a Join and Map the Columns). Sub queries are for when you only want to query the related table when the user searches for those specific table columns, generally because you want to improve performance (execute the search query faster); or if it's not necessary to see the table values in the search results.
Prerequisites
- The form you need to edit must already exist.
- The sub query must already exist.
- The main query must be correctly configured in the entity.
|
Quick Steps |
| 1 |
Open the form's Configuration tab |
| 2 |
Enter the Main Query |
| 3 |
Add a sub query |
| 4 |
Enter the sub query |
| 5 |
Save the query |
| 6 |
Add controls for searching the sub query's table |
| 7 |
Edit the control properties, if desired |
| 8 |
Save the form |
Detailed Steps
Step 1. Open the form's Configuration tab.
Step 2. Enter the Main Query
Click the Edit icon, then, enter the query you want to use as the main query. It has to exist on the entity before you can use it here.
Step 3. Add a sub query.
Click the Add Query icon. The Browse Queries window lets you browse for and enter only those queries whose Query Type is SubSelectQuery.
Step 4. Enter the sub query.
This query comes from the entity. The sub query properties are now available in the entity attributes.
Tip: Select the table name, then the query name. Click OK.
Step 5. Save the query.
Step 6. Add controls for searching the sub query's table.
The joined table is now available in the entity attributes. Open it to select the columns. Also, the Query tab gets added to the Criteria section.
Step 7. Edit the control properties, if desired.
Any control for a column found in more than one table requires notation to specify which table it's from.
This displays the Picklist ID from the Code table that stores the values from this category of data. If the ID isn't available in the table, you can't use it here.
Step 8. Save the form.
Then, run the Preview. Xelence displays the form output. You can test your updated settings.
When you have previewed your form, it should look something like this. The joined table's control displays on the form.
If you search with the joined values and the Query tab is part of the Criteria section, you can check the executed query to see the exists clause.
Related Articles
Create a Lookup Form
This post is part of the Lookup Form topic. Click here to open the Lookup Form Overview.
This post is also part of Controls and Buttons topic. Click here to open Forms Toolbox Controls
#Xelence
#Forms
#ControlsandButtons