4 Replies Latest reply on Nov 23, 2012 5:20 PM by daverd

    Lookup or Portal?



      Lookup or Portal?



           I am facing an obstacle in creating a db that I need insight to overcome.  The bottom line is implementing a many-to-many relationship.  Any help is appreciated.


           I partner with others in various business investments.  I want to keep track of those investments and partners.  In doing so, there is a many-to-many relationship as one partner an be associated with more than one investment and one investment has more than one partner.


           From a functionality point of view, within the investment record, I want to be able to (1) add a new partner as well as (2) identify already existing partners to the investment.  How do i do that?


           I am willing to forgo requirement #1 of adding a new partner via the investment record and do that all through managing the partner table.  However, I still get stuck with requirement #2. 


           I think I know how I would do it if only one partner was the limit per investment via a lookup.  However, it is the multiple partners that throws me off.  I can't see how I can do a lookup for more than one partner and I don't see how I identify multiple partners from a portal.


           Does anyone have any insight or can point me to a existing template that has a similar scenario?


           I thought that the Invoices example db would be of help where a product on the invoice can be either a new product or an existing product and one has multiple products per invoice, but I was unable to understand how it was setup.


           I am new to developing in Filemaker and consider myself an high intermediate user with Access as I've created dbs (for myself) that have many-to-many relationships.  And as a last point, I admit that I still don't understand the flexibility offered by  Filemaker's multiple table occurrences.  To me, I create one model for all related tables for the db.


           Thanks in advance.


        • 1. Re: Lookup or Portal?

               Many to many relationships almost always require a third table to function as the "join" table to facilitate. Take a look at these relaitonships:


               Investments::__pkInvestmentID = Investment_Partner::_fkInvestmentID
               Partners::__pkPartnerID = Investment_Partner::_fkPartnerID

               If this notation is unfamiliar, see the first message or two posted to this thread: Common Forum Relationship and Field Notations Explained

               With this setup, you can add a portal to Investment_Partner to your Investments layout and use it to list all partners in that investment. Fields from Partners can be included in the portal row to fill in additional info about each investing partner. In like manner, a portal to Investment_Partner, with fields added from Investments can be placed on the Partners layout to list that Partner's investments.

          • 2. Re: Lookup or Portal?

                 Thanks PMJ for both the idea and notation reference.  I am aware of needing an intermediate table to create the many-to-many relationship.  However, following the path of a portal and the notation reference are of value to me as I will now play around with this.

                 In addition, the Registration starter solution with FMP11 have Events and Client forms whose underlying concepts look like I can leverage - in particularly, the UI to the functionality I seek (once I get the underlying schema settled).



            • 3. Re: Lookup or Portal?

                   You may also find this demo file helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              • 4. Re: Lookup or Portal?

                     Thank you PMJ.  Kind of you to share.