the database is changing fields at will!

[chasm24]

help! my databases have taken on a life of their own. I have a simple relationship set up between two tables: projects and contacts. One project can have many contacts involved and one contact can be involved in many projects.

I have a portal in each table that displays the related records from each table. the key field is project name. in the contact table, when i indicate in the related field that the contact is involved in a specific project, then he magically shows up in the portal in the projects table. great so far.

BUT, when i go to a second contact and indicate in the related field that he is involved in the same project as the first contact, all the information transfers to the portal except all of a sudden, the second contact's first and last name change to the first contact's name. If I catch the change and make the correction, the the original names stick, but if I don't, then i have a bunch of contacts with the same first and last name, but different phone, email, etc. PLEASE help me before my databases devolve.

Also, how do I build the relationship so that a contact can be involved in many projects and show up in all the portals in the project table? for example, let's say i have 3 slots in a contact's record for being involved in a project: related project 1, related project 2 related project 3. I want it so that if any of these fields match the "project name" field in the projects table, that the contact info shows up in the portal. Right now, when i try to make it so that project name equals related project 1 and related project 2, no records show up in the portal because those criteria are never met as a perfect match. i feel like i'm talking in circles now. can someone help me sort myself out?

thanks

[Wim Decorte]

First of all, showing data in a portal does not "transfer" data at all. It only displays related data.

Your relational model is not correct. You can't have a many-to-many directly between two tables. You need an intermediate table. Call it "assignments". It only needs 3 fields:

- assignement ID

- contactID

- projectID

It's related to the Projects table by the projectID and to the contact table by the contactID. Each time you want to add a contact to a project you add a record to that table.

In the Projects layout the portal should show the related data from the assignments table.

[chasm24]

but what if I want to display more than just a contact ID in the projects table portal? I want the user to be able to see first name, last name, etc. thorough information about the contacts involved in the project.