1 Reply Latest reply on Jan 23, 2012 11:02 AM by philmodjunk

    Checkboxes and multiple relationships

    MichaelRiordan

      Title

      Checkboxes and multiple relationships

      Post

      I am a newcomer to FileMaker scripting, I haven't been able to find an answer anywhere.

      I have a database which records details of events, and details of people who sign up to these events.

       

      • I have two tables.

       

      1. tblPeople) records details of people who sign up to an event.
      2. tblEvents records details of the events (date, time, place, etc.)

       

      • There is an = relationship between field tblPeople::eventTitle and field tblEvents::Title. In tblPeople

       

      • tblPeople:eventTitle is a checkbox field which takes values from a value list populated by records in tblEvents::Title

      I want to use FM11's email function to send emails to people in tblPeople, and I want to include in each email sent a list containing details of each event they have signed up for, taken from records for those events contained in tblEvents.

      Using FM's send email function to send the email, I enter the following in the Message box:

      "¶Dear " & tblPeople::Name & ",¶¶¶You have been signed up for the following events:¶¶" & tblPeople::evnetTitle & "¶¶You have specified the following dietary requirement: ¶¶" & tblPeople::Dietary requirements¶¶You have been signed up for the following events:¶¶".

      Here tblPeople::eventTitle quite correctly returns a carriage return-seperated list matching the check boxes selected in tblPeople::eventTitle.

      If I now add a field from the tblEvents table, say tblEvents::Date, the email which is generated only contains the contents of the record which matches the first checked box in the tblPeople::eventTitle field. 

      My question is how do I get it to return a result for each checked box?

      Sorry again if this sounds elementary, I haven't been able to find an answer anywhere. Presumably there is some simple code that repeats the action for every checked checkbox?

        • 1. Re: Checkboxes and multiple relationships
          philmodjunk

          You have a structural problem with your database. Many people need to link to many events--what we call a many to many relationship. While a return separated list of values can be used for this, it's not nearly as flexible as using a join table instead:

          Events----<Event_People>------People

          Events::eventID = Event_People::EventID
          People::PeopleID = Event_People::PeopleID

          And note that I didn't use the name of the event in these relationships. Consider what complications ensue if you have to change the name of an event after creating records that are related by that event title...

          To answer your specific question, you can use the list function to pull up a return separated list of dates from the events table. List ( Events::EventDate )

          Here's a demo file that coincidentally uses events and contacts in a many to many relationship that you may find useful to look over for ideas on how to set up a join table for your database: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html