1 Reply Latest reply on Jan 10, 2014 7:27 AM by philmodjunk

    Script for Adding a record to a join table from two related tables



      Script for Adding a record to a join table from two related tables


           Hi All,

           I am creating a DB for a non profit to track membership and contacts.  They also want to track course attendance.  i have a contacts table [Contacts] and a Courses table [Courses] and a join table to track the registration [CourseHistory].

           [CourseHistory] is set up with the following:

           {CHID} = unique identifier
           {FIDContacts} = Foreign key for contacts
           {FIDCourses} = foreign key for courses
           {Paid} = drop down of yes/no to indicate if they have paid yet for the course they registered for.

           The procedure:

           The person will contact to register.
           The receptionist will look up the contact first in the DB
           With the record of the person to register displayed, the receptionist will click a register for a course button

           I want to create a script that:

           Creates a new record in the Courses History Table and auto populates the current {CID} from [Contacts] into the {FIDContacts} field of the new record in the [CoursesHistory] table


           Opens a new layout to allow receptionist to search for a course for the person to register
           Once found, then click a register button that then adds the current {CoursesID} to the last record created in the [CoursesHistory] table.

           Any help would be greatly appreciated.

        • 1. Re: Script for Adding a record to a join table from two related tables

               And if the receptionist cannot find a course for which they can register, you are left with a Courses History table that is linked to the contact but not to any course. I would suggest not creating a new record in the join table until the user clicks a control to register the contact for the course.

               You might find the "check boxes" layout in this many to many demo file a source of ideas for what you want to do: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               If you are using FileMaker 12 or newer, open this file from the File menu to get a copy converted to the newer file format. The method shown can easily be enhanced in a number of ways to better facilitate the process of searching out courses for which to register the contact.