selectrelated
selectrelated
DATABASE
,
OPTION
,
VALUE

The selectrelated statement selects 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 selects 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.

    selectrelated 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 Vendors database, the code example below will switch to the Checkbook database and select all checks that correspond to the curently active Vendor record (this code must be placed in a procedure in the Vendors database).

    selectrelated "Checkbook"

Note: Without even writing any code, this feature is already built into the Search menu.

### Selection Errors

If the selection can’t be performed, an error will be thrown. For example this could happen if a relationship hasn’t been set up with the specified data, or if there are no records in the specified database that match the current database. The most common way to handle this situation is to use the `if error` statement, like this:

    selectrelated "Checkbook"
    if error
        message info("error")
        return
     endif
     ... continue with successful selection

You could also use the try/catch statements. See Error Handling to learn more about how to handle program errors.

IMPORTANT: Notice that handling a failed related selection is different than when using Panorama’s other selection operations, for example select, selectwithin, etc. Instead of using the info(“empty”) function to check whether or not the selection succeeded, you need to use `if error` (or `try/catch`). The info(“empty”) function does not work with the selectrelated statement.

### Specifying the Form or Window in the Related Database

If there is currently more than one window open associated with the related database, the selectrelated 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.

    selectrelated "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.

    selectrelated "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).

    selectrelated "Checkbook","WINDOW","Checkbook:Report"

IMPORTANT: The selectrelated statement will ALWAYS bring some window in the specified database forward. You cannot use noshow or hide to prevent this. There is no way to use selectrelated while leaving the second database in the background.

### Select Mode

The MODE option allows an alternate selection mode to be used. The available modes are:

- select - normal selection (this is the default if the mode is not specified).
- selectwithin - only select related records that are within the current subset.
- selectadditional - select related records in addition to any current subset.
- safeselect - this is like a normal selection, except that if there are no related records, the previous selection is retained. See safeselect to learn more.
- safeselectwithin - this is the same as selectwithin, except that if there are no related records, the previous selection is retained.

When viewing the Vendors database, the code example below will switch to the Checkbook database and select all checks that correspond to the curently active Vendor record (this code must be placed in a procedure in the Vendors database). However, if there are no checks corresponding to the current Vendor record, the previous selection in the Checkbook database will be retained.

    selectwithin "Checkbook","FORM","Check","MODE","safeselect"

### Undo Select Related

You might think that if you wanted to be able to undo the selectrelated statement, you could just add a startdatabasechange above it like this:

    startdatabasechange "allrecords","Select Related to "+Company
    selectrelated "Checkbook"

However, since Undo can’t be used across multiple databases, the code above won’t work (because selectrelated switches to a different database). If you want to be able to Undo the selectwithin statement, add the UNDOMESSAGE option, like this:

    selectrelated "Checkbook","UNDOMESSAGE","Select Related to "+Company

If the UNDOMESSAGE option is not included, the statement cannot be undone.

### 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 select checks that match the current vendor, but only if the checks written in the last 180 days.

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

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

See Also

- addrelatedrecord -- adds a new record to the related database corresponding to the current database.
- findrelated -- finds information that matches the current record in a related database.
- Import Database -- importing another database into the current database.
- join -- joins data from another database into the current database.
- Join Databases -- joining another database into the current database (relational join).
- joindialog -- opens the standard Join Database dialog.
- joinonerecord -- joins matching data from another database into the current record.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- posttorelated -- posts data in the current record to the corresponding record in a related database.
- related( -- searches for a record in a related database that matches the current record in the current database based on a relational specification (based on one or more key fields or formulas in each database), then uses a formula to return other information from the same record. This is similar to the lookup( function, but instead of using an individual field for the key, a relation is used (see Relational Database Management).
- relatedarray( -- builds an Text Array by scanning a database and creating an array element for every record that matches a relational specification (based on one or more key fields or formulas). This is similar to the arraybuild( function, but instead of using an individual field for the key, a relation is used (see Relational Database Management).
- relatedrecordid( -- returns the record id of a record in a related database that matches the current record in the current database based on a relational specification.
- Relational Database Management -- linking together multiple databases based on common data.
- Relational Workshop -- tool that assists in composing relational lookup(, superlookup(, lookupall( and arraybuild( functions.
- selectrecordsrelatedto -- selects records in the current database that are related to any record in another database.

History

Version | Status | Notes
10.2 | New | New in this version.