8 Replies Latest reply on Jan 25, 2010 11:02 AM by philmodjunk

    Event Management - Problems

    Dubs

      Title

      Event Management - Problems

      Post

      1.) I am fairly new to Filemaker Pro and am using Filemaker Pro 7

      2.) I am on a PC

       

      I have created a database to contain 4 tables

       

      Organisation - Venue - Event - Contacts

       

      Likewise I have created 4 layouts each the same as above

       

      I have then created 4 unique ID's fields for each, ie Organisation ID, Event ID etc to try and link them

       

      Now I can display 1 event  under the contact by matching the event ID but there can be more than one event relating to the contact so how do i display the other event/s?

       

      On the contacts layout I am trying to display all of the events relating to that contact but, I can't. Each event has a unique EVENT ID but how can i display the multiple events to the one contact, on the Contacts Layout?

      Forgive me if I have explained this poorly and feel free to ask any questions.

       

       


        • 1. Re: Event Management - Problems
          philmodjunk
            

          You've encountered the classic many to many relationship issue that all relational database developers encounter at some point. For one event you could have many contacts and for one contact you can have many events. The solution is to create a "join table" that links your contacts and events tables like this:

           

          Contacts::ContactsID = Events_Contacts::ContactsID

           

          Events::EventID=Events_Contacts::EventID

           

          1 record in Events_Contacts one contact attending one event. Events_Contacts need only have two fields, EventID and ContactsID, though you can add others if you need to document other information specific to that one contact attending that one event. (The price paid for admission might be one example.)

           

          On a contacts layout, you can place a portal to Events_Contacts and add fields from Events to the portal. This portal will then list all events linked to the current contact record.

          On an Events layout, you can place this same type of portal but add fields from Contacts to produce a list of all contacts linked to the current event record.

           

          Make sense?

          • 2. Re: Event Management - Problems
            Dubs
              

            Thank's Phil much appreciated although I still do not understand completely. I also realised that I did not explain my situation very well.

             

            We are a company supplying technical services to events in and around the UK, the contacts field contains the contact information of the people holding the events, contact people at the organisations who are holding the events and contact information for the people who manage the venues, in some cases these contact people are the same for the event/organisation and venue.

             

            I am actually building quite a complex (well to me atleast) database as there are so many relationships. I have four seperate tables with information which is very straight forward

             

            Events Table, Venue Table, Organisation Table and Contact table

             

            The Events Table is straight forward except it needs to display the organisation holding the event from the organisation table and i was going to link this by ORG ID, however, it also needs to display contact details from the Contact Table but there are multiple contacts to be displayed for one event. I was going to link them by the unique CONTACT ID, but I can't for obvious reasons.

             

            The Venue Table is again fairly easy except it needs to display multiple contacts for that venue from the Contact Table and again i was going to link via the CONTACT ID, but it also needs to display the multiple events held at that Venue from the Venue Table and again i was going to link them via the VENUE ID.

             

            The Organisation Table is very easy as it can display the mutiple contacts via the CONTACT ID as the contact will only ever be related to the one organisation (I hope), but i fall over when it needs to display the multiple events from The Event Table as again there can be more than one event related to that organisation.

             

            The Contact Table can display all its own fields as you would expect and can display the organisation related to that Contact via the ORG ID but again it needs to display the multiple events that the individual could be involved in.

             

            I am pretty sure all these problems are exactly as you described in that it is down to multiple relations, but I am not totally clear on the solution. 

             

             

             

             

            • 3. Re: Event Management - Problems
              philmodjunk
                

              You've got multiple issues here with more than one design technique needed to implement them.

               

              "The Events Table is straight forward except it needs to display the organisation holding the event from the organisation table and i was going to link this by ORG ID, however, it also needs to display contact details from the Contact Table but there are multiple contacts to be displayed for one event. I was going to link them by the unique CONTACT ID, but I can't for obvious reasons."

               

              But you can do this quite easily. Your relationships would look like this:

              Events::OrgID = Organisation::OrgID = Contacts::OrgID

               

              As long as a contact record can only belong to one organisation, this will work. On your events layout, you should be able to place a portal to Contacts. Keep in mind that the Relationship graph may force you to make additional table occurrence boxes that all point to the same data source table and you have to pay attention to that details so that layouts and portals point to the correct table occurrence.

               

              I'm not sure I follow this: "The Venue Table ... needs to display multiple contacts for that venue from the Contact Table " Are these the contact records for the organisation that owns/manages the venue? If so, then you just need a separate field, MgrOrgID, for example to link it to a record in organisations.

               

              The last item, linking contacts to events is a many to many relationship and that will require the technique described in my first post.

              • 4. Re: Event Management - Problems
                bbgnyc
                  

                Hello:

                 

                Reading your questions and responses, I believe you are the right people to ask.  I'm am new to Filemaker Pro 10 and making my way through Event Management.  I have set up my Contacts form to the way I would like it and have been playing around with the Events Tabs.

                 

                This is what my problem is and what I need:  I want to create a database where I can put in events and register contacts for individual events.  So if I go to Event #1, I see who  I've linked to this event and their various statuses.  I also want to be able to go to an individual contact's record, and see what events the person has been to.  I know I probably have to create a tab within the Contact record.

                From reading some of your responses below, I think I am ready to start entering in contacts - but want to be sure I can link them correctly.

                Also - one the Events tab, when I click on View List of Events - how can you edit or rearrange that page? It doesn't have the layout I would like to or all the info I want shown.

                Reports and creating unique shortcut function buttons => my next big task but down the road

                Any help you can provide would be much appreciated since I am about 1 day into this software program.

                Thanks,

                T.





                • 5. Re: Event Management - Problems
                  philmodjunk
                     To modify a layout, you can select layout mode from the view menu or press its keyboard short cut. Just make frequent copies of your file so that if you mess up, you can start again with a different copy of your file.
                  • 6. Re: Event Management - Problems
                    Dubs
                      

                    You've encountered the classic many to many relationship issue that all relational database developers encounter at some point. For one event you could have many contacts and for one contact you can have many events. The solution is to create a "join table" that links your contacts and events tables like this: Contacts::ContactsID = Events_Contacts::ContactsID Events::EventID=Events_Contacts::EventID 1 record in Events_Contacts one contact attending one event. Events_Contacts need only have two fields, EventID and ContactsID, though you can add others if you need to document other information specific to that one contact attending that one event. (The price paid for admission might be one example.) On a contacts layout, you can place a portal to Events_Contacts and add fields from Events to the portal. This portal will then list all events linked to the current contact record. On an Events layout, you can place this same type of portal but add fields from Contacts to produce a list of all contacts linked to the current event record. Make sense?

                     

                    Absolute star thanks for this, finally got around to doing this outside of work at home and all working perfectly now for the rest of the relationships etc.

                    • 7. Re: Event Management - Problems
                      bbgnyc
                         Thank you.  Where do I go to create that relationship?  Do I go to File/Manage/Database and select the Relationship tab?
                      • 8. Re: Event Management - Problems
                        philmodjunk
                           Yes. In filemaker, the relationships tab is the only place you can create a relationship.