I could use some help designing a database. I have some FM experience, but am not an experienced developer.

The database is for managing collections in an archaeology lab. I have the basic design ok but there is one complication I'm not sure what to do with. We have accessions, material that comes into the lab by donation or a project we do. These are stored in boxes. Each box has an ID. Within each box, I have box contents (like items on an invoice). A box can have contents from multiple accessions or have multiple items from one accession. Right now I have tables for Accessions, Boxes (with ID and location), and box contents (with relations to accessions and boxes).

In a layout for the Accession, I have a tab that shows the box contents for that accession in a portal. There can be multiple listings for a particular box, so location information for the box is duplicated.

This is ok and gives me what I need. However, if possible I would also like to have a tab showing just a list of all boxes that contain material from the accession without the duplication I get from the box contents portal and a count of boxes that contain material from that accession. However, as I originally set things up, there is no relationship between accessions and boxes since each box can have multple accessions.

The only way I've been able to see to do this is to have a repeating field in the box table with each instance containing an accession number. I can then relate accessions to boxes, but I have the complication of automatically getting the accession numbers into the box table when the box contents record is created.

This doesn't feel like a good way to do it. Can anyone suggest a better method?

I think you're not using the full relational power of 7/8. You do have a relationship from Accession to Boxes, through the BoxContents join table. FileMaker 7/8 has no problem going through an (or multiple) intermediate Table Occurrence (TO).

You can create a calculation field in Accession to count the boxes.

Accessions_fej.zip

Thank you, Fenton, that does the trick. I just added a portal in the Accession layout and linked to the Box table and got just what I wanted. I didn't realize I could get to the Box table without a specific relationship between it and the Accession table.

Who'ld a thunk it.

Thanks again.

Mike