I have a Jokes database of 3 tables:

Jokes
PK_jokeID

Persons
PK_personID

JokesSent
FK_jokeID
FK_personID
date sent etc


The JokesSent join table contains a record of each time I send a joke to a
person. But I want to create a portal-based listing of all the jokes NOT
sent to a person, and also a portal-based listing of all the persons NOT
sent a specific. Obviously, these records do NOT exist in the join table.

Its kinda like having a cross-products between Jokes and Persons directly
(all combinations of each) but excluding those already in the join table.
But I can't figure this out.

Thanks in advance,
FMPlover

( Answer assumes you're usingFMP 7 or greater )

In the same fashion as you create a relationship in which one key EQUALS
another, you can create a relationship in which one key DOES NOT EQUAL
another.

In your relationships graph, double-click the box in the line joining
two tables. In the resulting window, you'll see a symbol selector
between the two sides of the relationship.

A relationship in which Persons::PK_personID <> JokesSent::FK_personID
would yield a list of persons who did not receive the joke.

(In FM7 and later, <> is replaced by the slashed equals sign to
represent not equal)

Matt

In article <GOo2h.162$Rj2.53 (AT) newsfe10 (DOT) lga>,
"FMPro user" <FMPuserX (AT) hicccup (DOT) org> wrote:

Matt,

Not exactly. Your method would "appear" to work under some conditions, but
it's more complicated than just changing the relationship to "not equals."

When would it seem to work? Only when the join table, JokesSent, contained
at least one record for every existing joke. If you added a new joke, but
had not sent it to anyone, the relationship would return zero records. This
is because it's based on the JokesSent table. It would also return multiple
results for the same joke that was sent to more than one person (but not the
current record's person).

IE7 wants to install now, so I'll come back after the reboot with more
information.


"Matt Wills" <Im (AT) Witz (DOT) End> wrote

OK, I see where I went wrong. The OP wants a list of jokes not sent to
the current name, not a list of everyone to whom it was sent (excepting
the current name). I might have figured that out had I actually tested
it.

There's got to be a way, though...

Matt

In article <EdmdnT3ZBPNHqdfYnZ2dnUVZ_s-dnZ2d (AT) comcast (DOT) com>,
"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote:

Matt,

Of course there is

Probably the easiest way is to use FileMaker 8.5 and the following:

I. JOKES NOT SENT TO A PERSON

1) Add a calculated field to Persons

JokesSent (text result) =
If(IsEmpty ( PK_personID );"";
"0¶"&List(JokesSent::FK_jokeID))

This calculation first of all forces an evaluation of the formula via the
IsEmpty() function. Next, when there is an ID (and there always will be), it
builds a list of all the Joke IDs associated with this person. Importantly,
it adds a zero to the beginning of the list. (Assumes there is never a Joke
ID of zero). So, if a person has not been sent any jokes, then the result is
simply 0. If they have received some jokes, then the list is

0
1
4
7

Or something like that.

2) Visit the Relationships graph and add a copy of the Jokes table. (Yes,
that's Jokes, and NOT JokesSent.) Call this copy of the table occurrence,
"JokesNotSent"

3) Connect JokesNotSent::PK_jokeID to Persons::JokesSent

4) Double-click the "=" connector in that relationship and change it to "?"
(Not Equals)

You're saying, show me all the records in the Jokes table that are not among
the lines of the JokesSent calculation.

5) Add to your Persons layout a portal based on JokesNotSent. Add the fields
from JokesNotSent that you're interested in viewing.

II. PEOPLE WHO HAVEN'T SEEN A JOKE

Fill out the other side:

1) Add a calculated field to Jokes

PersonsReceived (text result) =
If(IsEmpty ( PK_jokeID );"0";
"0¶"&JokesSent::FK_personID)
2) Add a copy of the Persons table to the relationships graph. Call this
copy of the table occurrence, "PersonsNotReceived"

3) Connect PersonsNotReceived::PK_personID to Jokes::PersonsReceived

4) Double-click the "=" connector in that relationship and change it to "?"
(Not Equals)

5) Add to your Jokes layout a portal based on PersonsNotReceived. Add the
fields from PersonsNotReceived that you're interested in viewing.

In article <Im-97E4F3.12311402112006 (AT) news (DOT) verizon.net>, Matt Wills
<Im (AT) Witz (DOT) End> wrote:

That's true for all versions of FileMaker, and in fact the two "not
equals" symbols are interchangeable - you can use whichever you prefer.
On the Mac the "slashed equals" symbol is easy to type by pressing
Option and =

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

You can see the relationship graph for this technique at:

http://wjm.org/linked/jokesnotsent.gif

Some newsreaders don't like the not-equals sign; so in the instructions
below where you see a "?" replace it with the not-equals symbol, =/=, as
depicted in the GIF link above.


"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote

Typo Monster strikes again:

Part II, Step 1 calc should be:

If(IsEmpty ( PK_jokeID );"0";
"0¶"&List(JokesSent::FK_personID))

[I omitted the all-important List() function.]

I also didn't explain why I add a zero to the list. This is so that the
relationship always works. If you just have the list of jokes and someone
has never been sent a joke (or a joke has never been sent to anyone) the
relationship will return a blank list. The zero makes certain there is
always a number to compare with.

"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote