Many to Many relationship between the same table

[Tom R.]

Hi -

I could use some help setting up a many to many relationship between a single table. My database is being developed to catalog real estate documents, such as deeds, mortgages, leases, etc., that are filed in the real property records of a county courthouse. The first version of my database had separate tables for each type of document, and it was a mess.

I've started over, and have a single table, Documents, and each document record is tagged as a particular type of document. This data classification system is based on the demo at FileMaker Magazine (http://www.filemaker...sification.html). So, I have a Classifications table and a Tags table, which is a join between Documents and Classifications.

An additional complexity is that Documents are also related (many to many) to projects (represented by the Opinions table in my DB). So there is an Opinion_Documents join table between Opinions and Documents.

What I need to do is to relate certain Document records to each other. E.g., one Document type is "Lease", and another is "Lease Assignment". A Lease can be assigned multiple times, and a Lease Assignment can assign multiple leases. The problem I can't figure out is, if the current record is tagged Lease Assignment, how do I show (either in a portal or a "picker" list layout) all documents tagged as "Lease" and that are also related to the currently selected Opinion (represented by a global value), so that a user can select them to add to my join table Document_Documents. (Or if the present record is tagged Mortgage Assignment, how do I show records tagged as Mortgages.)

I'm attaching a zip file of my database, which is in filemaker 12 format.

Thank you in advance for any advice!

Title Database.zip