Portal Relating to two different tables & multiple criteria

[Donkick]

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)

[comment]

each email may be associated with multiple clients

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

[Donkick]

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.

[comment]

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?

[Donkick]

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.

[comment]

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.