8 Replies Latest reply on Jun 4, 2010 8:35 AM by philmodjunk

    Working with Many to Many Relationship Tables

    tgold

      Title

      Working with Many to Many Relationship Tables

      Post

      I am new to FileMaker.  I have done extensive databse work in AlphaFive, but have little hands on experience in FileMaker.  I have a complicated database schema which has many overlapping relationships.  Unfortunately, none of the books or forum questions seem to address (at least clearly for me) how to do the following.  I would greatly appreciate the pointers.

       

      Specifically, I need to create scripts for buttons to perform the following functions on what I will refer to as  “Relationship” portals/browses:

       

      “Add Relationship”.  Each relationship portal has a many-to-many joining table linking the current “core” record to another “core” record in another table (i.e. current “Contact” to “Company” or current “Contact” to “Property”, etc.).  The relationship is created by placing both the ID Key from the current core record and the ID Key from the “related” core record in the joining table.  Also contained in that joining table is the “status” of the relationship (i.e. “Active” a logical field – 1 or 0) and on some occasions, the type of relationship (i.e. “Main Company” a logical field – 1 or 0).  To “Add a Relationship” a new record would need to be created in the joining table, however, first a search would need to be performed to make sure that the record exists and if not, the ability to add a new record should be triggered (note: these would be the same sequence and steps used to enter the “core” record from any other location in the database as well).  Once the core record is added (if necessary), the script would enable the user to add the related core record to the joining table and any logical selections (i.e. “Active”, “Main”, etc.) could be made at that time.  This action would be launched by clicking on a button adjacent to the associated portal.

       

       

      “Edit Relationship”.   To “Edit a Relationship” the existing record in the joining table would need to be opened.  This will ultimately be restricted to specific user groups through database security controls so that errors are limited.  If the “Edit” needs to take place in the related “core” table, the ability to modify the detail of the associated core record through a modal window would be necessary.  This action would be launched by clicking on an edit icon on the portal row associated with the related core record. 

       

       

      “Delete Relationship”.  To “Delete a Relationship” the existing record in the joining table would need to be deleted from the database.  This will ultimately be restricted to specific user groups through database security controls so that errors are limited.  In actual process a relationship would only be deleted if it was created in error as most cases would require simply a change in “status”.  Still debating whether the ability to delete the actual core record from this location is needed or not.  This action would be launched by clicking on an icon on the portal row associated with the related core record.

       

       

      “View Related Record Details”.   To view the details of each associated core record in the joining table through a modal window.  This action would be launched by clicking on a specific column in the related core record in the portal (i.e. “Company” as a field in a column - not the portal row record).

       

       

      “Active – Inactive – All”.  Ability to filter a portal based upon the “Active” status of a related core record.  The active status is kept in the joined table as a logical field.  Clicking on a radio button selector seems to be the simplest user interface.  Default would be “Active”. 

       

      Thanks for any "simple" directions!!!

        • 1. Re: Working with Many to Many Relationship Tables
          FentonJones

          Stopping duplicate entries would require that you know what the entry is going to be before the actual join record is created. The way to do that would be to enter the data into a global field(s) first. A join table has 2 ID fields; from a parent table's point of view, the parent and the other whatever. Since you have the parent's ID, you'd need to enter the other's ID into a global field (field with global storage). 

           

          The global ID field could be a drop-down or pop-up menu. How well that works depends on how many would be in the list. If a lot, then you'd need a better mechanism to choose from; another filtered portal, another layout perhaps.

           

          In any case, once you have the other table's ID, and commit the record, a relationship to the join table based on the parent ID and the global other's ID would tell you whether you have that join or not.

           

          You could then have a button to create the new join record, using the 2 IDs, via a short script. Set the parent ID into a script variable first. (the global ID remains available as is). Go to a layout of the join table. New Record, set the IDs into their fields. Commit Record, then return to the original layout. Active would be an auto-enter of 1.

           

          Viewing related record details, in a separate "modal" window. FileMaker does not have modal windows. But it can be faked by using the Loop, Pause, End Loop steps. It's a little tricky (but not much). You need a button on the "details" layout, with the button attachment option to Halt the current script; easy to get stuck in the endless loop if you forget to do so.

           

          Edit and Delete would also be scripted, allowing only certain Privilege Sets to run the critical steps. 

           

          It is possible to "hide" buttons, even in a portal, either via a calculation (buttons don't show, but are still clickable), or via a filtered relationship targeting a "constant" field (buttons don't show and are not clickable).

           

          The All or Active only filter on the portal could be built in either FileMaker 10 or 11, but easier in 11. So state your version.

          • 2. Re: Working with Many to Many Relationship Tables
            tgold

            Thanks so much for your response.  Unfortunately, I may be a little too green to handle the solution without some assistance.  I am more concerned that these actions I need appear to me to be something that is needed for any front-end database solution to navigate and edit almost any relational database structure.  Obviously the table names would need to be changed to that used in the specific data structure, but I would think that any user of a database would need a way to add a relationship, edit a relationship and delete a relationship without deleting the core record.

             

            Thanks again for your help.  I will delve in a little more and see if I can get your solution steps to "click" in my pea brain!

            • 3. Re: Working with Many to Many Relationship Tables
              philmodjunk

              While it's possible to write script do each item on your list, you may not need to.

               

              You can place a portal to your join table on a layout based on one of the "core" tables.

              You can enable the option "allow creation of records via this relationship" for the relationship linking the core table to the join table.

              Put the 2nd core table's ID field in the portal row as a drop down or pop-up menu listing the available records in the core table.

              Now "add relationship" is as simple as clicking into this dropdown in the bottom empty portal row and selecting a value from the dropdown.

              No scripting required.

               

              You can delete a relationship simply by adding a button to the portal row that deletes the portal record. (This deletes the join record.)

              Detail fields from the related 2nd core table can be placed in this portal for display and/or editing purposes and this may elimnate the need for a popup modal window for doing the same.

               

              You can set record level access permissions on each of these tables to control the level of access (including who can view and edit specific records) for different groups of users.

               

              So you can script it if you want to, but you might use one or all of these simpler approaches instead--especially if you are still learning how to do things in filemaker.

              • 4. Re: Working with Many to Many Relationship Tables
                tgold

                See my questions in red.  I have done a lot of power user/development over the past 18 years and every system has its unique quirks.  Some of Filemaker's steps have me stumped.

                 

                While it's possible to write script do each item on your list, you may not need to.

                 

                You can place a portal to your join table on a layout based on one of the "core" tables. I have created a portal to the core table from the current core table so I could see all the related data fo that associated record.  If I repalce that table with the join table I will not have the related core data to view. 

                You can enable the option "allow creation of records via this relationship" for the relationship linking the core table to the join table. This I have done.

                Put the 2nd core table's ID field in the portal row as a drop down or pop-up menu listing the available records in the core table. Are you suggesting as part of the first step to also add the 2nd core table's ID field to the portal? 

                Now "add relationship" is as simple as clicking into this dropdown in the bottom empty portal row and selecting a value from the dropdown.  Is this a separate step/activity?

                No scripting required.

                 If I can get passed removing the joined record, editing and adding, then I'll worry about deleting!!!!  Thanks.

                You can delete a relationship simply by adding a button to the portal row that deletes the portal record. (This deletes the join record.)

                Detail fields from the related 2nd core table can be placed in this portal for display and/or editing purposes and this may elimnate the need for a popup modal window for doing the same.

                 

                You can set record level access permissions on each of these tables to control the level of access (including who can view and edit specific records) for different groups of users.

                 

                So you can script it if you want to, but you might use one or all of these simpler approaches instead--especially if you are still learning how to do things in filemaker.

                • 5. Re: Working with Many to Many Relationship Tables
                  philmodjunk

                  tgold wrote:

                  See my questions in red.  I have done a lot of power user/development over the past 18 years and every system has its unique quirks.  Some of Filemaker's steps have me stumped.

                   

                  While it's possible to write script do each item on your list, you may not need to.

                   

                  You can place a portal to your join table on a layout based on one of the "core" tables. I have created a portal to the core table from the current core table so I could see all the related data fo that associated record.  If I repalce that table with the join table I will not have the related core data to view.  This is not the case. As I stated further down in this post, you can add fields from the 2nd core table to the join table based portal. These fields will be visible and editable once you've selected the ID number of a record from this table.

                  You can enable the option "allow creation of records via this relationship" for the relationship linking the core table to the join table. This I have done.

                  Put the 2nd core table's ID field in the portal row as a drop down or pop-up menu listing the available records in the core table. Are you suggesting as part of the first step to also add the 2nd core table's ID field to the portal?  No. Your join table has two fields, Core1ID and Core2ID. If your layout is based on the Core1 table, add the Join table's Core2ID field as a popup/drop down in the portal.

                  Now "add relationship" is as simple as clicking into this dropdown in the bottom empty portal row and selecting a value from the dropdown.  Is this a separate step/activity? This is exactly what I described. Once you've set up the portal and drop down, you can add new portal records simply by entering data in the bottom blank row. Selecting a "core 2" record in the drop down both enters data--creating a new join record--and links it to both Core 1 and Core 2 tables.

                  No scripting required.


                   


                  • 6. Re: Working with Many to Many Relationship Tables
                    philmodjunk

                    Here's a simple demo file of the concept I uploaded to a share site awhile back:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                    • 7. Re: Working with Many to Many Relationship Tables
                      tgold

                      Thank you so much for the direction.  It helped explain a lot, specifically with respect to some of the ways Filemaker does things that are different from Alpha.  I have a few more questions....

                       

                      tgold wrote:

                      See my questions in red.  I have done a lot of power user/development over the past 18 years and every system has its unique quirks.  Some of Filemaker's steps have me stumped.

                       

                      While it's possible to write script do each item on your list, you may not need to.

                       

                      You can place a portal to your join table on a layout based on one of the "core" tables. I have created a portal to the core table from the current core table so I could see all the related data fo that associated record.  If I repalce that table with the join table I will not have the related core data to view.  This is not the case. As I stated further down in this post, you can add fields from the 2nd core table to the join table based portal. These fields will be visible and editable once you've selected the ID number of a record from this table.

                      You can enable the option "allow creation of records via this relationship" for the relationship linking the core table to the join table. This I have done.

                      Put the 2nd core table's ID field in the portal row as a drop down or pop-up menu listing the available records in the core table. Are you suggesting as part of the first step to also add the 2nd core table's ID field to the portal?  No. Your join table has two fields, Core1ID and Core2ID. If your layout is based on the Core1 table, add the Join table's Core2ID field as a popup/drop down in the portal.  I have done this and it works, the problem is I have a list of tens of thousands of records in some of my core tables and that obviously makes it impossible to use this type of drop down.  I need to have a way to search for the appropriate record I need to relate to the ciurrent record.  As an example, in some cases these are Contacts I am searching and in other cases the core table has Transaction that I am a searching.  In addition, in many cases I may need to review more data from the record to which I plan to create the relationship to make sure I have the correct record as there are many elements of some of these records that make them seem similar.  So, I need to have the ability to review multiple fields from the related table to ensure I have identified the correct one.  Further, there are times that when searching it will become evident that there is no record and one needs to be created.  I need to be able to launch the approrpaite "add a record" script to give me the ability to create the core table record on the fly. 

                       

                      Thanks for any eample and ideas you can provide. 

                       

                      As I stated in an earlier post, these seem to be basic search and other functions that should not be so challenging to develop and seem to be things that Filemaker should have a wizard-driven ability to create....oh well.

                      Now "add relationship" is as simple as clicking into this dropdown in the bottom empty portal row and selecting a value from the dropdown.  Is this a separate step/activity? This is exactly what I described. Once you've set up the portal and drop down, you can add new portal records simply by entering data in the bottom blank row. Selecting a "core 2" record in the drop down both enters data--creating a new join record--and links it to both Core 1 and Core 2 tables.

                      No scripting required.

                      • 8. Re: Working with Many to Many Relationship Tables
                        philmodjunk

                        I started you out with the "basics" as these are easiest to set up and use. As your needs grow, more sophisticated approaches may be appropriate.

                         

                        There are two ways to handle cases where you have a large number of items appearing in your drop down and both may be used in the same solution.

                        If your records can be organized into smaller groups based on a value in a given field in that table, you can set up a conditional value list system where you first select the group from one drop down and then select the specific item from the second.

                         

                        An option that is very close to what you describe is to set up a search field such that you enter text in the search field and the system pops up a list of matching entries with sufficient detail to select the correct item. If such a search comes up with no matches, the script can ask the user if they want to create a new record.

                         

                        Here's a thread on setting up conditional value lists:

                        Custom Value List?

                         

                        Here's a thread on a scripted search method that can be adapted to what you describe:

                         

                        Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

                         

                        (You can use this method with a field that is not set up as a drop down list or which does not use auto-complete if you find that slows down your system too much.)