Finding References to Contacts across numerous tables

[chlowden]

Hello

I am a newbie and I am having a very hard time getting my head around a ,probably, very easy logic.

I have 7 tables. 1 is contacts (freelancers), another is clients and the last 5 are a collection of companies. All the contacts can work for any of the 5 companies. Any Client can work for any of the 5 companies.

In the 5 company fields, there are 10 work posts that are populated from the contacts table. Yet each contact can have multiple and different roles depending of the project.

I have managed to create in the Client table layout a portal via a client_ID from each project that shows which of the 5 companies the client has worked with. I am trying to apply the same the portal idea to show each of the contacts to track who has worked for whom & on what BUT I just cannot get my head around it where to start.

I am convinced that what I have so far built is far from elegant but it works. To make things clearer there are attached a couple of pix. What I really want is to collect the relevant contact data from all the tables and have portal that shows the result condensing in the contact layout for both projects and job titles.

Many thanks in advance.

[mr_vodka]

I think that you should change your structure to something like the following:

Tables:

Contacts

Companies

Job

JobRole

Role (Optional)

I think that perhaps you may be able to combine your contacts and clients into ones table. Obviously I have not seen all the fields in both tables but I believe that you shouldlbe able to and then use a field to dseignate whether it is a contact or a client as per your definition.

Then there is the companies table where you store the company info.

Next is the Job table. This would basically be the main table where you would store all the info about the job and perhaps store the foreign key for the company.

Next is the Role table where I sould store all the roles that anybody could have. This table is optional depending on how many roles there actually are.

Finally, I would have a JobRole table. This is where it would store the JobID, the ContactID, and the RoleID / Role.

Generally speaking, if you find yourself creating bunch of fields such as AssistantMonteur1, 2, 3, etc. It mean that you should probably look into creating a new table. If you create this other table, your relationships graph will be greatly cleaned up as well since you will not need all those excess Table Occurences.

[chlowden]

Thank you for your very concise response. I don't understand what the advantage is to have a job roles table. I thought about putting a value set to define the contact name (makes the solution more dynamic) but what I really want is to have a report that tells me in the contact layout, on what project did the this particular contact have and what was his role. I have tried to keep a 'step-by-step' relationship but this job role issue is a sort of cul-de-sac that I can't reverse out of.

A question, in my solution, each job role has a relationship via a contact-ID number. The actual contact name is a relationship via the contact field. When I try to connect to this contact contact name field in the project page (see picture 2) the portal has no name but if connect to the contact-ID that feeds the contact name,I get the right contact-ID number. Therefore are portals only for 'real' text and not for info brought in via another relationship?

[comment]

  Quote

I don't understand what the advantage is to have a job roles table. ... but what I really want is to have a report that tells me in the contact layout, on what project did the this particular contact have and what was his role.

That is exactly why you need a join table between Projects and Contacts.

  Quote

are portals only for 'real' text and not for info brought in via another relationship?

Portals are for showing MULTIPLE related records. Without a portal you only get information from the FIRST related record.