Lookup Table - Which way to go?

[AlphaBravo]

Hi There,

I'm having some woes with what seemed like a simple idea at the time. I'm using Filemaker Pro Server 8 on Windows.

My database has uses 4 tables that are causing the woe: Contacts, Companies, Addresses, and Lookup.

There are several many to many relationships involved: a Company can have many Addresses as well as many Contacts, A Contact can have many Addresses, etc. The Lookup table is used to track these relationships and I have scripts that add ID's to the Lookup fields to make this easier to maintain.

Here's where I run into trouble: on the Contacts and the Companies layout, there is a Portal to Addresses that displays all the related Addresses for the Company or Contact. I want to track two optional values for each of these Address relationships: 'Label' and 'Use For Gift'. Unlike the Address info itself, Label and Use For Gift are fields that are specific to that particular intersection of Contact and Address, or Company and Address. For example, the same Address shared by two Contacts should be able to have differing Labels for the Address, etc.

I assumed that if I put these fields on the Lookup record itself, that would be the perfect spot for them. However, although the portal on a Contact or Companies page can use the Lookup table to display the appropriate Address info, fields on the portal for Label and Use for Gift don't always relate to the proper Lookup record 'going the other way'...

I can get this to work the way I want if the portals display records from the Lookup table instead of the Address table... but then I get empty portal rows for the Lookup records that establish relationships between only Contact and Company and not Address.

How can I get Label and Use For Gift fields that apply uniquely to these specific Address/Company or Address/Contact relationships, and portals that only display Addresses?

The attached file (development username and password is 'admin') shows the Label and Use For Gift fields being on the Lookup table, and the portals on Contact & Company display data from the Lookup table. So you can see I get the desired effect with my Label and Use For Gift fields, however I have the extraneous rows for the other relationships described by the Lookup table...

Thanks for reading all this and for any advice or help you can lend! Have a great day,

-Andy

Contacts.zip