Help! A (complex?) related list

[Ray8989]

Hi guys. Newbie here – and I do mean newbie. Humanities major newbie. I’m working on a theater alumni database for a high school. Don't ask why.

I’ve got about 2500 contacts. Each record includes a list of the productions that contact was in, the date of the production, and the contact's function in the production (actor, crew member, etc). It's the table to the right of the screenshot here:

I'd like to create a button that will show all contacts who were in the same production (similar to the ones that already exist at the bottom of that screenshot, for name and graduation date). To throw a wrench into it: The similarity criteria can't just be the Production Name, because there have been productions with the same name. It would have to match Production Name with Production Date and then find related contacts. I've looked at the value list tutorial, but don't fully get how to apply it to this. How do I make it so that if any of the productions match, those contacts will be on the list (and then be able to see which productions those are)?

The goal of all this is to send out letters/emails to most contacts saying something like: "These are the productions you were involved in. These are the people who were also involved. Are you still in touch with them? Do you remember this experience?" etc.

Sorry if I sound terribly ignorant/amateur… I am.

Thanks for the help!

[comment]

I’ve got about 2500 contacts. Each record includes a list of the productions that contact was in, the date of the production, and the contact's function in the production (actor, crew member, etc).

I am afraid that's not a good starting point. You should have 3 tables: Contacts, Productions and a join table of Roles. There should be one record in the Roles table for each participation of a single contact in a single production. Use a unique ContactID and ProductionID to relate the join table to its two parent tables.

See a basic example here:

http://www.fmforums.com/forum/showpost.php?post/246136/

and one with slightly more advanced UI here:

http://fmforums.com/forum/showpost.php?post/355429/

[Ray8989]

All right, I did it. Thanks for the response! I've got the three tables, and the Contact ID and Production ID have relationships to the Roles table. I'm having some trouble creating a portal; just not sure how to do it. Can you point me to somewhere that goes through it step by step? Do I have to use value lists?

[mr_vodka]

You can create a portal by clicking the portal tool in toolbar in layout mode. Once selected, draw it by clicking and dragging to the size that you want. When asked which table to reference, you want to reference your roles table. You can choose fields from the production table, role, or contact table as well.

[Ray8989]

I'm sorry, I wasn't clear. I know how to create a portal, but what I want is to show related values - all other Contacts that were in the same production. How do I do that within the portal?

[comment]

all other Contacts that were in the same production.

If this is in relation to a contact, then shouldn't it be "all other Contacts that were in the same productions" (plural)?

[Vaughan]

In the Roles table, I suggest you also have a "name" field to take into account that some people may, for instance, screenwriter under a different name to the name that they use for other roles.

[comment]

I believe that's a pseudonym?

[Vaughan]

I believe that's a pseudonym?

Some people call it that. :D

[Ray8989]

If this is in relation to a contact, then shouldn't it be "all other Contacts that were in the same productions" (plural)?

Yes, that's correct. My English degree is useless, obviously.

[comment]

See if this can get you started. Note that this is only a rough sketch; it needs further improvement by eliminating the current person from the list of its siblings.

JoinSiblings.fp7.zip

[Ray8989]

See if this can get you started. Note that this is only a rough sketch; it needs further improvement by eliminating the current person from the list of its siblings.

(Belated) thanks for this! Database in in much better working order now. Unfortunately, I can't figure out how to take the current person out of the list. What do I need to do to eliminate them?

[comment]

Add a predicate to the self-join, so that:

Roles::PersonID ≠ SameTeam::PersonID