selectrecordsrelatedto
DATABASE
,
OPTION
,
VALUE
The selectrecordsrelatedto statement selects records in the current database that are related to any record in another database.
Parameters
This statement has three parameters:
database – name of the related database.
option – additional option, or dictionary containing options.
value – value of additional option.
Description
This statement selects records in the current database that are related to any record in another database. A prerequisite for using this statement is that you must use the Datbase Options>Relations dialog panel to set up a relational link from the current database to the second database. See Relational Database Management to learn how to set up these links.
Once the relations are set up, a simple one line program using the selectrecordsrelatedto statement can be used to select all records that are related. For example, suppose you have a vendor database and a checkbook database, linked by the vendor name. This program could be used in the Vendor database to select all vendors for which one or more checks have been written.
selectrecordsrelatedto "Checkbook"
If you want to select records that are NOT related to any records in the related database, use the REVERSE option, like this.
selectrecordsrelatedto "Checkbook","REVERSE","YES"
This example would select all vendors that haven’t had a single check paid to them.
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.
For example, suppose you have a vendor database and a checkbook database, linked by the vendor name. This program could be used in the Vendor database to select all vendors for which one or more checks have been written. However, if none of the vendors have any checks written to them, the previous selection in the Vendor database will be retained.
selectrecordsrelatedto "Checkbook","MODE","safeselect"
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 only vendors that have been sent checks in the past 180 days.
selectrecordsrelatedto "Checkbook","SOURCESUBSET",{CheckDate > today()-180}
See the join statement to learn more about the relation options that are available. Please note that any additional options you specify must be in all upper case, with no additional spaces or punctuation.
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 a database for a record in a related database that matches the current record in the current database based 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.
- selectrelated -- selects information that matches the current record in a related database.
History
| 10.2 | New | New in this version. |