Use a relate within an AML cursor

Summary

This document will provide an example of how to use a relate within an AML cursor.

Procedure

1. Create two INFO tables containing key items that can be used to relate the tables together. The key item in the following sample tables is ID.
   
   

   Arc: list countrydata
   Record  COUNTRY     ID
        1  USA         10
        2  Canada      20
        3  Mexico      30
   
   Arc: list citydata
   Record   ID CITY
        1   10 Los Angeles
        2   20 Toronto
        3   30 Mexico City
        4   10 New York
        5   20 Ottawa
        6   30 Tijuana
   
   

   
   
   How to create the sample tables:
   
   
   Arc: tables
   
   Tables: define countrydata
   1
   Item Name: country 10 10 c
   11
   Item Name: id 3 3 i
   14
   
   Tables: add
   1
   COUNTRY: USA
   ID: 10
   2
   COUNTRY: Canada
   ID: 20
   3
   COUNTRY: Mexico
   ID: 30
   
   Tables: define citydata
   1
   Item Name: id 3 3 i
   4
   Item Name: city 15 15 c
   19
   
   Tables: add
   1
   ID: 10
   CITY: 'Los Angeles'
   2
   ID: 20
   CITY: Toronto
   3
   ID: 30
   CITY: 'Mexico City'
   4
   ID: 10
   CITY: 'New York'
   5
   ID: 20
   CITY: Ottawa
   6
   ID: 30
   CITY: Tijuana
   
   Tables: quit
   
   
   
2. Create a relate between the two tables.
   
   

   Arc: relate add
   Relation Name: cityrel
   Table Identifier: citydata
   Database Name: info
   INFO Item: id
   Relate Column: id
   Relate Type: linear
   Relate Access: ro

   
   
3. The relationship between COUNTRYDATA and CITYDATA is a one-to-many relationship. Each record in COUNTRYDATA has multiple related records in CITYDATA. The goal of this example will be to list each country and the multiple cities that are related to it.
   
   Copy and paste the following script and save it as an AML file.
   
   /* Usage: CURSOR <cursor> DECLARE <cover> <feature_class> {RO | RW}{logical_expression}
   /* Usage: CURSOR <cursor> DECLARE <info_file> <INFO> {RO | RW} {logical_expression}
   /* Usage: CURSOR <cursor> <OPEN | CLOSE | INSERT | DELETE | REMOVE>
   /* Usage: CURSOR <cursor> <NEXT | FIRST | PREVIOUS | record_number>
   /* Usage: CURSOR <cursor> RELATE <relate> <NEXT | FIRST | INSERT | DELETE>
   /* Declare a cursor to loop through each
   /* record of the COUNTRYDATA info file.
   
   CURSOR cur1 DECLARE countrydata INFO rw
   CURSOR cur1 open
   
   /* The outside DO LOOP will continue to process records until the last record
   /* has been read. When the last COUNTRYDATA record is reached, the variable
   /* %:cur1.AML$NEXT% will be set to FALSE, and the loop will exit.
   
   &do &while %:cur1.AML$NEXT%
   
   /* For the current COUNTRYDATA record being processed
   /* in the outside loop, loop through each of the related
   /* records in the CITYDATA info file. When the last
   /* related record is processed, %:cur1.cityrel//AML$NEXT%
   /* will be FALSE, and the inner DO LOOP will pass control
   /* back to the outer DO LOOP.
   
   &do &while %:cur1.cityrel//AML$NEXT%
   
   /* Display to the command line the current COUNTRY,
   /* ID and related CITY value being processed.
   
   &type %:cur1.country% %:cur1.id% %:cur1.cityrel//city%
   
   CURSOR cur1 RELATE cityrel NEXT
   &end
   
   CURSOR cur1 NEXT
   &end
   
   /* Once all COUNTRYDATA records have been
   /* looped through, close and remove the cursor.
   
   CURSOR cur1 CLOSE
   CURSOR cur1 REMOVE
   
   &return
   
   
4. Run the AML.
   
   Arc: &run sample.aml
   Feature cursor CUR1 now declared using file
   COUNTRYDATA with Read Write access
   Feature cursor CUR1 now opened with
   3 reselected records out of 3
   Fetched record 1 for Feature cursor CUR1
   
   USA 10 Los Angeles
   USA 10 New York
   
   No more related records for relate CITYREL
   Fetched record 2 for Feature cursor CUR1
   
   Canada 20 Toronto
   Canada 20 Ottawa
   
   No more related records for relate CITYREL
   Fetched record 3 for Feature cursor CUR1
   
   Mexico 30 Mexico City
   Mexico 30 Tijuana
   
   No more related records for relate CITYREL
   No more records to fetch for Feature cursor CUR1
   Feature cursor CUR1 now removed