1 Reply Latest reply on May 16, 2011 11:20 AM by philmodjunk

    relational databases

    ALynch

      Title

      relational databases

      Post

      Hi - I am trying to create a relational database and have not had that much experience with FM Pro although am pretty computer literate.  My tables I have set up as follows:

      TABLES:

      Events Master     Contacts Master     Attendance

      All my data needs to be imported by csv files from excel.  The Events list has an Event ID # for each event. 

      What I want to create is a relationship from the Event table to the contacts table that auto fills the events details in the contacts table.   My contacts spreadsheet also contains the event ID. I have set up an auto fill function on each of the event master fields to fill into my contacts  table but it is not filling any of the info in when I import my csv file.  Below is a screenshot of my relationship graph.  Maybe someone can tell me where I am going wrong.

      Also ecause participants attend a number of events, I want only one contact entry for someone who may have attended a number of differnt events.

      I tried with the starter solution but it was way too complicated and I did try to find some step by step instructions on how to complete the events starter solution.  If anyone knows where I can find these please let me know.  I think though my problem is that I am uploading the information from spreadsheets and not just directly entering the information into the database.

      Any help would be greatly appreciated.

      A

      Picture_4.png

        • 1. Re: relational databases
          philmodjunk

          You've got several issues here that need fixing. First you should have this table structure and relationships:

          Events----<Enrollment>----Contacts

          Events::EventID = Enrollment::EventID
          Contacts::ContactID = Enrollment::ContactID

          You should not include the other fields in your relationship as there is no need for this and won't make it possible for one person to be enrolled in multiple events.

          To enroll contacts in an event, place a portal to Enrollment on your events layout. Put Enrollment::ContactID in this portal row formatted with a drop down list or pop up menu of ContactID's in column 1 and contact names in column 2. Selecting a contact from this list enrolls them in the event. A status field in the Enrollment table can then be used to record who actually attended the event if you need that data. You can place additional fields from Events in this portal row to show the name, location, date of the event if you choose to do so.

          Here's a demo file that matches contracts to companies in the same type of many to many relationship. If you were to rename the two main tables "events" and "Contacts", it would match what I am describing here: 

          http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html