1 2 Previous Next 19 Replies Latest reply on Jun 15, 2012 3:27 PM by PeteMasse

    Many to Many data entry



      Many to Many data entry


      I have a many-to-many relationship separated by a join table to create two one-to-many relationships.  The tables are "computers", "software" and "compsoft" as the join table. 

      Computers table         pfComputerID  Title Version

      Software table         pfComputerID Hostname IpAddress

      CompSoft table        fkComputerID fkSoftwareID


      When entering a new computer, I want to be able to select/deselect the software that is installed on that computer, i.e. multiple selection/deselection of software per computer.  Currently selecting multiple software simply populates the the fkSoftwareID on the join table with multiple vales.  I need it to create a new recored in the CompSoft table for each software title that belongs to the a specified computer.

      Not sure where to begin with this.  I suspect some scripting may be needed.

      Thanks in advance.  Pete


        • 1. Re: Many to Many data entry

          Scripting may or may not be necessary.

          Put a portal to CompSoft on your Computers layout.

          Enable Allow Creation of Records via This Relationship for CompSoft in the Computers to CompSoft relationship.

          Format the _fkSoftwareID field as a drop down list or pop up menu of ID's from the Software table with the Title field included as the second field for that value list.

          Now you can select software titles in this field and build a list of software titles for the new computer.

          Here's a demo file that illustrates this approach as well as more sophisticated options:  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

          Note: you can open this Fp7 format file in FileMaker 12 to get a copy converted to the new file format.

          • 2. Re: Many to Many data entry

            Perfect!  Exactly what I needed.  Thank you for your help.

            • 3. Re: Many to Many data entry

              Any idea what part of what script is used to make the Red delete X button appear only on records with something in them in the example ManyToManywDemoWExtras.fp7?

              • 4. Re: Many to Many data entry

                Since I created that file in the first place, of course. Wink

                In layout mode, right click it and select conditional formatting to see the conditional format expression used to control its visibility. It changes the font size to 500 to make it disappear.

                In layout mode, you can also double click it to see what script is performed when it is clicked so you can drill down to see what is done to add or remove the needed Join table record.

                And you can note the relevant table occurrence name for this portal as shown in its lower left corner when in layout mode and then go to Manage | Database | Relationships to find it to see how the relationship was defined to make this work. (You may want to also open Layout Setup... and note the table occurrence name in "Show Records From" so you can find it and the relationship linking it to the portals shown on this layout.)

                • 5. Re: Many to Many data entry

                  Thanks to you I have made much progress.  However, I guess I'm not quite understanding your add contact wDialog script.  I don't require as many bells and wistles as your script has.  For starters:

                  1. What value is returned in the first line Set Variable [$EventID; Value:Get ( ScriptParameter )?

                  2. Is Contacts::gContact_Name simply a temp storage spot for the value entered in via the add dialog box?


                  • 6. Re: Many to Many data entry

                    1) Enter layout mode and double click the button that performs this script. Click the "Specify" button in button setup to bring up a second dialog where you have an "optional script parameter" box. This box contains the expression that is evaluated and passed to the script so that Get ( ScriptParameter ) can extract it. In this case, it's passing the event ID of the current event record on your layout.

                    2) Basically that's what it is. Specifically, it's a field defined with global storage in its Field Options. I use a "g" as the first letter of my global fields so that I can easily see that this field has global storage specified.

                    • 7. Re: Many to Many data entry

                      I see that you are using a field in the Contacts table to store the input from the dialog box.  I'm curious, Do you think the Global storage option in a table field is supported when using a MySql backend, which is what I am currently doing?

                      • 8. Re: Many to Many data entry

                        I haven't tried that, but it should work if you define the field in a FileMaker table. As long as a global field is not used as a field in a relationship, it can be defined in any table you care to use for that purpose and it will still be accessible from all layouts, all scripts and for all calculations where you need it. I often define a "globals" table and use it to define all global fields not used in relationships so as to make it easier to manage them.

                        • 9. Re: Many to Many data entry

                          I'm almost there.  However when clicking the '+' button to add a new contact, on yours it will automatically add the new contact to the list.  On mine, it adds the piece of software to the software table but then I have to manually select it to add it to my portal list.  I haven't quite figured out how to do this.  Could you explain?

                          • 10. Re: Many to Many data entry

                            The script creates two new records, not one. First it copies the Id of the current record into a variable. Next, it creates a new contact record where the auto-entered serial ID for it is generated and copied into a second variable. Then the script changes layouts to a layout based on the join table and creates a new record in its table, entering the two serial ID's from the two records being linked in this join table.

                            • 11. Re: Many to Many data entry

                              I realize this, however after I enter in a new Contact (software title in my case) and press OK on the dialog box, the software title will not automatically appear in the portal list like yours.  I have to then choose the next empty portal line and add it in from the drop down list.

                              • 12. Re: Many to Many data entry

                                Which suggests that your script did not successfully create the linking record in the join table or it would appear in the portal.

                                • 13. Re: Many to Many data entry

                                  Thank you.  I understand the record creation part.  However I am trying to duplicate the behavior where when you press the + button, then enter in a new contact and press OK on the dialog box, it automaticall appears on the next available line of the portal tool.  Mine addess a new contact just fine however it currently does not automatically appear on the portal after it has been added.  I have to then click on the pop-up-menu and add it manually inside the portal.

                                  I'm missing one small thing.

                                  • 14. Re: Many to Many data entry

                                    To repeat, if your script is successfully creating the new, linked record in the join table, it will automatically appear in the portal when you click your button and enter your data.

                                    If it does not appear, either the record is not being created in the join table, or the correct ID values needed to link it to the two records being joined are not being correctly entered into that new record.

                                    The other possibility is that your portal does not include the correct field to display this name from the contact table. This is done by adding the name field from Contacts into the portal to the join table. (I'm assuming that when you check your new record in contacts, you find the data you entered in the custom dialog appears correctly in this new contact record.)

                                    1 2 Previous Next