Match Criteria--finding common ground between tables

[chasm24]

good morning everyone,

I'm attaching my relationship graph in the hope that it will help me explain my issue. I have 3 database files: projects, companies and contacts. Between each I have a simple linking table (project-company, company-contact, and contact-project). Totally separate from these links, though, I want to create a relationship between projects and companies that will allow me to create a portal, listing all companies that "match up" with projects, according to the "sector" field in each database. I've tried to create that relationship in my graph but the portal i made in the projects database to display related records in the companies database is completely empty, even though I know that many projects and companies have the same sector.

Any idea what I'm missing?

I appreciate the help

Companies_DB_Relationship_Graph.pdf

[RalphL]

Try replaceing the 3 linking tables with one table. This is called a star join. There is an example of this in the example file named Task Management that comes wiith FMP 7.

[chasm24]

thanks ralph,

but won't this create problems in my portals? I want one portal to display records that are related because the "sector" field matches up while the other portal should display records that are related because i made a link (with the two primary keys) through the intermediary table.

[RalphL]

No it shouldn't. Take a look at the file I suggested. Or look at Post#139633, I posted a sample file there.

[chasm24]

Hi Ralph,

I've looked at the task management file and it does seem to do some of the things I'm trying to do, but It's very hard to decipher exactly what's going on in the ERD (sorry, i am a beginner). How do I search for post 139633? I'm hoping that file will makes things a bit clearer.

the task management file is smooth but i don't quite understand how all the "similars keys" function together. Can I create a "similar sector" key between a companies and project database?

the other questino I have is that i made what i thought were links between tasks and projects in the sample task management file, but when i looked at the assignments table, there were no records. how can there be assignments and no assignment records?

your help is very much appreciated, i'm trying very hard to understand this.

thank you for your time.

[RalphL]

Look at http://fmforums.com/forum/showtopic.php?tid/139633/post/139633/hl/CommitteeRalphL/#139633

Similar keys are sort of a standard part of all the FileMaker sample files. They are just the same Last names or same Company Names or same City. In the related contacts the user can pick one of these with a radio button.

If you look at the Relationship chart there is a Table Object Group (TOG) with Projects in the center and a relationship to Tasks Project ID. (The 2nd from top.) This is the relationship that is used by the portal where tasks are shown. It allows you to define tasks for the project but makes no assignments. That is why there are no assignment records. If you go to the other tab then you have the relationship with assignments. You assign people to tasks.

Or you can go to Tasks the 3rd TOG and assign contacts to the task there.

The report uses Assignments TOG 4.

The closest thing to an E-R Diagram is TOG 4.