Relation Struggle

[Irenem]

Hello folks,

I have a table called Contacts, here I store companys and persons an they are related to eachother trough self-join relationship. I have created a second table called functions.

Now I want to add functions.

EG: Person A works at company B as a secretary but she has also the function of Director at company C.

In my company layout I have a portal showing the empolyees. How can I show the differnt functions of the same empolyee in the different company records?

I hope somebody can help .... I'm desperate!

[Ender]

Instead of using a self-join, you would need a "Function" table to record the type of relationship between a Person and the Company. The Person and Company records could still reside in the same table, if this works best for other parts of the workflow. Personally, I'd probably have Company and Person be separate tables.

[Fenton]

Instead of a self-relationship to tie a Person to their Company record you should use a separate "join table", with the PersonID and the CompanyID on each record. That will allow you to have multiple companies for a person. The function would go in this join table, as it belongs that Person-Company connection.

It would a portal, with only a few rows, in the Person file; also similar in the Company file (though more rows). From Person you would choose the CompanyID. (From Company you would choose the PersonID, but that's more awkward.) The relationship from People to join would be on PeopleID, with "Allow creation of related records" (also "Delete"). So all you'd have to do is choose the company.

The CompanyID in the join table would also come from a PersonID (as there's only 1 table for both). But you'd create a calculation field, with an ID ONLY for Company records, and use that for your value list.

This all works OK. But it seems as if you need a separate layout and table occurrence for 'people' and 'companies', at least separate portals and/or separate drop-down value lists. I believe Stephen Dolenski here at FmForums has perfected this combo table with join table technique; I'm sure his has a better interface. But I've always just had separate tables for companies and people.

If you have separate layouts, then Finds would need to be trapped for just that type. All and all it would seem to be better to have only 1 layout; maybe a new pop-up window for adding links?

CompanyPeople.fp7.zip

[Ender]

Wow, I've never felt so in-sync with Fenton before. It's kind of exciting!

[Irenem]

Thanks for the reply.

I have a Function table. The problem is the relation between the Function table and the Contacts table.

Occurrence 1 (Layout is based on this occurrence)

ContComp

Relation:

ID=RelID

Occurrence 2:

ContactsEmpl.

Relation:

ID=FunctContactID

RelID=FunctRelID

Occurrence3:

Function

The problem is that in every record that is related to the same emplyee i see the same function. I do something wrong with my relation, but I can't figure out what.

[Fenton]

When you place the "employee" field on the layout, ie., in the portal row (whose relationship is to the join table), the "employee" is not from the join table (obviously, 'cause it isn't even there), but it is also not using the same relationship back to the original Employee table occurrence.

You need another instance of the Employee table, a table occurrence on the other side of the join table; so the relationship is passing through the join table, not just reflected back.

[Irenem]

Thank you, you made my life a lot easier today.

Sometimes I think much to complicate, but thanks god we have a forum with a lot of good members!