Many-to-many quandary

[kellen]

I have a database with referral records to different medical clinics in our area. I've hastily simplified and modified the file if you feel like looking at it. This is an already made database that I'm trying to restructure to work better. There is a table for the referrals called "ReferralData"

Also, there is a table I've just created for types of services provided called "Terms", examples: "general physical", "cholesterol blood test". This is related to the "ReferralData" table in a Many-to-many relationship.

I also have a table for groups of these service terms called "TermGroups". This is related to the "Terms" table in a many-to-many relationship so that i can have overlapping term groupings.

I put in some portals to better pick out relating terms/groups/referrals on related tables. My portals between "Terms" and "TermGroups" work great, I made value lists so as to be able to pick related items better.

I tried to do the same thing with "Terms" and "ReferralData".

As far as I can tell, this table relationship is exactly the same as "Terms" to "TermGroups". All the settings for the relationship and the portals are the same for both relationships. But when I try to select a line of the portal in either "Terms" or "ReferralData", I can't. Just to figure out what was wrong, I made a record in the join table "JoinTermReferral" with two preferred related ID numbers. the relationship showed up in both portals. But in the portal on "ReferralData" I tried to change the ID number list to change which "term" the referral was related to, but instead it modified the ID number of the "Terms" record that I was trying to change from.

How do I make my "ReferralData" to "Terms" relationship work just as well as "Terms" to "TermGroups"?

I hope my descriptions aren't too horribly confusing. The file is attached, all relevant layouts are at the top of the layout list. All relevant fields are at the top of their respective layouts. Relevant value lists are at the bottom.

ircDataBase.fp7.zip

[Fenton]

When you have an enterable, modifiable ID field in a "join" portal, it should be a foreign key OF the join table, but never the primary key of the original table. Otherwise you're changing the primary ID of the original table. Yikes! You did the same thing in both your portals.

[kellen]

Thank you Fenton, sometimes I need those simple things pointed out to me. I appreciate your help.

[Vaughan]

The primary key of the original table should be set to not allow modification after entry, to explicity prevent the value from being changed.

[Totes]

Question?

I am about to attempt something similar as to what you are discussing (my first run at it).

If my

__KP_Incident_ID

is a "Number" "Auto-Enter Serial, Cant Modify Required Value, Unique...can I use it as a the FK in my join, or do I need to create something else?

Thanks,

James

[Fenton]

The foreign key in the join table is a different field. So the settings of the primary key in its own table have little to do with that. It is when you confuse the two that you get into trouble.