The posttorelated statement posts data in the current record to the corresponding record in a related 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 posts data in the current record to the corresponding record in a related database. If there is no corresponding record, the statement can create a new record. A prerequisite for using this statement is that you must set up relational links in both directions between the databases, using the Datbase Options>Relations dialog panel. 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 posttorelated statement can be used to update a related database with changes made in the current database, as shown in this diagram.
The same program will work to add completely new records to the related database.
Note: If you want the posttorelated statement to add a new record like this, be sure to set the Append Non-matching Data option in the Database Options>Relations dialog panel. Otherwise the statement will simply stop with an error if no match is found. Or, you can build this option right into your code, like this:
posttorelated "HobbyShopCustomers","APPEND","yes"
Specifying the Form or Window in the Related Database
If there is currently more than one window open associated with the related database, the posttorelated 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.
posttorelated "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.
posttorelated "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).
posttorelated "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 post an update to the most recent checks written to the current vendor, but only if the check was written in the last 180 days.
posttorelated "Checkbook","Duplicate Source Key","last","SOURCE SUBSET",{CheckDate > today()-180}
A frequently used option with this statement is "DUPLICATE SOURCE KEY", shown above. 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 post updated information to the most recent check written to the current vendor (assuming that the checkbook is sorted in chronological order).
postrelated "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
- 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.
- 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.
- selectrecordsrelatedto -- selects records in the current database that are related to any record in another database.
- selectrelated -- selects information that matches the current record in a related database.
History
| Version | Status | Notes |
| 10.2 | New | New in this version. |