Trying to create another table instance as a filter

[nick_jp]

Hi, trying to build a simple client tracking DB here...

The relationship I have is fairly simple:

A company can have many employees

An employee can be assigned to zero or more roles within the company.

By "role" here I mean something like "primary contact", "billing contact", "technical manager" etc.

I have the following tables:

Cust_Company (Company level record)

Cust_Company_Entity (Company employee level record)

Cust_Entity_Role (List of roles that an employee can be assigned)

Cust_Entity_Role_Join (The join table for linking a company employee to any given role)

Now, I have an overall company view, which has some portals to various child data sets, like projects, employees etc for a given company. I want to have a couple of seperate fields on this form showing the Primary Contact name and phone numbers (name and phone number are stored in the client employee record).

I think I need a portal here, showing a single record which is the Cust_Company_Entity who is defined in the Cust_Entity_Role_Join table with the entity's FK Company_ID field matching the company PK Company_Field and the Cust_Entity_Role_Join record's FK Role_Type field matchin the PK field of Cust_Entity_Role where the role is "Primary Contact"... not sure how to do this correctly though. Table instances? A calc field?

I tried making a 2nd table instance of the Cust_Entity_Role_Join table and relating it directly to the Cust_Comapny table in the relationship graph (I added a calculation lookup field to Cust_Entity_Role_Join to get the company_id), but I'm not getting any data and I think the approach is wrong...

Could someone please advise on a "correct" way to do this? I'm sure dynamicly filtered subsets are staple on this platform. Would really appreciate any help.

Thanks,

Nick

Edited October 30, 2007 by Guest

[Fenton]

Good guess. A calculation field, result text, Storage, [x] Do not store (important unless you want to bloat your file). The calculation is "Primary Contact" (that's all).

A table occurrence (TO) relationship, from Company TO to another instance of the Role-Join table, based on the CompanyID AND the above field, to the CompanyID and Role will match only the primary contact (you do have only 1?).

The name (phone, etc.) will show from one more step along the path, to another instance of the Employee table. These related fields do not need to be in a portal really, since there's only 1 match.