I have a database tracking contracts that my company (an agency, basically) makes on behalf of performing artists, with the people who are presenting them (and paying for their performances).
Since some of our artists collaborate with other of our artists or sometimes with outside guest artists, I need to be able to assign more than one artist to a given contract, and divide the total fee between the participants. Each contract only ever has a single presenter - whoever is footing the bill. In very rare cases, there are two funding sources and I either make a separate contract for each one with separate amounts, or one agrees to be the contract party and "subs out" to the other entity.
So, I have a table called Contracts which keeps the dates, internal sales info, and details of each contract, along with standard legal terms which are copied over from global fields so that I can modify them on a contract-by-contract basis.
I have an Artists table which keeps our artists' personal info and any pertinent info for working with them.
I have a table, FeesAndArtists, which is basically a child to the Contracts table. It is related to the Contracts table using the Contract# (which is not the primary key for the contracts table, but acts like one - it is a unique identifying number for each record) and records in FeesAndArtists are created from a portal on the main Contracts entry form layout. I enter the Artist ID (from a drop-down box containing values from the Artist ID field in the 'Artists' table), and the amount that individual artist is to be paid, and Filemaker auto-enters the contract number.
I have a relationship set up between the Artists table and the FeesAndArtists table, using the Artist ID (again a unique identifier, but not technically the primary key).
So as I understand it, and as my relationship graph shows, 'Contracts' and 'Artists' are related in a one-to-many/many-to-one relationship, using 'FeesAndArtists' as a join table. However, I tried to create a portal to display related records from 'FeesAndArtists', in the main Artists layout, to allow users to browse the contract history of an individual artist, and no records appear in the portal.
I'm stumped. As far as I can tell, it's all set up right. What could be going wrong here?