View Article

Introduction

If you join a table (or tables) to the main query, the application searches that table every time the user executes a search, whether the user enters criteria from that table or not. You need a main query join if you want to enable the user to search for records from the main query's table that also have matching attributes from another table.

You can also join related tables with sub queries (different process: see Add a Sub Query). Main queries are for when you always want to query the related table no matter what criteria the user enters; or if you want to view the returned values in the search results.

If the form searches more than one table, you may need to display the values returned from the non-form tables. The only way you can do this is by adding the columns to the search results. You can add a property for this in the Properties section.

Prerequisites

• The form you are editing must already exist. 
• The main query with the join must already exist in the entity.

Detailed Steps

Step 1. Open the form configuration.

Step 2. Click the Edit icon in the Main/Sub Query Details section.

Step 3. Click the Browse Base Query icon to add the main query.

The Browse Queries window lets you browse for and enter the query.

Tip: You can also add this query manually.

Step 4. Select the main query.

The query comes from the entity and must have its joined columns mapped. If you have not yet mapped the columns you want to use on the form, do so now.

The child table properties are now available in the form in the entity attributes.

Step 5. Save the query details in the Main/Sub Query Details section.

You must save the query in the Main/Sub Query Details section for Xelence to update the form with the new query.

Step 6. Add controls for searching the joined table.

The controls are available for either the user's use or further configuration.

Step 7. Move the controls, if necessary.

You'll also need to delete extra rows; every separate control type you drop appears in a new row.

Step 8. Add the columns to the data table.

The data table columns come from an object with a one to one relationship with the entity.

To add the columns in the data table, right click the data table and click Add columns. Xelence displays the Add Column wizard. Complete the wizard and click OK.

Step 9. Optionally, add the Query tab.

The Query tab allows the user to see the executed query.

These are all going to be restricted to certain users. The Query tab is really for developers to make sure queries are executing as intended.

Step 10. 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.

If you have added the Query tab, you can confirm the query includes the join.

What are the improvements over S3 Version 6?

Related Articles

Create a Lookup Form

Add a Sub Query

This post is part of the Lookup Form topic. Click here to open the Lookup Form Overview.

#Xelence
#Forms