Often, data from multiple tables gets combined into one, by doing a join. Conceptually, this is done by taking all possible combinations of rows (the "cross-product"), and then filtering out everything except the answer.
To begin each join a comma should end the previous statement before beginning the next one. DO NOT add a comma to the end of the last statement as this will result in an error.
EACH
FOR is only used for the first table in the query, all subsequent tables must be accessed with EACH to start an iterating query that will find a single record on each pass.
To establish a join, the table(s) you are adding to the query must have some relation to one or more tables already in the query.
Example Query on the coins database to retrieve all costheads (jc_costcode) that belong to contracts (jc_job) that belong to the logged in Company
FOR EACH jc_job WHERE jc_job.kco = {kco},
EACH jc_costcode WHERE jc_costcode.kco = jc_job.kco
AND jc_costcode.job_num = jc_job.job_numIf you do not use the EACH keyword for a subsequent table then you must use one of the following to obtain a single record:
FIRST
Uses the criteria in the record-phrase to find the first record in the table that meets that criterion.
Progress finds the first record before any sorting.
LAST
Uses the criteria in the record-phrase to find the last record in the table that meets that criterion.
Progress finds the last record before sorting.
The FIRST and LAST keywords are especially useful when you are sorting records in a table in which you want to display information. Often, several related records exist in a related table, but you only want to display the first or last related record from that table in the sort. You can use FIRST or LAST in these cases.
OF
Some of the tables in the Coins ERP+ database share a relationship based on common field names between record and table that also participate in a UNIQUE index for either record or table. All OF relationships within the coins database are detailed in the database enquiry and appear for each table in the form similar to:
Where such a relationship exits, the OF statement may be used to relate one table to another. So in our earlier example we used the query:
FOR EACH jc_job WHERE jc_job.kco = {kco},
EACH jc_costcode WHERE jc_costcode.kco = jc_job.kco
AND jc_costcode.job_num = jc_job.job_numAn OF relationship exists between jc_job and jc_costcode as can be seen in the database enquiry for jc_job:
So we can re-write this query as:
FOR EACH jc_job WHERE jc_job.kco = {kco},
EACH jc_costcode OF jc_job