This is a public Forum  publicRSS

Forum post

    Bubba
    Table relationship and lookup problemAnswered
    Forum post posted March 25, 2009 by Bubba , last edited February 9, 2012
    239 Views, 2 Comments
    Title:
    Table relationship and lookup problem
    Post:

    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.

    Best Answer

    PhilModJunk

    Bubba,

     

    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:

    Answer

     

    • PhilModJunk

      Bubba,

       

      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:

    • Bubba

      Yes.  :smileyhappy: Thanks much.

       

      Bubba