2 Replies Latest reply on Apr 23, 2012 12:08 PM by tonymaycpa

    Multi-layer relationship - Don't want to reinvent the wheel


      I am just beginning to create a database but the main thrust of the "customer's" request is a stumbling block at the moment.


      Our Government Contract Administrator wants to keep track of our government contracts.


      For a given contract there can be many CLINs (contract line items) (one-to-many). That's the easy part.


      The government will incrementally fund each CLIN which means there can be many fundings for each CLIN. (one-to-many again).


      The database desing part is easy enough but I not accustomed with FileMaker enough to easily figure out how to allow the user to first creat a contract, then the multiple CLINs, and then the funding for each CLIN. Initially there will only be one funding for each CLIN so entering the funding as you enter each CLIN may make more sense for the data entry staff.


      Later when the second funding comes in the data entry staff will have to find the specific CLIN and add an additional funding record.


      One of the reports that will be requested by management will be what is the total value of all of the CLINs on a given contract and what is the total amount funded on a given contract. Still trying to grasp the concept of forms and reports (Access minded) to a combined Layout function that accomplishes either/both tasks.


      I have played around with having a portal on top of a portal but struggling. If anyone has seen or done this type of structure I would greatly appreciate any information. I can extropolate very well so if it is just close I can take that and run.


      I have years of experience with creating database in Access but I have not had to do a multi-layer relationship like this though I would probably have created a Contract Details screen that lists all of the CLINs and then have a link on each CLIN line that would pop up a modal dialog screen to enter the funding for that CLIN. Kind of annoying for the user to bring up a screen for the funding, close it, go to the next CLIN, click the link to bring up the screen again but for the next CLIN.


      Thanks in advance.



        • 1. Re: Multi-layer relationship - Don't want to reinvent the wheel



          Say you have three tables: Contract, CLIN and Funding.


          Each contract may have many CLINs, each CLIN may have many funding entries.


          The CLIN table has a foreign key to the contract table and the funding table has a foreign key to the CLIN table. It may also be useful if the funding table has a foreign key to the contract table. (You can implement this using a reference field in the funding table which references the foreign key in the CLIN table, that is, the foreign key to the contract. )


          Connect the three tables using two relationships in a line from left to right:


          (If you wish to be able to create a new record directly within the portal make them create relationships.)


          Each table has two associated layouts: a list layout and a detail layout.


          In the list layout there should be a button to switch to the details layout.

          In the details layout there should be a portal to the next level down.

          In the portal there should be a button to jump to the next level down.

          This button should execute a "go to related records" command to jump to the list layout of the next level down. You may wish to add a second button to jump directly to the details layout of the next level down. Whichever you choose, it should be possible in the lower level to browse through the records without having to go back to the upper level.

          The lower levels of course need a button to return to the upper levels.


          This form of hierarchical relationship is very easy for the user to understand.


          The beauty of FileMaker is that you are also able to place a button on the contracts layout which jumps directly to the funding list layout, selecting all of the funding entries which belong to this contract.


          I hope this helps and good luck with the implementation!



          • 2. Re: Multi-layer relationship - Don't want to reinvent the wheel

            Thank you very much for the information.  Conceptually this is where I was going but you have given me specific implementation guidance (multiple layouts, buttons going up and down a level).  This will shave considerable time off of my trial and error.


            I can see the use of making the Contract ID a foreign field in the Funding table.  This would seem to facilitate more easily getting a total of funding on a contract level without the need to go through the CLIN table.