7 Replies Latest reply on Mar 10, 2016 7:44 AM by beverly

    many_to_many misery




      I need to keep track of training certificates (issued by our supplying companies). Every certificate may authorize one or more of our people to give user training or to perform product repairs, or both. And, that certificate may authorize them to do that on one or more of that company's devices we have installed at customers. Hospitals in my case.


      So, I have certificates, I have authorized people (engineers), and I have devices they are authorized for. The basic data entry form would be a layout based on certificates with portals for adding the people trained and devices trained on. The aim is to create overviews of devices and exactly who is authorized to train users on them and/or to repair them and the create overviews of our engineers and the devices they have valid authorizations for and when these expire etc. 


      Doesn't seem hard, but where I am pretty OK at layouting, valuelisting, and scripting things together, I seem to lack the brain cell type for putting the right relationships together.  I just don't see it.


      Attached is an image of a setup that I have attempted to join together in several ways but without the result I am hoping for.  As many persons may be (through those certificates) holding authorizations for many devices, I guess I need a join table (or more?). That is where I get stuck.


      So I am turning to the community for directions that will put me out of my misery.




        • 1. Re: many_to_many misery

          I would recommend you back up, if you haven't already done this, and use an actual ERD design tool rather than rely on the Relationships graph for data modeling for anything other than the simplest data design. A data model is a time-consuming portion of any "real" application and needs to be done using a tool for that purpose.


          Using SQL Editor (an inexpensive tool that runs on the Mac), you can (a lot) more easily do data modeling. While you model the data, the tool generates the DDL for you as well.


          And, YES, SQL Editor works with FileMaker!


          MalcolmHardie Solutions


          Good luck.




          P.S. Below is a simple M:M design I did in SQL Editor


          • 2. Re: many_to_many misery

            to solve this problem you need to think in the data layer instead of the presentation layer..

            Many to many entity relationships are usually solved with a join table.Many-to-many (data model) - Wikipedia, the free encyclopedia

            • 3. Re: many_to_many misery

              I meant to also include in my posting above that SQL Editor will CREATE the FileMaker database for you once you model it.


              Navicat and other much more expensive tools also do data modeling, but SQL Editor is the only one I know that works specificially with FileMaker (Oracle, MySQL, SQL Server, ...).


              In fact, here are SQL Editor's supported databases:

              - m

              • 4. Re: many_to_many misery

                Good morning donselaar,


                I hope your day is going well. I think the relationship example that Morkus provided is a good strategy to use. I've created several solutions that have very similar goals to the ones you described. In those, the layouts for certification documentation/reports were primarily based on the training event table - studentcourse table in Morkus' example. It was also helpful for me to approach these projects with the thought that the student - authorized people, in your case - was the central component or hub of the design. Devices change and so do applicable training courses, but the student and their associated training events never do. Have a great day!


                God bless,



                • 5. Re: many_to_many misery

                  Wow! this looks promising. Have you tried it?


                  • 6. Re: many_to_many misery

                    Hi Bev,

                    If you mean have I tried SQL Editor, yes. I've been using SQL Editor for years as well as data designer in Navicat (or Erwin for large Oracle projects).


                    SQL Editor is inexpensive, has been around quite a while and gives a data modeler a real tool to model a database.


                    I worked with the developer of the product a while back to fix a FileMaker issue it had creating the actual FileMaker database. It worked fine after that update.


                    Post back your comments on the tool. (Other than being a registered user, I'm not associated with SQL Editor company in any way.)




                    - m

                    • 7. Re: many_to_many misery

                      I haven't tried it, but it does look good.