5 tables and one headache

[MarcoB]

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

[Fenton]

The relationship from Inspection to Inspection_List would need to include FarmID.pk. Because the block_number is not unique.

[MarcoB]

But I included farmID.fk.

I thought the Primary Key (farmID.pk) should stay in the parent file.

[MarcoB]

I did it, but it is not working.

[MarcoB]

I'm including my ERD for this project.

ERD_Disease.pdf

[Fenton]

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

[MarcoB]

Sorry, but I don't have so much experience with Filemaker and I don't understand wht you mean.

[MarcoB]

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