Table relationship and lookup problem
In FM Pro 9.0v3 for Mac I have a contract management database comprising a main table. Each record contains information on a single contract and a unique contract serial number for each is generated upon entry. Upon initial data entry each contract is to be assigned a contract manager. Each manager handles multiple contracts. The contract manager names and much related information are located in another table, the contract manager table. Each time a new contract manager is introduced, his/her information is generated as a new record in the contract manager table, and assigned a unique manager ID serial number.
When adding records to the main contract table I want to look up manager names in the contract manager table and enter them via a lookup (or other means) of contract manager names sorted in alphabetical order. Additionally, for each contract in the main table I want to enter the name of the previous contract manager as well as the name of the current contract manager. The previous contract manager would be selected from the same contract manager table.
In the main table I have fields for current manager ID, current manager name, previous manager ID, and previous manager name.
I am having a mental block on how to join these tables to achieve what I want and need help with the logic/design/setup.
I should add that the main table has other relationships with other tables.
All suggestions gratefully received.