5 Replies Latest reply on May 17, 2016 12:34 PM by oxuser

    Many-to-many

    oxuser

      I'm a historian and I'm trying to work out a logical structure for my FM14 database. I am recording 4 different life events, each contained in separate tables, for individuals. The table relationships are one individual to many event 1, event 2, etc. I have also set up a number of other tables which record data such as archival references, occupation types, place/addresses which I'd like to be able to select from across all the events.

       

      Eg. Person A (Individuals table) is married (Event 1 table) in Place A (Place table) which I found in a source (Source table). In that same source I find another person, Person B, (Individuals table) who dies (Event 2 table).

       

      I'm having trouble with circular structures here and FM is generating several table occurrences when I try to link these '3rd level' tables. I am not an advanced user at all (used to use Access but some time ago) but while the rest of the database is operating smoothly, after many online tutorials I'm struggling to get what feels like this basic aspect right.

       

      Can anyone help me out here? I feel I'm not being very efficient in this.

       

      TIA

        • 1. Re: Many-to-many
          erolst

          oxuser wrote:

          I'm having trouble with circular structures here and FM is generating several table occurrences when I try to link these '3rd level' tables.

           

          Actually, it's less you than FileMaker having trouble with circular relationships; they are not allowed because every path expression must be unambiguous. That's why you are offered new table occurrences.

          Let's say you have Movie --< Actor >-- Person

           

          From the context of Movie, Person::fullName is unambiguous; there s only one path to follow – via Actor.

           

          If you add a Director table and try to add this, too, to both Movie and Person …

           

          Movie --< Actor >-- Person

                     \-< Director >-/

           

          FileMaker won't let you – because then the expression Person::fullName could be resolved via two paths. Which one should FM choose?

           

          This example bears directly on your problem – the best solution here is to make Actor a generic Appearance table and add a Type/Role or similar field. Consider this …

           

          oxuser wrote:

          I am recording 4 different life events, each contained in separate tables, for individuals.

          Why use different tables? An event is something that takes place at a certain time (and place, but let's ignore this) and has one or more involved parties. The only difference is the type of the event. (Note the similarity to the example above.)

           

          So instead of saying implicitly that an event is of type x because it is in table x, say explicitly: an event is of type x because a field “type” in my (single) Event table says so.

           

          That also means you don't need to (redundantly) re-create structure and code whenever you add a new event type; just add a new record to the EventType table.

           

          So a possible structure for your database could be:

           

          People --< EventParty >-- Event >-- EventType

           

          where > means “many“: an EventType will have any events of its type, an event can have many parties (people), a person can be involved in many events (via being an EventParty).

           

          HTH.

          1 of 1 people found this helpful
          • 2. Re: Many-to-many
            oxuser

            Brilliant. I was reworking everything when your reply came through and thankfully I'm working along the right lines in simplifying the event tables into one. I did find a workaround which was to create a new 'source line' table for each of the event tables but fear for my sanity when it comes to creating portals for each, so many thanks for your suggestion - I'm re-writing now.

            • 3. Re: Many-to-many
              oxuser

              A further query related to this database if I may... I have now set up an Events table incorporating all the fields I need but separated out into 4 different layouts so the relationships are simpler. I now find that more than one person can be involved in one event and vice versa, so I created a join table (Event Incident) populated by foreign keys from both People and Events and an event type field (event 1, event 2, event 3, event 4). My question now is how do I set up the layout to do the following examples:

               

              1. add an Event 1 instance to Person A

              2. add a Person B to the same Event 1 instance

              3. add a Person C to an Event 2 instance

               

              Does my layout have to be based on the Event Incident table? How do I as a user interact with this little join table? From my dashboard I planned to be able to go to the People layout, fill that in, then add an event from that layout. From that event, I then planned to add another person and perhaps add another event. Eventually I'd be able to see all the events associated with one person. Simple, right?!

               

              I've been using simple button scripts, (eg. open new layout [eg. Event 1], set the variable of the current layout's pkfield and apply it to the newly opened layout) so I can navigate across the layouts while linking the person's or event's id. However, I'm not sure how to deal with the join table now when it comes to recording new events/people.

               

              TIA

              • 4. Re: Many-to-many
                erolst

                Well, that was my original suggestion: since an event can have several involved parties, and a person can be involved with many events, you need

                 

                {EventType --< } Event --< EventParty >-- Person

                 

                but I would put the type of event into the Event table; event parties could play different roles within an event, but that wouldn't make it a different type of event.

                 

                So on a Person layout, you can use a portal that looks into EventParty (that is the list of events for that person) and displays the event data from the related Event records (where the type of the event would come from the EventType table, if you want to codify the types with a table, instead of just a value list).

                 

                As to the UI: I guess I would create and display two Cartesian relationships for Person and EventType (related to wherever you want to manage this from), make the selections using global $$variables and have a script create the necessary records:

                 

                • go to an Event layout and create a new Event of $$selectedEventType

                • store the new record's primary key

                • go to an EventParty layout and use a loop to process the $$selectedPeople list**, giving each new record one personID and the stored EventID

                • clean up the $$variables ;-) and return to the start layout

                 

                **that might be a “list” of only one, but this way you can use the same script regardless of cardinality, which is a good thing!

                 

                btw, if you have an EventType table, that would also be a nice place to store things like min. and max. number of parties, and use that such values for error checking in any scripts that create and edit events (or more to the point, their parties).

                • 5. Re: Many-to-many
                  oxuser

                  I'm getting there now... It's not quite as streamlined as I had dreamt of, but at least the relationships work and I can flip across from people to events while recording the events in the join table.

                   

                  Overall I'm enjoying learning new approaches, eg. global key/search, but need to focus on the fact that it's only me using it so no need for the fancy bells and whistles

                   

                  Thanks again for your advice!