Am trying to create dynamic Value Lists depending upon the existing data. Have two tables that are related on an = basis. The 2 tables are:
- Order.Locations (primary key Order.ID)
- Congregation.Locations (primary key Congregation.ID, but also has Order.ID as an indexed field which is the join to Order.Locations)
Every Congregation within the Congregation.Locations table can have one and only one Order.ID attached to it. At the point of data entry, the Congregation's Order may not be known, so can't make Order.ID a required field on the Congregation.Locations table.
Finding Order.ID's without an associated Congregation.ID is simple. However, when adding a Congregation to an Order, want the Value List to only show Congregations without an Order.ID
Record-1: Order.ID=1 Congregation.ID=2
Record-2: Order.ID=1 Congregation.ID=3
Record-3: Order.ID=(null) Congregation.ID=4
Record-4: Order.ID=(null) Congregation.ID=5
Record-5: Order.ID=2 Congregation.ID=6
So after determining Order.ID Record-C has no Congregation.Location::Congregation.ID associated with it, I want to call up that Order.ID and only show the Value List of Congregation.ID's of Record-3 and Record-4 (those Congregation.Location records without an Order.ID).
Please let me know what questions you have. Thanks in advance for your helpful suggestions!