I'm new to fileMaker, but have been reading a lot on one to many and many to many relationships... but still stuck. I've tried creating a 3rd table keying off elements of two other tables. But I'm not getting any results (populated fields)

In a nutshell I've got a database that idenitifies Composites which are made up of Roles. Composites can have many Roles under them. The same Role can appear in multiple Composites.

Then I have the Role to Transaction Code relationship in which Transaction codes can similarily vary in Roles they are housed in.

Under "test" layout I was attempting to populate results.

Any direction would be appreciated.

v5.fp7.zip

It's usually easier to populate the join table through portals, that way one of the foreign keys is populated by the relationship. I have added a portal to each of your Composite and Role layouts so you can see how it works. Adding a value list of the available Role/Composite IDs simplifies data entry and ensures referencial integrity.

I have also renamed your tables so they are clear about the entities they represent, and changed your recordIDs to be auto-entered serial numbers, to ensure all Composites and Roles have IDs (so they can be used in relationships.)

Good luck, jellyroll!

CompositeRolev5.fp7.zip

Looks good. Though I'm having trouble when I import records. It does not seem to update the relationship on the portal. Any ideas?

However I did get the data in the format I need, much appreciated. The portals are relaying the data via a new layout that wasn't functioning before :

Orson Scott Card rocks!!

Edited February 3, 200619 yr by Guest

The issue appears to be that the Composite_Role Table is not updating to the new imported records. This isn't something I have to do manually is it?