Export (0) Print
Expand All

Joining Data Sources on Forms

Data sources can be joined together in the following ways:

  • Display data from more than one table, as if it is a single data source.

    For example, fields from more than one table might be displayed in a single grid.

    With this type of join, the two data sources, in many respects, function as a single data source. For example, if you use an inner join and if a user is modifying or adding data to a record in a grid, neither table will be updated until the whole "record" (grid row) is exited rather than a table being updated as the user moves from one data source to another within the row.

  • Display data from more than one table where there is a parent/child relationship between the data sources. The two data sources are kept visually separate.

    For example, order records might be displayed as the parent data source in one grid and order lines as the child data source in another grid.

TipTip

For more information about joining data sources, see the tutorial_Form_Join form.


  1. Add both data sources to the Data Sources node in the form.

  2. Set the JoinSource property on the secondary data source to the name of the main data source.

  3. Set the LinkType property on the secondary data source to InnerJoin, OuterJoin, ExistJoin, or NotExistJoin.

    The LinkType property settings are described later in this topic.

  4. Create the form design, and then display the relevant fields from the form data sources. Typically, you would choose a grid control for your joined data sources.

NoteNote

You also need to write code to update data in shared fields. For example, if there is a relationship between the tables on Table1.Field1 and Table2.Field2, only one of these fields will be displayed in the form (because the fields are identical). When a change is made to Table1.Field1 on the form, however, Table2.Field2 is not automatically updated. You must add code to the form—for example, on the modified method for Table.Field1.


Advantages of Joining Data Sources

It is also possible to display data from other data sources by using the display or edit methods. Following are the advantages to joining data sources:

  • Performance is increased if you join data sources, compared to using the display or edit methods. Selects, inserts, and updates can occur simultaneously in more than one table because a single query is used.

  • Navigation is synchronized in both data sources—when the user selects a new record, both data sources are updated at the same time.

  • Notification methods, such as active, are run for both data sources. Methods that operate on the data source, such as next, executeQuery, and so on, are redirected to the data source that owns the join, the main data source. The init method is run for both and/or all data sources.

Display data from more than one table where there is a parent/child relationship between the data sources, and the two data sources are kept visually separate.

  1. Add both data sources to the Data Sources node in the form.

  2. Set the JoinSource property on the child data source to the parent data source.

  3. Set the LinkType property on the child data source to Active, Passive, or Delayed.

    The LinkType property controls how the child data source is updated in relation to changes in the master data source.

  4. Create the form design, and then display the relevant fields from the form data sources. Typically, you would choose two grid controls—one to display the parent data source and one to display the child data source.

The following table describes the different settings for the LinkType property.

Use Passive, Delayed, and Active when you join data sources that have a parent/child relationship, and InnerJoin, OuterJoin, ExistJoin, and NotExistJoin should be used when joining data sources to appear as one data source.

Setting

How the join is performed

Passive

Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.

Delayed

A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately. For example, the user could be scrolling past several orders without immediately seeing each order lines.

Active

The child data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.

InnerJoin

Selects records from the main table that have matching records in the joined table and vice versa.

There is one record for each match. Records without related records in the other data source are eliminated from the result.

OuterJoin

Selects records from the main table whether they have matching records in the joined table.

ExistJoin

Selects a record from the main table for each matching record in the joined table.

The differences between InnerJoin and ExistJoin are as follows:

  • When the join type is ExistJoin, the search ends after the first match has been found.

  • When the join type is InnerJoin, all matching records are searched for.

NotExistJoin

Select records from the main table that do not have a match in the joined table.

Community Additions

ADD
Show:
© 2015 Microsoft