7 Replies Latest reply on Oct 20, 2011 9:34 AM by JamesHart

    Finding records in a many-to-many relationship



      Finding records in a many-to-many relationship


      Hi all, hope you can help.

      I am developing a database to keep track of our clients' computers, their serial numbers etc. I have tried to make the database structure as extensible as possible so forgive me if it sounds at all complex in terms of relationships:








      I have also set up link tables between the 'core' tables, as I wish to have many-to-many relationships as follows:



      In 'plain English':

      - People can be part of one or more organisations

      - Computers can belong to one or more people

      - Computers can also belong to one or more organisations



      At the moment I can browse and search by person or organisation. I can then view the devices directly associated with them, via the link tables and my relationship diagram.


      Now, let's say an organisation has a Dell PC. The ID of the PC is linked to the ID of the organisation that owns it, using the link table. So, it's searchable.

      One of its employees brings a MacBook to work, and we want to record that too. So, we link the MacBook to the person, rather than the organisation, because it belongs to that person explicitly. Semantically this makes sense.

      Suppose the person with the MacBook phones up with a problem and I want to look up the computer details. Although I can go to that person's record to locate the MacBook, equally I would like to be able to look up their organisation as well and access the Macbook's details that way. Relationships exist to link the MacBook to the organisation via the person, but I can't work out how to display both the direct and indirectly linked computers in a single layout.



      - I wondered perhaps if I can use a portal, but ideally I'd like to use a filtered list layout since it works much nicer with FM Go :-)

      - I think it perhaps needs an "OR" find request, saying "show me all the computers from these two link tables (computer-org & computer-person) where the ID of the organisation is X OR the ID of the person is Y"


      All help greatly appreciated. Thanks!

        • 1. Re: Finding records in a many-to-many relationship

          I think a filtered portal would be the easiest to implement.  I've been using portals with FM Go and not had any issues with FM Go (on iPads) dealing with filtered portals.  If you want one portal showing computer-org and computer-person you may want to use two occurences of the same join table for both.  You could use global fields for the organization and person filter criterea.

          • 2. Re: Finding records in a many-to-many relationship

            If you want to filter a list layout, Enter find mode, specify criteria, constrain found set, in a script can produce much the same filtering effect as a filtered portal does by omitting any records from the found set that do not match the specified criteria. The key difference is that previously omitted records that now match the desired criteria will not be automatically added back into the found set. You'd need to perform a straight up find again to do that.

            • 3. Re: Finding records in a many-to-many relationship

              Thanks to you both for replies so far.


              Mark - my preference is for a list layout. I understand your approach with the portals but somehow I prefer the idea of a scrollable filtered list layout, not least for ease of actually laying out the interface.


              Phil - I have almost achieved what you suggested. One problem which I can't work out:


              Attached is my relationship diagram with link tables. The 'root' is a table of Macs that we know about. They can optionally be linked to either a person or an organisation, hence the two 'pathways'. Then, I have extended the path such that if a Mac is linked to a person, we can find out if that person is in turn linked to an organisation. Thus we can view all the Macs in an organisation, even if a given Mac is linked directly to an individual.


              I select an organisation through a script. This sets the global variable $$selected_organisation_id, then takes us to a list of Macs showing related records. Thus we get all the Macs directly associated with that organisation. Next, the script 'extends the found set' where:

              $$selected_organisation_id = TEST_001_organisation#person


              My reckoning says this should look down the relationship path, find any people who work for the selected organisation, see if they have any Macs and then add them to my list. This does work.

              However, if I change the data in my mySQL table (I'm using an ODBC connector) such that a given person is linked to a different Mac, the list does not refresh, even after re-running the whole process from scratch. This puzzles me. The only way I can get the list to update is to close the file and re-open it. I think it sometimes also works after leaving it for 5 minutes or so.


              Is this related to my using of a global variable, or is there something fundamentally not right about my 'extend found set' find request?




              • 4. Re: Finding records in a many-to-many relationship

                It would have helped if you mentioned the (I assume) ODBC link to an SQL table in your original post as that would have provided a more complete picture here. My experience with ODBC is limited, but suspect that this may be a latency issue with how quickly FileMaker gets the updated info. I doubt that your script or relationships are a factor, they just don't see the new data in a timely fashion to display results based on the changes to the data in the external SQL table.

                • 5. Re: Finding records in a many-to-many relationship
                   Thanks again Phil - I have to say the ODBC link is generally so seamless I forget it's there! Apologies for not mentioning earlier. Last question then before putting this to bed - based on my relationship diagram and previous post, is the logic of my 'extend found set' request accurate in terms of the results I can expect to get? I assume this is the case since it appears to be working, but I think we've all created a layout or script that appears to work but later you find an exception!
                  • 6. Re: Finding records in a many-to-many relationship

                    The logic seems OK. IF you haven't already, you might play around with an Execute SQL script step to see if you can requery the data source without having to close and reopen the file...

                    • 7. Re: Finding records in a many-to-many relationship

                      All sorted - there wasn't actually a latency issue in the end, there was a problem with the way my script was running the find and reevaluating the globals. I would elaborate further but (a) I forget exactly the issue and (b) it was my own stupid fault

                      Net result: the logic and relationships do work in terms of my original post question, and the ODBC connector remains very impressive when using a mySQL back end. There isn't a latency issue.


                      Thanks for your contributions.