I've just discovered how wonderful self - join relationships are.

I have a database of activites, to display today's activities I just
have to have a global field with today's date and make a self- join
relationship by relating that to the date filed.
Easy.
Wonderful

Then it gets trickier.
Each activity has a contactID field through which is related to a
contacts DB.

I want a portal that shows all today's activities, and for each
activity, some data from the related contacts DB.

So I create a self-join relationship, as above, to get today's acctivities.
I make a portal, based on this relationship - which shows me all today's
activities.

That works OK, but I get stuck addding the fields from the related
contacts DB. If I have a relationship based on the contact ID it
doesn't work. I guess that's because the "activites" side of it doesn't
have anything meaningful in it.
I think I need a relationship that matches BOTH the self - join on date,
and the relationship with the other DB based on contact ID.

But I can't work out how to do it.


ANy ideas please?

ta

Assuming you have the tables ACTIVITY and CONTACT. In ACTIVITY you
have the field "GlobalDate". You created another table occurrence of
ACTIVITY and called it
ACT_ACT GlobalDate XX Date
In the Activity table you have a layout with portal records from
"ACT_ACT GlobalDate XX Date" which shows activity data but you want to
include contact data.

Here's what you do:
- create another table occurrence of contact and call it
ACT_ACT_CONT GlobalDate XX Date_ID Cont XX ID Cont
- link the contact ID in "ACT_ACT GlobalDate XX Date" to the contact ID
in "ACT_ACT_CONT GlobalDate XX Date_ID Cont XX ID Cont"
- drag contact fields that you want to display straight into the
activity portal but take them from the table occurrence "ACT_ACT_CONT
GlobalDate XX Date_ID Cont XX ID Cont"

You can now edit the contact information in the activity portal (this
wasn't possible in earlier versions of FM)

FP wrote:

Got it. creating another occurrence of the contact table was the step
I had missed before.

Thanks.