3 Replies Latest reply on Mar 14, 2016 3:46 PM by siplus

    Combining multiple records for one person

    jtamayo

      I have a database with 3 tables

      Events

      People

      Attendance (a join table for People and Events)

       

      The people table contains fields for basic info, including the person’s title (Assistant Professor, Associate Professor, Professor). If I create a record for someone and a few years later their title changes, I create a new record for them with their new title. (The reason for this is because we look at our event attendance by position every year.  So say in 2011 John Smith attended an event and was an assistant professor, then in 2014 he became an associate professor.  If I change the title on his original record to associate professor, it’ll show he was an associate professor in 2011 when he was really an assistant professor.)

       

      Now my question is how can I create a layout or run a report that will show me the event attendance records for a person no matter how many title changes they’ve been through?  Using the John Smith example from above, I want Filemaker to group all of events John Smith went to when he was both an assistant professor and an associate professor.



        • 1. Re: Combining multiple records for one person
          keywords

          Your description suggests that you need to review the data model. Whatever approach you take comes with its own set of issues and constraints, and part of the art of design is choosing which set of issues you are most willing to live with. However, duplicating a Person record is probably not the best way to address this need. You could consider:

          1.     A separate table of Titles, and a join table to link titles to people with from… and to… dates to delineate current titles from past titles.

          2.     Simply changing the title in the person record, but logging the change somehow (e.g. using NightWing's UltraLog, or similar) so as to maintain the person's history.

          3.     Whichever method you use, making sure that where changeable data is deployed (such as in event participation) it is posted by auto-enter or lookup, not just referenced, to make sure past records are not changed when data changes. (Note: this is really just a variation on the common "purchase price" issue in an invoice.)

          4.     If you decide to stick with your duplicate person method, then you could create an additional key field to link the two (or three, or four, or more) versions of the same person and then build your report based on that field rather than standard uniqueID field.

          5.     No doubt there are many other ways you could approach this requirement, but that will do for now.

          • 2. Re: Combining multiple records for one person
            stephensexton

            A simple approach in this case may be an auto-enter field in the Event table that populates with the title of the user/lecturer.  However, as per keywords, a separate table of Titles may be of value regardless of your

            eventual approach.  In this case, the auto-enter field in the Event table may populate with the TitleUUID instead of the actual title.

            • 3. Re: Combining multiple records for one person
              siplus

              keywords is right, you don't duplicate a person record just because he changed title.

               

              There is an easy solution to your problem - although it is not very pure from a data modelling pov: store the title in the attendance join table as well, together with the professor's ID. This way you can have actual data (read: title) in the people database and the attendance table itself keeps track of what the title was when they attended.