Hi Mark,
Thanks for the suggestion. I think that would work if my one-to-many relationship was set up in the other direction, that is if Rental Agreement Types had many Buildings. In that case I would only have 3 records in my Rental Agreement Types table, and I could pick the record ID related to "Annual"
However, the relationship I have is: Buildings have many Rental Agreement Types
The data looks more like this:
Building A
- Rental Type "Annual" with conditions XYZ, current - > Record ID 1
- Rental Type "Month to month" with conditions ABC, current - > Record ID 2
- Rental Type "Annual with conditions XY, not current - > Record ID 3
Building B
- Rental Type "Annual" with conditions DEF, current - > Record ID 4
- Rental Type "Month to month" with conditions CGY, current -> Record ID 5
So it's not enough to look at the "key/record ID" field because Buildings have many Rental Agreement Types rather than Rental Agreement Types having many Buildings.
The form already knows the related Building, so there should be some way for it to know the related Type of Rental Agreement for the conditions of "Annual" and "Current" - but I'm starting from the Tenant table creating a record on the related Rental Agreement table, so they're more distantly connected.
------------------------------
Kim Gardner
------------------------------
