multiple to multiple relationships

Thanks in advance for your assistance on this...

I am using Mac OS X 10.5 and FileMaker Advanced 8.5

I am working for a film festival in assigning sponsors to each film in
the festival according to the interests of the organization

I have five databases...Two main databases, Organizations and Films, a
Keywords Database, and two databases for assigning keywords

I assign the same keywords to Organizations and Films through the
keywords database. These assignments are stored in two assignment
databases that list the Organization ID/Film ID, depending on which
one it is, and the Keyword ID, and I am able to show them in a portal.

When I am in the films database, I am able to set up my relationships
so that I can see list the organizations that have at least one of the
keywords I have specified for the film, but I want to set it up so
that it shows ONLY the organizations that have all of the keywords
specified.

This is driving me a little crazy, and I'm wondering if it's even
possible to do this.

Thanks.

David Averbach

Re: multiple to multiple relationships

On Apr 8, 8:13 am, David Averbach wrote:
> Thanks in advance for your assistance on this...
>
> I am using Mac OS X 10.5 and FileMaker Advanced 8.5
>
> I am working for a film festival in assigning sponsors to each film in
> the festival according to the interests of the organization
>
> I have five databases...Two main databases, Organizations and Films, a
> Keywords Database, and two databases for assigning keywords
>
> I assign the same keywords to Organizations and Films through the
> keywords database. These assignments are stored in two assignment
> databases that list the Organization ID/Film ID, depending on which
> one it is, and the Keyword ID, and I am able to show them in a portal.
>
> When I am in the films database, I am able to set up my relationships
> so that I can see list the organizations that have at least one of the
> keywords I have specified for the film, but I want to set it up so
> that it shows ONLY the organizations that have all of the keywords
> specified.
>
> This is driving me a little crazy, and I'm wondering if it's even
> possible to do this.

Not easily. You'd have to generate a match field that contains the
Power Set (less the empty set) of related key words, and do a match on
that.

So if there are 3 related keys {x,y,z}
The power set would be:

{x}
{y}
{z}
{x, y}
{x, z}
{y, z}
{x, y, z}

you'd have to generate that and store it in a single field, which each
subset on its own line.

and then you'd do a match on that. (note that the keywords need to be
sorted, because you only need {x, y} not {y, x}, but if one side had
{x,y} and the other {y, x} they still wouldn't match, so keep them
sorted.

Given two multiline match fields filemaker will return a match if any
line in either field matches any other line. So the powerset field
would be attached to organizations. And in films, you'd just generate
a key with all the keywords... so if the film has keywords {x,z} it
will match the 6th line in the example above and match. If the film
has keywords {x,y,z} it will match the 7th line. If the filem has
keywords {w,x,y} it won't match the example above... etc.

Note that if there are a lot of keywords this rapidly becomes
untenable, because the size of the power set is 2^n. With 15 keywords
for example, the power set will have 32,768 subsets. So this solution
should only be considered practical if there is some reasonably low
upper limit on the number of keywords being applied.

Also note that because the powerset key must be indexed to work as a
foreign key, it can't be an unstored calculation based on the org-
kewords relationship. This means that it must be recalculated and
stored each time a key is added / removed from its keywords.

Thus you'll have to script adding/removing organization keywords to
also recalculate and set the power set field.

I do not believe there is a simpler or cleaner way of doing this in
Filemaker.

-regards,
Dave