1 Reply Latest reply on Nov 25, 2011 9:33 AM by philmodjunk

    Automatic population of a join table

    JasonLindop

      Title

      Automatic population of a join table

      Post

      Hello,

      I have a join table, called attendance, that combines contacts and events (because one event can have many contacts, and vice versa). I can manually type in the contact and event primary keys into the attendance table (i.e., the customer primary key from the customer table goes to the customer foreign key in the attendance table). This works fine and, based on this, I can then create a portal in a given contact's record, showing what events they have attended. 

      But this is prohibitively slow, and I need to populate many attendance records at once by brining in a large group of records from the contacts table, combined with an event from the event tables.

      Any clues on how to do this would be much appreciated (it seems the process may require the use of a scrip, an area I'm only now learning about). 

      Thank you,

      Jason

        • 1. Re: Automatic population of a join table
          philmodjunk

          Yes, the use of a script to loop through a set of Contacts records, creating an attendance record for each in the Join table, linking them all to the same event record would seem to be the best approach.

          You don't indicate how you would select your group of contacts for that. I will assume that you have performed a find to locate those contacts on your contacts layout just before running the following script. You would also need to have specified the contact ID in a field on your contacts layout. I'll name that field, Contacts::gSelectedEvent. I will also assume that gSelectedEvent has global storage specified as that makes the script a bit simpler to create.

          #Should be performed from the contacts layout after pulling up a found set of contacts and selecting an event to which to assign them
          Go To Record/Request/Page [first]
          Freeze Window
          Loop
             Set Variable [$ContactID ; value: contacts::ContactID ]
             Go To Layout [Attendance]
             New Record/Request
             Set Field [Attendance::ContactID ; $ContactID]
             Set Field [Attendance::EventID ; Contacts::gSelectedEvent //this step only works if gSelectedEvent has global storage specified]
             Go To Layout [Contacts]
             Go To Record/Request/Page [next ; exist after last]
          End Loop

          Note that this script does not check to see if the contact has already been linked to the selected event. If this is a possibility, a test to check for the existance of such a record in attendance would be needed to keep from adding an extra record.

          Linking contacts to an event can also be done from what looks like a group of check boxes inside a portal of contacts. Portal filtering can be set up so that the user can pull up different groups of contacts. You can then click a check box in this portal to create a record in attendance--selecting that specific contact for the specified event.

          You can find an example of that approach (but without the portal filtering) in this demo file that, coincidentally, happens to demo linking events and contacts in a many to many relationship: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html