tangled relationships

[dabombsmom]

I'm working with a media database. It's already got a lot of relationships (staff, media, various codes,etc.). I want to be able to save many lists by subject. What I've come up with is to make a new DB called List Names. Then I make a clone of the staff file with a field called list name. I wanted to make it a global field but this is giving me a problem, too. So I replaced all with the list name ("A") in the 1st clone. A is the 1st record in List Names DB. Now, to create the A list, I'm exporting names from the staff file into the clone, and each time I create a new list (clone), I create a new entry ("B" etc.) in the List Names DB. Follow me so far?

Here's the tough part. I want to be able to see what other lists a record is on. For example, I have lists named A and B, and staff records numbered 1, 2, and 3. A contains 1 and 2. B contains 2 and 3. I want to be able to see the list names A and B in record 2, regardless of whether I'm looking at it in list A or list B. I haven't been able to accomplish this yet. I tried a drop down menu with a value list of related records from the list field, but when I'm in DB A it only shows A, and B only shows B. Is this because the relationship is circular? Help?

I have tried doing this with repeating fields, but it's too complicated for my associates.

[Vaughan]

My only suggestion to "see what other lists the record is on" is to create a layout, and put a portal on the layout for every relationship the record may be related to. That wa you'll see everything at once.