9 Replies Latest reply on Jul 19, 2015 1:36 AM by PhilEvans

    Data Modelling



      Data Modelling & Joins


      Hi all,

      I'm coming back to FM after a few years away. It's moved on a lot since the last time I used it.

      Can anyone help with my modelling, relationships and join tables as I'm struggling to figure out the best way to achieve what I want to achieve.

      My tables are

      • Clients - corporate entities
      • Attendees - who are staff/employees of Clients and who attend Sessions 
      • Programmes - educational and training programmes that comprise different
      • Sessions - generally two day events within a programme
      • Staff - my staff who teach at the sessions - there are multiple staff at each session


      • Clients have multiple Attendees
      • Clients subscribe to multiple Programmes
      • Programmes have multiple Sessions
      • Sessions have multiple Attendees
      • Sessions have multiple Staff members

      At a later date we will also be adding surveys and questionnaires that Attendees will be asked to complete pre and post Session. But we'll ignore that for now.

      I need to be able to report on

      • which Attendees (and display related Client data) attended which Sessions (and therefore which Programmes)
      • which Staff members lead which Sessions

      I've tried creating join tables but am unsure where the chain terminates. I keep getting the error message "there cannot be more that one relational path between any two tables on the graph etc"

      Can anyone suggest the best way to achieve what I want to do?

      Screen grabs attached

      With thanks



        • 1. Re: Data Modelling & Joins

          You may want to work through the tutorial on Table Occurrences found here: Tutorial: What are Table Occurrences?

          Each "box" in your screen shot is a "table occurrence".

          I suggest that you make two occurrences of your Clients table as the simplest way to remove the "loop" in your relationship graph that is the source of this warning message. (If you click OK, FileMaker creates an extra occurrence for you to make the relationship possible but without any such loop. But an extra occurrence of Clients looks like the better option than letting FileMakaer create the duplicate TO here.)

          • 2. Re: Data Modelling & Joins

            Thanks Phil. I'll take a look now

            • 3. Re: Data Modelling & Joins

              Hi Phil,

              Is this looking more like it (red boxes are the TOs)?




              • 4. Re: Data Modelling & Joins

                It's not what I recommended, but if it works for you...

                But shouldn't that bottom right table occurrence refer to an occurrence of Sessions? you seem to have two occurrences of Sessions on either side of your join table.

                What I recommended was using two different table occurrences of Clients.

                Other issues:

                You show a match from clientName to clientID. This should not be. ID's should be auto-entered serial numbers of UUIDs generated from the Get ( UUID ) function. A name should not match to an ID.

                And you seem to have a join between Clients and Attendees. Can an Attendee be a client or representative of more than one client? If not, then you don't need a join table between clients and attendees.

                Also, your data model is set up so that each program is unique to a particular client. Is that really the case or might a given program be presented to more than one client (but perhaps in different sessions?).

                • 5. Re: Data Modelling & Joins

                  Hi Phil,

                  Thanks for your help.

                  I think I've over-thought and over-complicated this so I have taken it back to basics. I have also changed some of the terminology and naming conventions to make it more user friendly for our staff.

                  One more question though. As you can see from my Tables and Relationships, each Service_User should only ever have one ReferralForm. Depending on the Service_User type, a user may never have a ReferralForm.

                  How can I restrict a Service_User from having more than one ReferralForm? Is that something that can be created through relationships or is it something I would need to script into the user interfaces to check to see if a ServiceUser already has a ReferralForm?




                  • 6. Re: Data Modelling & Joins

                    I think that your question actually is either:

                    An organization can never have more than one referral form--in which case you can set up a text field in Service_Users with this auto-enter calculation: _OrganiazationIDfk & "|" & _ReferralFormIDfk and then set a Unique values validation on this text field.


                    An organization can never have more than one referral form for a given Services record--in which case you can set up a text field in Service_Users with this auto-enter calculation: _OrganiazationIDfk & "|" & _ReferralFormIDfk & "|"_ServiceIDfk.

                    There are other, interface level methods that can be used to make it impossible to select the same ID combination more than once as well.


                    • 7. Re: Data Modelling & Joins


                      No. The Organisation table can have multiple Service Users. The Referral Form relates to the service user and not the Organisation.

                      So I'm trying to figure out how I can create a Service User, then, at a later date, complete a Referral Form that is unique to that user. A Service User would only ever have a maximum of one related Referral Form.




                      • 8. Re: Data Modelling & Joins

                        The simplest then would be to get rid of the seperate referral form table and just move those fields into the Service_User table.

                        If you choose not to do that, you can set up a layout based on Service user and include the fields from the Referral Form layout on this layout just as though the fields are part of the Service User table.

                        • 9. Re: Data Modelling & Joins

                          Thanks. I appreciate the advice. Merging the two tables sounds like the best way forward. I can then separate the contact data from the referral form data by creating separate forms for each "section"

                          Thanks again