4 Replies Latest reply on Jul 26, 2012 9:16 AM by JimMac

    Assign specific record to a contact

    ultranix

      Title

      Assign specific record to a contact

      Post

      I am preparing a module in which I could track whether or not I sent my contacts (CONTACTS table) monthly newsletter.

      For that matter I created a table NEWSLETTER of 3 fields: Date, Text (Text of sent newsletter), status (replied, unreplied, called, etc.).

      Not all of my contacts are receiving that newsletter, only those, whose rating is B and above (A, A+).

      The main purposes of the module are basically two: 1) ability to know who received what and when 2) ability to count newsletters sent on each occurence (on every record entry in NEWSLETTER table).

      How do i set up database and link everything in order to achieve desired results?

        • 1. Re: Assign specific record to a contact
          philmodjunk

          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-----<Contact_Newsletter>------Newsletters

          Contacts::ContactID = Contact_Newsletter::ContactID
          Newsletters::NewsletterID = Contact_Newsletter::NewsletterID

          • 2. Re: Assign specific record to a contact
            JimMac

            Phil, I have had 3 cups of coffee on this one.Laughing

            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).

            If so...

            Can the Contact table see the Newsletter Table without additional relationships?  I have heard of "tunneling" but never understood it.

            Jim...

            PS:Pardon my jumping in on this one.  It is very interesting.Innocent

            • 3. Re: Assign specific record to a contact
              philmodjunk

              Jim,

              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.

              https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

              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.

              • 4. Re: Assign specific record to a contact
                JimMac

                As always, Phil, you provide a clear explanation that is easy to follow!Cool

                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!

                Jim...