I'm trying to create a portal on my "incoming mail" layout that includes all "active" jobs related to the current email of "[email protected]"

It's trickier than it sounds at first glance...

Here are the 3 tables I'm working with.

-Client Emails = [email protected] (each email may be associated with multiple clients; example: John Doe & Jane Doe)

-Clients = John Doe (each client may have multiple emails; Example: [email protected] & [email protected])

-Jobs = "active" or "inactive" (each Job relates to a single client)

each email may be associated with multiple clients

In what way exactly - given there is no joining table between the two?

Your right, the emails are not related to more than one client.

However, the same email may occur within multiple clients.

Example

John Doe could have two related emails, [email protected] & [email protected] (these are only related to John Doe)

Jane Doe could have just one related email, maybe she uses john's home email address, [email protected] (this email is only related to Jane Doe)

Let me know if you need further explaination.

I'm afraid that's not clear. In your example, is there one record for "[email protected]" or two? If there's only one, by what means is it related to two clients?

There are two records in the Client Email table that contain johndoe@home, one record relates to John Doe, and the other relates to Jane Doe.

Well, if the relationship between Emails and Clients is based on matching e-mail address then all the king's horses will not be able to decide between John Doe and Jane Doe as the sender.

You could add a calculation field to the Emails table like =

Case ( IsEmpty ( ClientID ) ; List ( Clients::ClientID ) ; ClientID )

and use it as the matchfield to Jobs. In the current example, the portal to Jobs would show jobs of both John Doe and Jane Doe - until you select one of them and enter their ID into the ClientID field.