2 Replies Latest reply on Mar 26, 2009 12:53 PM by Bubba

    Table relationship and lookup problem



      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.

        • 1. Re: Table relationship and lookup problem



          Thanks for the detailed explanation of the issue. It helps a lot when we don't have to guess about different features of your system.


          You need two separate relationships. One for the current manager and one for the second.


          You want your relationships graph to look like this:


          Manager::Manager ID ----= ----Contracts::Manager ID

                                                           Contracts::Manager ID----=----Manager 2:: Previous Manager ID


          Note that Manager and Manager 2 are two "instances" of the same actual table.


          You don't actually need your Manager Name and Previous Manager Name fields.


          Instead, place the fields Manager::Manager Name every where you need the current manager name and Manager 2::Manager Name everywhere you need the previous manager's name.


          That break down the barriers for you? :smileywink:

          • 2. Re: Table relationship and lookup problem

            Yes.  :smileyhappy: Thanks much.