Use a data source join

This content relates to the data source Join feature and the Table control. Using these, you can create a data source that extracts a one-to-many relationship, and displays that in a table. For example, a typical scenario is order header to order detail, where a single order number is associated with one or more lines of detail (an ‘order header 1:1M order detail’ relationship).

Note

For this to work, the underlying data structure must support the join you want to make. For example, if the tables are order header and order detail, and you want to join an order number, order number must be in both tables.

Configure a join in a data source

  1. In the Data Sources box, create the data source, and then:
    1. Add the tables that you want to join. We will term these the header and detail tables with a one-to-many relationship.
    2. In each table, add the columns that you need. Make sure that both tables include the columns that will form the join. We will term this column ID.
  2. In the header table, select the ID column.
  3. Click Join.
  4. In the box type:

    dataSource.detail.ID

    replacing each of the three parts to identify the ID column in the detail table. Type ahead will help you find the correct column.

  5. Click Apply.

This creates the join between the header and detail tables. When you use this join, searching on header ID will return all the detail rows with that ID.

Use a join in a Table control

This assumes you are carrying on from the preceding section.

  1. Add a Table control.
  2. In the Table control’s Properties, click next to Table Columns and then, in the Edit Table popup, click Manage Lookups.
    Here we just need one lookup. However, tables can be associated with more than one lookup. For details, see Use lookups with tables.
  3. In the Manage Lookups popup set:
    1. Lookup to a name (this identifies the lookup in the Edit Table popup).
    2. Source Name to dataSource
    3. Database Column to dataSource.header.ID (the column with the join)
    4. Trigger to the Textbox control that will populate the table. For example, an Order Number search control.
      Setting the Default value property in a Textbox control will not trigger the lookup at runtime. The table will only populate when you enter a value in the control and hit Enter.
    5. Click OK to go back to the Edit Table popup. It will now show the name of the lookup you have just added.
  4. In the Edit Table popup, add the columns that you want to appear in the table. Because the table's lookup uses the join, the Database column column will now list the columns made available by the join (and you can use type ahead).

Example

Here are some images from a very basic example. The first shows the data source and the table with the join:

The lookup showing the join:

The column setting showing that it displays a column from the joined table:

At runtime when 3 has been entered in the trigger control:

And finally, the same query executed in SQL Server Management studio:

Article last edited 19 May 2017