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).
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.
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.
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.
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...
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.
Have any of these answerwd helped you?