Confused relationships...

[chasm24]

Hello all my filemaker friends....I'm having some relationship/portal issues...let me explain:

I have 3 databases: Projects, People, and Companies, each record in all 3 DBs have IDs to use as primary keys. I have a table that links all of these together called LINKS with the only fields being Project ID, Person ID and Company ID. The trouble is that the links i make are always between one project and one person, one person and one company or one company and one project.

In the Project Database I have two portals: one that shows related people and the other that shows related companies. The problem is that when i make a link between a project and a person, the person shows up in the portal as expected, but there's also a blank line that shows up in the companies portal that I don't want to be there. Is there a way to make a link without creating extra "blank" lines in the other portal?

what do you all think?

[BrentHedden]

It sounds like one of two things -

1) You have a "blank" record in the person table. The record has a valid ID, but all of the other fields are blank/null. If this is true and you don't want to delete the record, add another condition in the relationship to exclude this/these "blank" records.

2) You allowed the relationship to "Allow creation of records in this table via this relationship". This is set up when you edit the relationship. If this is turned on, any portal you make will have a blank set of fields at the bottom. Whenever something in entered into these blank fields, a new record automatically gets created. It's a handy feature, but not always wanted.

[SlimJim]

I think the problem lies in the logic of the LINKS table. You are trying to use one LINKS table when in fact you need three. One for each of the three types of many-many relationships you are setting up. Whenever you set up a person-project link with a blank company field in LINKS then you link both the person and the project to a blank company and at the same time create two further links that you do not want.

[aaa]

I think that you must construct your tables such:

Company -> Company_Id field

Projects -> Project_Id,Company_Id fields

Peoples -> People_Id,Project_Id

Then create relations:

Company <-> Projects by Company_Id

Projects <-> Peoples by Project_Id

And you will can to answer to all your questions

[chasm24]

I need three separate tables to create the links? I thought I could just have them all in one...Doesn't this make for an unwieldy relationship graph?

[SlimJim]

The question is: does the scenario proposed by aaa fit the bill? I may well have misinterpreted what you were saying but I got the impression that all the relationships were many-many which is why I suggested three tables. If some of them are one-many then my comment regarding three tables is not valid.

Regarding your blank lines: if you write a record into LINKS which contains a project, a person and a blank company field then the relationship from Projects to companies is going to produce a blank company record. If the person belongs to a company involved in the project then you should put their company into the company field

[RalphL]

This could be a star join. See Using FileMaker 7 pp 149-151 & 176-178. Also look a the sample file Task Management.fp7. The table Assignments is much like your Links table.

[chasm24]

I've made separate tables for each type of link:

project-company

project-contact

company-contact

on some of the relationship graphs i have to create a separate table occurence (Contacts 2). Will this complicate anything? it seems to work just fine but i want to make sure that the portals can display everything that's related.

[SlimJim]

Thanks for the reference. An interesting concept for interdependent data. The diagram on p150, Fig 5.22 is what I had in mind when I suggested that three join tables were needed. So for the poster here it depends on whether the project-company-person data fits into the picture. As a direct comparison with the example in the book: project as route, company as truck and person as driver and the LINKS table as route assignment (= project assignment) might work.

[Added later] Sorry didn't see the new post by chasm24. The three link tables plus the three other tables are related in a circle (not allowed) so you need one duplicate to break the circle and make it work so there should not be any problems with contacts 2 other than making sure that the fields on the layouts come from the right contacts table occurrence.

[RalphL]

When I designed a database as shown in Fig 5.22, I found it necessary to add a 7th table that joined the 3 join tables in order to get the 3rd join table to fill in from the data in first 2.

[chasm24]

RalphL, how did your 7th table join the other 3 join tables? did you have to assign a key for each record in the 3 join tables and then link them that way? This seems to make things very complicated

[RalphL]

I don't remember the details but a lot of scripting was required. And yes it was complicated. I can dig up the files if you are interested.

[chasm24]

thanks, but i think i have it under control now. just had to use a second occurence of the company table and everything is working okay. thanks for everyone's help!