Panorama > Procedures > Database
 
findrelated
findrelated
DATABASE
,
OPTION
,
VALUE

The findrelated statement finds information that matches the current record in a related database.

Parameters

This statement has three parameters:

database – name of the database.

option – additional option, or dictionary containing options.

value – value of additional option.

Description

This statement finds information that matches the current record in a related database. The recommended practice when using this procedure is to use the Database Options>Relations dialog sheet to set up a relationship between two databases (see Relational Database Management). Once this is done, this statement can be used simply by specifying the related database name as the parameter to this statement.

findrelated database

For example, suppose you have a Vendors database and a Checkbook database, and a relation has been set up between the PayTo field in the Checkbook and the Vendor field in the Vendor database (see Relational Database Management).

When viewing the Checkbook database, the code example below will switch to the Vendors database and find the vendor associated with the current check (this code must be placed in a procedure in the Checkbook database).

findrelated "Vendors"

Specifying the Form or Window in the Related Database

If there is currently more than one window open associated with the related database, the findrelated statement will normally bring forward the topmost window associated with that database. If you wish, you can specify a specific form or window to bring forward.

To bring forward a specific form, use the "FORM" option. This example code will bring forward the Check form. If that form isn’t already open, it will be opened.

findrelated "Checkbook","FORM","Check"

To bring forward the data sheet, use the "DATASHEET" option, like this. If the data sheet isn’t already open, it will be opened for you.

findrelated "Checkbook","DATASHEET","YES"

To bring forward a specific window, use the "WINDOW" option, like this. The specified window must already be open. You can specify the window by name, or by number (see Window ID Numbers).

findrelated "Checkbook","WINDOW","Checkbook:Report"

Specifying Additional Relation Options

If needed, you can customize one or more relation options, overriding the options set up in the Database Options>Relations dialog. For example, this code will find checks that match the current vendor, but only if the checks were written in the last 180 days.

findrelated "Checkbook","SOURCE SUBSET",{CheckDate > today()-180}

A frequently used option with this statement is "DUPLICATE SOURCE KEY". This option lets you specify what to do if there is more than one record in the source database that matches the current record. The possible options are first, which finds the first matching record (closest to the top), last, which finds the last matching record, or error, which causes an error in this situation (your code can trap the error and perform the appropriate operation, perhaps using the selectrelated statement to select all of the matching records).

Given the example Vendors database seen earlier, this code could be used to find the most recent check written to the current vendor (assuming that the checkbook is sorted in chronological order).

findrelated "Checkbook","Duplicate Source Key","last"

Keep in mind that the template set up with the Database Options>Relations dialog sheet can also specify how to handle duplicate matches. But if your code uses this option, the setting in your code will override the setting from the template.

See the join statement to learn more about the relation options that are available.

See Also

History

VersionStatusNotes
10.2NewNew in this version.