8 Replies Latest reply on May 19, 2016 11:27 AM by LambdaEnt

    Events

    LambdaEnt

      I am creating a (relatively) simple database with Events, and a list of possible Participants.

       

      1. Create Event record

      2. Select Participants from a current table, already populated

      3. Participant record shows what events have been "joined"

       

      Can't seem to get the relationships correct.

       

      Thank you!

        • 1. Re: Events
          SteveMartino

          You need 3 tables, Event, EventParticipant (join table) and Participant.

          PK from event related to FK on EventParticipant.

          PK from Participant related to another FK on EventParticipant

           

          In it's basic set up, EventParticipant would be a portal on the event table, where you can add or lookup participants

           

          How do you have it set up?

          • 2. Re: Events
            ErikWegweiser

            Hi. I'm going to presume you have not only multiple Events and multiple potential attendee Participant people, but also people who attend in multiple events. Therefore—and please pardon me if this is 'old hat' to you—you'll need an Attendee 'join' table to contain records that are at the very least every instance of one participant person attending one event. The Attendee table would have the event ID and the participant ID. A relationship from Participant to Attendee would show all the Events they've attended and a relationship from Event to Attendee would show all the participants of that one event.

            • 3. Re: Events
              erolst

              In case you're more visually oriented, both Steve's and Erik's suggestion can be graphically displayed as

               

              Event --< Attendee >-- Person

               

              so an Attendee record has exactly one related Event and one related Person record (since it describes a combination of these entities), while both People and Event records have (potentially) many Events attended and People attending, respectively.

              • 4. Re: Events
                LambdaEnt

                Yes, you are correct: Multiple Events and multiple potential attendee Participant people, but also people who attend in multiple events.

                 

                I've now created those relationships.

                 

                But the other information (address, phone #, etc) isn't populating into the Join table when choosing from a list of Attendees.

                 

                Also, First and Last name are separate fields, but I need a calculation field of Full Name (First & " " & Last) to be in the pick list. When I do that, not says field is not modifiable.

                 

                • 5. Re: Events
                  SteveMartino

                  Something isn't right, or complete.  I see on your graph all tables are showing many-to-many relationships.

                  Just adding a name/creating a record into the join table doesn't create a related record.  What process are you using.

                  Take a look at the Event Management starter solution and pick thru how it creates records, and how the tabkes are joined.

                  • 6. Re: Events
                    LambdaEnt

                    No. there is not a many to many relationship.

                    Events: Event ID <> Events | Participants: Event ID

                    Events | Participants: Singer ID <> Participant: Participant ID

                     

                    I am using a portal, and yes it does create a related record.

                    Please see the Layouts image showing that.

                     

                    Here is the file. There is just sample data in it.

                    user name: admin

                    no password

                    https://dl.dropboxusercontent.com/u/825107/Aftershock%20Events.fmp12

                    • 7. Re: Events
                      SteveMartino

                      Take a look.  I modified some things for basic functionality, but it needs a lot of work

                      https://dl.dropboxusercontent.com/u/71328624/Aftershock%20EventsSMmod.zip

                      • 8. Re: Events
                        LambdaEnt

                        That did it!

                         

                        I removed the unneeded fields from the join table, as you suggested. Also, changed the participant ID field back to the manually entered one — this is a unique number known to the participants and needs to be set.

                         

                        Kudos for the phone # mask...that was in my plan. Saved me a bunch of work.

                         

                        Thank you.