I have a portal that shows the records from an email table that matches the current record email address. So for example if I am on jon doe i have a portal that shows all the emails sent to his email address. The mathing relationship is email from the contact table matched to the to field of the email table. This shows the records just fine when I have only one email address in the to field for an email. But as soon as I have more than one email in the to field, it does not show in the portal. I think this is because it is looking to match all the email addresses in the to field to one record only. How can I set the relationship so that if an email address in the to field exists in the contact field, it will find that and show in the portal. So if jon doe and jane doe are both in the to field and then I go back to jon doe's contact file and look at his portal it will show the email with jon doe and jane doe?

Thanks!

Is the separator in your email addresses on the 'TO' line a semi-colon? Whatever your separator between emails, use calculation to change it to a carriage return which will place each email on a separate line. Then when you relate to this calculation, FM relates each line as if it was a separate record. So in your email file, create a calculation with:

Substitute ( TrimAll ( to ; 0 ; 3 ) ; ";" ; ¶ )

... and change your relationship to:

Contacts::email = emails::calculation you created

However, I should caution against using an email address to relate records. What if the contact changes emails? You would lose the ability to view all of their prior emails. In general, it is best to use unique auto-enter (meaningless) primary keys to bind all relationships. In that way, a change in information won't break your relationships.

Edited May 22, 201015 yr by Guest
Added relationship line