Help, i'm really stuck!! Hope someone can help!

I have created a many-to-many relationship between two files (Clients and Interests).

The files and relationships look like this:

CLIENTS: ClientID (Auto number), Email (Text), FName (Text), LName (Text) etc.

Relationship: ClientInterests (ClientID::ClientID)

INTERESTS: InterestID (Auto number), Interest (Text)

Relationship: InterestedClients (InterestID::InterestID)

RELATED: ClientID (Number), InterestID(Number), Client_In (Calculation - ClientInterests::Email), Interest_In (Calculation - InterestedClients::Interest)

Relationships: Clients (ClientID::ClientID), Interests (InterestID::InterestID)

I think my relationship set-up is fine. I have a portal in CLIENTS and one in INTERESTS. I am displaying Interest_In in the CLIENTS portal and Client_In in the INTERESTS portal.

Hope you're still following?!!

So far I can add interests to the portal (using a related value list) in CLIENTS and all fields show in RELATED apart from InterestID. The Client email does not show in the portal in INTERESTS either.

When I delete records in RELATED, these are removed from the appropriate portal in CLIENTS but when I remove a portal entry in CLIENTS, a record still remains in RELATED.

I want to be able to control all changes within CLIENTS.

i.e. if I add an interest to a CLIENTS record I want the clients email to appear in the appropriate record in INTERESTS.

If possible, RELATED should be "invisible" to the user and INTERESTS can be viewed but not edited.

Hope this is clear. Any help is much appreciated.

Thanks.

In order to get Client email to show up from the Interests file, in FM6, you need to add a calc field to the Join file (which you're calling "Related"). The calc field just shows the value from Client::email. That way, a portal to the join file can pull data all the way from the Clients file. (In FM7, this kind of chain-relation is automatic. Whee!)

You say that when you "remove data" from the portal in the clients file, the join record still exists... I wonder what you're doing. If you simply go into the field and DELETE the text, then that doesn't remove the join record. You'd need to set up a button in the portal to "Remove this interest from list", and define it to delete portal row.

Thanks for your reply.

I got the deleting part working - the related records are now being removed.

I still can't get the first part working though. I already have a calculation field in RELATED (Client_In). Is that what you meant or do I need another one?

It's not totally necessary but it would be nice to have all the appropriate client emails appearing in INTERESTS.

Thanks for your help