In your Newsletter table, you have one record for each newsletter distributed and you have many contacts that receive the newsletters. This makes for a many to many relationship:
Contacts::ContactID = Contact_Newsletter::ContactID
Newsletters::NewsletterID = Contact_Newsletter::NewsletterID
Aivaras should create a new table named Contact_Newsletter and enter a new record for each ContactRating ≥ B and the "Sent" NewsletterID
And Not already in the Contact_Newsletter table (no duplication).
Can the Contact table see the Newsletter Table without additional relationships? I have heard of "tunneling" but never understood it.
You have the right idea. You've sketched out the script I would use.
I'll include a many to many demo file at the end of this post for you to examine.
The typical interface approach is to put a portal to Contact_Newsletter on the contacts layout and include any needed fields from Newsletters in the row of this portal. This produces a list of all Newsletters issued to that contact. Conversely, if you put that portal to the join table on Newsletter, you can use fields from Contacts to list all the contacts that received it.
This permits both viewing and editing the data, and you can create/delete records in the portal to make/destroy links between the two tables.
On the other hand, you could put a portal to Newsletters on the Contacts layout and this would also list all the Newsletters distributed to that contact, but you can no longer make/delete linking records in the join table.
And you can base reports on the join table to produce a summary report listing data from all three tables.
PS. "Tunneling", as I understand the term, is just using references to table occurrences that are more than "one remove" from a given layout's assigned table occurrence. In otherwords, there's at least one table occurrence between the layout's TO and the one you are referencing--and this happens a lot with many to many relationships as the Join often is the "between" TO.
Keep in mind that this is the same setup used in a classic Invoicing system with Invoices--<LineItems>----Products. You may not have realized it, but LineItems is a join table in a many to many relationship and a LineItems portal may "tunnel" to the Products table to display a description field from Products.
This can be a very powerful technique but should be used sparingly and with caution as it can produce very confusing results and involve undesirable performance hits if the number of records and/or intervening table occurrences get too large.
I went back to the "tunneling" reference book and your explantion is now gelling the concept.
In Aivaras's example:
Contacts TO can "tunnel" through Contact_Newsletter and get information and Action from Newsletter TO, without a direct Contacts::Newsletter relationship.
Yes, I have done this, but never realized I could use much more from that relationship than is apparent.
Thanks again for the demo file!