MarcoB Posted September 27, 2007 Share Posted September 27, 2007 (edited) I have 3 tables (all of them are one-to-many) that are combined to make other 2 tables: OWNER ownerID.pk FARM farmID.pk ownerID.fk BLOCK blockID.pk farmID.fk block_number OBS: blockID.pk is a serial number for all the the blocks from all the farms in the table BLOCK. block_number.pk is a name we give to the block and is used in the farm, we often use to number it is sequence. One farm can have from 2 to 200 blocks. ex: [color:red]blockID.pk farmID.fk block_number 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 3 1 7 3 2 The tables have more fields, these are the basics fields you need to understand my problem. The relations between these 3 tables are fine and working correct. Now I have 2 more tables, and my problem starts here. Every six months we have to make an inspection in blocks and compile a report. One inspection for each farm. Here are the tables: INSPECTION inspectionID.pk farmID.fk INSPECTION_LIST inspection_listID.pk inspectionID.fk farmID.fk block_number My problem is in the relation between table INSPECTION and INSPECTION_LIST. I have a portal in a layout called Inspection to show INSPECTION_LIST lines and create records but I got the same information every line in INSPECTION_LIST. I want to enter the block # in the line and it would bring the information from the record in BLOCK, and I would enter the information about the block inspected. Edited September 27, 2007 by Guest Link to comment Share on other sites More sharing options...
Fenton Posted September 27, 2007 Share Posted September 27, 2007 The relationship from Inspection to Inspection_List would need to include FarmID.pk. Because the block_number is not unique. Link to comment Share on other sites More sharing options...
MarcoB Posted September 27, 2007 Author Share Posted September 27, 2007 But I included farmID.fk. I thought the Primary Key (farmID.pk) should stay in the parent file. Link to comment Share on other sites More sharing options...
MarcoB Posted September 28, 2007 Author Share Posted September 28, 2007 I did it, but it is not working. Link to comment Share on other sites More sharing options...
MarcoB Posted September 28, 2007 Author Share Posted September 28, 2007 I'm including my ERD for this project. ERD_Disease.pdf Link to comment Share on other sites More sharing options...
Fenton Posted September 28, 2007 Share Posted September 28, 2007 (edited) So if you type a block# into the Inspection-Line portal, you should get a unique combo of InspectionId-FarmID-Block#. If the records in the portal are the same, it is often because the fields have been miss-assigned; they should be either the TO of the portal, or a TO FURTHER down the relational line. In order to see more Block info, you would need another TO of Block attached to Inspection-Lines. You cannot go BACK to the Block TO, because that passes thru Inspection, where the the block combo is not in any way unique (as block# isn't even there, and could not be). That could be the problem. You have to pass THRU Inspection-Lines to see data from the unique block. Edited September 28, 2007 by Guest Link to comment Share on other sites More sharing options...
MarcoB Posted October 1, 2007 Author Share Posted October 1, 2007 Sorry, but I don't have so much experience with Filemaker and I don't understand wht you mean. Link to comment Share on other sites More sharing options...
MarcoB Posted October 3, 2007 Author Share Posted October 3, 2007 (edited) OK, I read and read your explanation and changed things around, now I have it done. Now it is working but I don't know if it is a good design or just a bad but working design. Fenton, I would like to hear what do you think. Edited October 3, 2007 by Guest Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6142 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