kellen Posted October 31, 2007 Share Posted October 31, 2007 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 Link to comment Share on other sites More sharing options...
Fenton Posted October 31, 2007 Share Posted October 31, 2007 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. Link to comment Share on other sites More sharing options...
kellen Posted October 31, 2007 Author Share Posted October 31, 2007 Thank you Fenton, sometimes I need those simple things pointed out to me. I appreciate your help. Link to comment Share on other sites More sharing options...
Vaughan Posted October 31, 2007 Share Posted October 31, 2007 The primary key of the original table should be set to not allow modification after entry, to explicity prevent the value from being changed. Link to comment Share on other sites More sharing options...
Totes Posted October 31, 2007 Share Posted October 31, 2007 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 Link to comment Share on other sites More sharing options...
Fenton Posted November 1, 2007 Share Posted November 1, 2007 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6156 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now