5 Replies Latest reply on May 30, 2012 7:13 AM by rhlilienkamp

    Need help with Multi level relationships


      I am new to filemaker so please go easy.


      Here is the scenario:


      We have a family of clients that makes investments in a series of projects. Sometimes these clients invest directly and sometimes they create a company to invest in the projects and most of the time it is some combination of both.


      There are several occasions where my clients have companies that invest in other companies that make investments and the companies can have several owners.


      Ideally we want to maintain a list of individuals and companies and their direct ownership (one level down) whether it be another company or individual.


      We would like to use that list to determine who actually owns what at the individual level or the company level. These can have 4 or more levels of ownership e.g. Project A is owned by CompanyC ->Which is owned CompanyB -> which is owned by CompanyA -> which is owned by a group of Individuals. The companies and indivduals can be on any level.


      Is there a good way to do this in filemaker with the unknown number of and varying levels of ownership.


      I hope this makes sense.


      Thanks for you help,


      Using FM 12

        • 1. Re: Need help with Multi level relationships

          Hi, Scotty. Welcome to FileMaker.


          This kind of situation is really a data modeling problem moreso than a FileMaker problem. In other words, how do I set up my data tables and fields to track what I want? (And that's independent of technology, for the most part.)


          Given that you have an unknown number of levels here, what I would suggest is that you model this so that Project = Company = Group of Individual. (Huh?) In other words, you only have one table. That table contains the necessary fields to track a given entity, whether that entity is a project, or a company, or a group of individuals. Then, you define your relationship as what we call a self-join. The table has a primary key and a foreign key, and you join a record to another record in the same table.


          That way, I can take record A (which is a Project), and join it to record B (which is a company), which is joined to record C (which is another company), which is joined to record D (which is a group of individuals).


          Makes sense?


          Another way to do it would be to use what we call a "multi-key". This is when you use a return-delimited list as your key field, which allows you to have a many-to-many relationship, but I tend not to recommend such a setup to newcomers, because they can be a little dicey to manage. Someone else may disagree, or have another suggestion.



          • 2. Re: Need help with Multi level relationships

            Scotty may need to invest heavily,


            Its a data model problem you'll have win any DB. Not peculiar to FMP.


            First I suggest using a join table. Thats a table with only related ID's used to related People to Companies and People to Investments and Companies to Investments.


            For example:

            People          <-> Join <->     Investments



            Companies     <-> Join <->     Investments


            When entering Investments from the People table you enter the People ID and the Investment ID into the Join table, thru a portal to the join table that shows the Investment ID (for entry) and its description in a Value list.


            Same thing when entering Investments tied to Companies.


            Now a relationship of


            People          <-> Join <->     Companies     <-> Join <->     Investments


            Will show all the related Company Investments.


            One way to simplify the structure is to always use a Company for all investment. Even if it is a Person make them also a Company. Then you will always have a related Company for every Investment and the link above will show all related Investments for a People record.


            I hope that helps (HTH). If not then you probably need to learn more about the FMP terminology to understand it. I'd suggest investing in a training class or the FileMaker Training Series.

            • 3. Re: Need help with Multi level relationships

              The way to approach the situation where BOTH contacts (persons) AND Companies can be clients for a project/investment/quote/whatever is to create a CLIENTS table as a join table.












              source_id (  = person_id or company_id)

              client_type_code ( psn, cmp)








              IF a project has multiple clients, then a ClientLines table is required as child of Projects and therefore:










              Invoices and payments etc are recorded against the client_id, and again can be structured for payments from different clients...



              When adding a source ( person/company) to a project, have the script check if the source_id exists in the client table, and conditionaly create a new record if not, parse the client_id, and add as a record to the project (if single client) or ClientLines table.



              This can also be built to handle parent organisation ( investment groups, trusts...) and subsiduary companies, the company becomes the client.


              Contacts can be associated with multiple companies via a ComanyContacts table...

              • 4. Re: Need help with Multi level relationships

                Sorry for the delay in answering this.  I felt thet the best approch to your problem was the Party Model.  I am still in the learning phase of this data model.  I found a sample file by Daniel Shanahan of New Leaf Data.  This file and a pdf document can be found at http://www.newleafdata.com/downloads.php  I used a clone of this file to make the attached file.  The Party Model has People & Organizations, I changed this to People, Companies and Projects.  This required 2 new tables, value list change, calculaton changes and script changes.  I also added a field Ownership to the Role table to allow the recording the ownership, i.e., te number of shares or percent owned.


                The reations between people and companies are always people are owners and compaines are assets.    The reations between 2 companies are one is the owner and the other is an asset.  The reations between people and project are always people are owners and projects are assets.  The reations between companies and project are always companies are owners and projects are assets.


                I think this should meet your requirements.


                • 5. Re: Need help with Multi level relationships

                  Have any of these answerwd helped you?