Linking Companies and Individuals

[gareece]

I hope I can get some help on this one before I book myself in for psychiatric treatment! I also hope I am not repeating any questions already asked by others but I couldn't find any threads that relate specifically to what I require.

I have been using a database in FM6 which had two files (among others) called Company and the other called Individual. The Individual records were accessed via a portal in a layout on the Company database. The common identifier was a field called CompanyID. This worked OK but as a very basic relationship it had significant limitations. Almost all of the user interface operated from within the Company Database and as bad design would have it, all Individual records needed also to be added as a 'company' in order to be available for searching and other misc functions.

I am moving the database/s to FM7 and would like to use the opportunity to clean this up but I am having trouble working out how I can set up the relationships between companies and individuals. I intend to 'merge' all the seperate databases to tables unless someone can give me a warning not to do so.

Principally I need to do the following:

1. Link a new or existing individual to a company (ie as an employee, accountant, etc).

2. Link 2 or more companies together

3. Link 2 or more individuals together

I need to be able to display all these records on the same layout.

As an example, I may enter in an individual called Bob Smith. Bob works for ABC Contructions so I also create a company of the same name and link him as an employee. Sounds easy so far! I then need to add Bob's sister, Mary Jones as an individual and link her to Bob. When I search for Bob I need to see 5-6 fields from Mary's record on the same layout as Bob'e main page (like a portal). I'd also like to link Bob's accountant to Bob's record - who is also the accountant for ABC. When I search on ABC I would see Bob and the Accountant as related records to ABC. In the future Bob may engage a new accountant so I would need to 'unlink' him from Bob's record without changing the relationship between the accountant and ABC.

If I could figure out how to setup and define the relationships and display multiple related records from the same table that contains the main record in the same layout then I think I might be very close to getting my FM7 implementation underway.

Thanks in advance.

[The Shadow]

Maybe something like the attached example?

You probably are missing out on the "join table" concept, which makes it easy to have many-to-many relationships.

[gareece]

Thanks Shadow - I am still confused but this definitely takes me closer to figuring it out.

I will study more on the join table idea.

[Fenton]

I think there's a little confusion here, about just what is meant by "related companies" in your original post. It is an interesting problem.

I propose that:

1. Companies related directly to another company, and

2. Companies indirectly related to another company via a connection to one of the company's people is a different thing, relationally anyway.

1. This is what Shadow's file does, via the CompanyJoin table. It also links related people, but ends there.

2. This is what you mean when you say: Bob works for CompanyA. He has an accountant who works for CompanyB. When I look at CompanyA I want to see Bob's accountant and his company (:.

That would be accomplished by another "branch" off of the Related People, to another instance of the Company table.

The amazing thing about version 7 is that, by specifying that Table Occurrence, it can follow the path through the in-between files, and show those "companies related by people" records correctly; which is 4 relationships away from the main Company TO.

I also removed the "Allow creation of related records" from the 2 Related TO's. They're only needed once, to the Join tables. The 2 "Related" TO's are basically "reference" TO's, of the main tables, for reading linked data; you're not creating records in them.

Hopefully this works. It's kind of confusing.

company2.zip

[gareece]

Fenton,

Nice work. I am just starting to get my head around FM7's use of tables as well as developing my comprehension of many-many relationships.

Your help is very much appreciated.

I spent some time on the db and believe that I have a nice working solution. I am using the Join People/Company tables to store relationships. I am also using a script to 'reverse' the id's so that there is a reciprocal relationship. For example if I add Microsoft as a related contact of ABC Constructions I also see ABC Constructions as a related contact of Microsoft.

I am contemplating adding a new field with a pop-up list that describes the nature of the relationship and automatically fills in a reciprocal value for the 'reversed' record (ie. Accountant as one value will automatically fill Client in the 'reverse' record). I need to think this through as to whether it will get messay as the db grows and how much control I want to give the user.

I am avoiding duplicating relationships by adding a field which concatenates both the primary and related id fields. I will avoid duplication by making this new field unique. I think this will work but I need to figure out how to display custom messages as I don't want the use to have to deal with a 'Revert' style error message. If I don't you might see another post in another forum in the next couple of weeks!!!

Thanks again for taking the time to respond.

[Fenton]

Yes, 7 is amazing when it comes to what you can do with relationships. I see the point of the "reverse" script. Hopefully you are simply creating a new record in the CompanyJoin file that is a "mirror" of the Ids. That would be relationally sound, and what you'd do manually.

A reverse with the "company linked by person" is trickier. The fact that John, who works for Accounts.com, is Bob's accountant does not mean that Bob is John's accountant. It could however mean that Bob is John's client.

Which means that you'd need, as you said, a "type" of connection. And a reverse record in the PeopleJoin file, which would be automatically created for the "client" types. But this could get tricky, if not all types automatically get a reverse "client" record.

I agree that the user interface is going to take some work, so that it gives relevant information and handles much of the data entry without either annoyance or error.

[gareece]

Just had a thought! What if I ditched the idea of having seperate company and individual tables but rather I create a single table called contacts. This table would have all fields that related to both companies and individuals (ie. companyname, firstname, surname, etc) and I would also include a field that identifies the type of record (ie. company or individual).

Records can be linked to each whether they are companies or individuals and layouts would only show relevant fields.

I have done this with another database. It means that some fields will never get populated but this have not caused a problem so far.

By the way I have created this 'reverse' relationship by adding a new record in the join table. Thanks for the tips.

[Fenton]

I don't know about the efficacy of combining Companies and People. Perhaps, if it works better for what you're doing. But I don't see that you gain much. You'll find yourself creating extra calculation fields and relationships within that one table, just to keep companies and people distinct. It will likely get more complex and confusing rather than less. It is not always better to keep the number of tables to a minimum. It's best to keep the relational structure as simple as you can to reflect the reality of a situation; but not to cram things together that would be better kept separately. Unless these "businesses" all only have one proprietor and no employees.

I like the idea of the "reverse" records, with a "type" to distinguish what the "relationship" is (accountant - client, lawyer - client, retailer - customer, etc.). I think the type adds relevant info to the relationship (unless you don't care). But this just means 2 records in the relevant join file instead of one.

The relationship of one company to another, not through their people, but just companies, may be a little more difficult to determine. In that case perhaps the type could just be left blank, or something generic.

Where I do see a seeming consolidation of a Company and a Person is in the case of the one-man business (like me). In that case there is a record in Companies and a record in People. There are 2 kinds of relationships this company-person can have: a business relationship with another company (my business lawyer; I don't have one), or a personal business relationship (my masseuse; wishful thinking, a car repair shop; anything that you can't/shouldn't write off your taxes).

So that's more or less the same as anyone else. What matters is that you have to decide, for each "relationship," whether it's business-business, or personal-business, to know in which portal to enter the data, and which way the relationship is going; who is providing the service/material and who is receiving it.

(P.S. I've been testing command keys with my thesaurus. Makes me sound smart -)

[gareece]

Thanks Fenton

I do have a few single person person companies on the db but I also have a significant number of multi employee ones too. I think I need to contemplate the structure over the next few days (or beers, which ever comes first).

In terms of data used to explain a relationship (accountant - client, lawyer - client, retailer - customer, etc.) I will need to have some sort of generic entry or a script that allows the user to enter both sides of the relationship. I see this as a trade off of how much automated data entry to minimise user data entry time vs. allowing user to enter very specific values. I doubt I'll ever need to search or use this field other than to give the user an 'idea' of what the relationship is so that related contact portals are meaningful. Mmmmm.

By the way, I think that with an average of 2 posts a day the IRS should accept that your masseuse bills are a fair and reasonable tax write off!

Thanks for all your help