English

How To: 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