ad hoc relationship

Type

Concept

Purpose

Like a predefined relationship, an ad hoc relationship is a relationship between two sets of records, but an ad hoc relationship is created as a script is processed instead of being specified on the Relationships form during the Form Definition process. An ad hoc relationship lets you easily access records in another table while processing a procedure. However, because the ad hoc relationship isn't stored as a part of the database, it must be redefined in each procedure.

An ad hoc relationship is created by combining a relational (or relational statistical) operator with the name of an unrelated table, or the name of a predefined relationship to which new selection criteria are added.

When you create an ad hoc relationship, the named operator is used to give the set of related records a unique name. This lets DataEase identify each distinct set of records selected from a given table.

Unlike predefined relationships, ad hoc relationships can also include comparisions based on a rage, such as ?between Current Date ? 60 to Current Date?, as well as fields where the values are not equal.

Syntax

relational operator TABLENAME | RELATIONSHIP

[named "UNIQUE RELATIONSHIP NAME" ]

[with ( selection criteria) ] FIELDNAME;|.

Usage

Once an ad hoc relationship is created with certain selection criteria, the criteria cannot be changed during the remainder of the script. If you want to select another set of records, you must create a new ad hoc relationship and give it a unique name using the named operator. The FIELDNAME is required for all relational and relational statistical operators except count of.

Example

for MEMBERS with TOTALDUE > 100 ;

list records

LASTNAME in order ;

sum of RESERVATIONS named "SPRING" with

( RESERVATION DATE between 03/21/95 to 06/20/95 ) TOTALDUE.

end

Since there is a predefined relationship between MEMBERS and RESERVATIONS based on the MEMBER ID field, the statement:

sum of RESERVATIONS named "SPRING" with

 ( RESERVATION DATE between 03/21/95 to 06/20/95 )     TOTALDUE .

..creates an ad hoc relationship by adding the additional RESERVATION DATE selection criteria to the existing predefined relationship.

This script tells DataEase: (1) Select all the MEMBERS records that have a TOTAL DUE greater than $100, (2) find all these members' reservations in the related RESERVATIONS table that are dated between March 21 and June 20, 1995, and (3) for each record selected from the MEMBERS table, list the member's last name and the total cost of that member's Spring reservations.

If the predefined relationship used a custom relationship name and the script specified the tablename instead of the custom relationship name, it would be necessary to restate the predefined relationship criteria. For example, the statement above would read:

  sum of RESERVATIONS named "SPRING" with

( MEMBERID = MEMBERS MEMBER ID and

RESERVATIONDATE between 03/21/95 to 06/20/95 )

TOTALDUE.

If we also want DataEase to list the sum of these members' Summer reservations, we have to create a new ad hoc relationship and give it a unique name as shown below:

for MEMBERS with TOTALDUE > 100 ;

list records

LASTNAME in order ;

sum of RESERVATIONS named " SPRING " with

( RESERVATIONDATE between 03/21/95 to 06/20/95 ) TOTALDUE ;

sum of RESERVATIONS named " SUMMER " with

( RESERVATIONDATE between 06/21/95 to 09/20/95 )

TOTAL DUE .

end