1 2 Previous Next 26 Replies Latest reply on Nov 26, 2013 7:51 AM by fbolte

    How to create entries with existing data in many-to-many relationship?

    fbolte

      Hi all,

       

      I'm evaluating FileMaker right now and am quite impressed so far. I'm building an image database and there I have a question regarding tagging.

       

      I have a many-to-many relationship with a link table between IMAGES and TAGS: images <---> image2tags <---> tag

       

      On an image's detail page, all related tags are shown in a portal, no problem so far.

       

      Now it has to be possible to enter new connections (tags for this image).

      So I enabled creation of entries via the relationship, and populate the entry field with a list based on the tags table. So when I type, FileMaker uses the type-ahead feature to propose tags based on the characters already entered. That is really great and works well.

      Exept … my problem is, that it creates double entries in the tags table if a tag already exists. I set both fields (tagID and tagname) to be unique, but the only result was that when trying to enter an existing tag a warning dialog pops up that prevents the creation. Which is absolutely correct, but not what I want.

       

      Is there a way to tell FileMaker to create an entry in the link table with an existing tagID instead of trying to create a duplicate in the tags-table?

       

      My only solution yet is with an additional global database field in the images table called newtag, that after entry triggers a script (when leaving) that does all the checking and creation. This is working but I don't really like it, because

      a) it feels cumbersome

      b) the entry field for newtag has to have a static position and does not move down like the entry field inside the portal

      c) loses focus after creation

       

      I have a feeling that there is an option to tell FileMaker how to handle the automatic creation, but I can't find it.

       

      Any suggestions welcome?

        • 1. Re: How to create entries with existing data in many-to-many relationship?
          Mike_Mitchell

          Hello, fbolte.

           

          It sounds like your portal is set up incorrectly. You need to put the tagID field from the join table (image2tags) in the portal, not the tagID from the tag table. What you want to create are join records when you attach an image to a tag. You can use the value list feature to show the second item in the list alone if you just want to see the name and not the ID.

           

          (BTW - often, a better setup for join table creation is a list view selection rather than a value list, because you still have to allow users to alter the key field if you do it via a portal. But it does require more setup and scripting, so it would depend on how savvy your users are and how much you trust them.)   

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: How to create entries with existing data in many-to-many relationship?
            psijmons

            Set Error Capture to ON and after the commit step, revert the entry in that field when you get Error=504

             

            Would that do it for you?

            • 3. Re: How to create entries with existing data in many-to-many relationship?
              fbolte

              (Wow, that was fast.)

               

              I realized, that I didn't explain all of the problem, sorry.

               

              If the entered tag does not exist, it shall be created (in the tags table). Does this happen if I use the link table for the portal instead of the tags table? (I had not enough time to test yet, sorry.)

              • 4. Re: How to create entries with existing data in many-to-many relationship?
                fbolte

                I'm not sure what you mean exactly. Is this a solution for the entry field losing the focus?

                • 5. Re: How to create entries with existing data in many-to-many relationship?
                  Mike_Mitchell

                  The short answer is no, it will not automatically create the new entry in the parent table if it doesn't already exist. There are a couple of ways to do that:

                   

                  1) You can use a list-based selection process with an additional option for "Create New" in your list. This can be accomplished using a Virtual List technique, were you substitute a calculated list for the actual contents of the list being selected and trigger a script when the user chooses to create a new value.

                   

                  2) You can do essentially what you're already doing - trigger a script if a user enters a value that's not in the list to create the new record. In this case, you can do essentially what psijmons is recommending - you check to make sure the entry is a member of the existing list and trigger your new record if you get the appropriate error (use an OnObjectModify trigger).

                   

                  Note that, in either case, you'll need to come back and insert the ID of the newly created record into your join record once it's been created. I recommend against using the text of the parent table as a key. But you still need to point your portal at the join table. Otherwise, you won't be creating join records; you'll be manipulating the parent record and that will not create the many-to-many join properly.

                   

                  HTH

                   

                  Mike

                  • 6. Re: How to create entries with existing data in many-to-many relationship?
                    danielfarnan

                    No, the idea is that when you have the tagname field set to only contain unique values, the error code that is generated on field commit is number 504. You could then have a script that triggers on the OnObjectExit event and tests for the presence of this error code (but your script has to set Error Capture to On to be able to detect the errors).

                     

                    However, I am confused as to why your tags table needs an ID as well as a tagname - especially since tagnames are going to be unique. If you get rid of the ID field you will have an easier time, and there's no reason to keep it from a data normalisation perspective.

                    • 7. Re: How to create entries with existing data in many-to-many relationship?
                      Mike_Mitchell

                      Because if you ever want to change a tag name, you’ll have to either (a) update all the existing records, or (b) live with the old records having old tag name. Giving the tag an ID prevents this.

                       

                      Of course, if your tag names are forever …  

                       

                      Seriously, in the event a user puts in a tag name wrong (typos, anyone?), you’ll not want to go to the trouble of globally replacing all the incorrect names with the correct one. Preserving the data integrity through the use of keys will prevent such an accident.

                      • 8. Re: How to create entries with existing data in many-to-many relationship?
                        danielfarnan

                        I see your point, but having to run a global replace is not that big a deal. Plus, if your code to prevent duplicates isn't perfect you'll have to spend more time hunting down the issue. And what if someone accidentally deletes a tag record? Are you able to re-use an ID value or do you need to run the global replace anyway?

                         

                        There are several use cases where a "value list" table only needs to contain one field.

                        • 9. Re: How to create entries with existing data in many-to-many relationship?
                          Mike_Mitchell

                          Yes, if all you’re doing with a table is populating a value list, you can certainly have just a single field. Except this isn’t just a “value list” table. This is a parent table for a many-to-many join. The value in question is being used as a key field for that join. It’s bad practice to give the user access to modify a key field. General principles dictate that the user should not be permitted to touch it (and that includes any DBA or “super user” you may have).

                           

                          If someone deletes the tag record, then you would normally enforce referential integrity and delete the related join records as well. (This is why you don’t normally give access to delete records in a domain table to the general population.) Alternatively, if this is unacceptable, you should implement scripted controls to prevent deletion in the case where the parent record is in use.

                           

                          Mike

                          • 10. Re: How to create entries with existing data in many-to-many relationship?
                            fbolte

                            Guys, you're great so far. Thank you!

                             

                            I'm still having problems, though. I tried the script-catches-error method, but it doesn't work. It still pops up the warning. And I have to reset the field or jump back into it to change. How do I prevent that popup from appearing? ErrorCapture is ON.

                             

                            I also tried to place the trigger on several events.

                             

                            OnObjectModify triggers after every entered character, booh.

                            OnObjectValidate is triggered befor the error occurs.

                            OnObjectSave is not really triggered, because the warning pops up before which prevents saving, same goes for OnObjectExit.

                             

                             

                            On another topic, that Mike brought up: I'm not sure how to display the »tagnames«, if I have to set my portal to show the values of the link table, since it only contains the foreign keys (IDs).

                             

                            (I would love to post my scripts, but it's all German, because FileMaker decided it's a good idea to localize the scripting, d'oh!)

                            • 11. Re: How to create entries with existing data in many-to-many relationship?
                              Mike_Mitchell

                              The standard method for displaying the description field from the parent table while actually editing the field in the join table is to layer two fields. The key field is placed under the description field, text set to the same color as the background (or clear). You then set the tab order so the person's cursor is placed in that field, but he actually sees the description once he enters the appropriate ID.

                               

                              But again, this is really not the best way to do things. You're allowing users to manipulate the contents of a key field, which opens the possibility (probability) of them breaking a relational join in your database. You're better off setting up a list view and having the users click their selection in a second window while you set the keys via scripting.

                              • 12. Re: How to create entries with existing data in many-to-many relationship?
                                billbarman

                                What if you add a calculation field in your link table that counts the number of related tag records (should either be 0 if this is a new tag; or 1 if a tag record already exists)

                                 

                                Then, after a new link record was created through the portal when a new Tag name is entered, run a script that checks the value of the calculation field.  if 1; then all is ok;  else if 0; then go to related link table and set ID fields and Tag name fields to new values and return to original layout; if greater than 1 something went wrong somewhere in your unique value requirement in the Tag table.

                                • 13. Re: How to create entries with existing data in many-to-many relationship?
                                  fbolte

                                  I'm not sure if I unterstand. It seems as if I first shall create an entry in the link table and after that the tag that is linked. Sounds to me as if I would always have a (short) time of inconsistent data. Also I'm not sure how to create an entry in the link table if I don't have the ID of the (not yet created) tag.

                                  • 14. Re: How to create entries with existing data in many-to-many relationship?
                                    fbolte

                                    Uh, this layering sounds very hacky to me. Don't want to do that. And I don't want my colleagues to have to enter IDs, only the tags.

                                     

                                    (BTW: If I populate the portal with the tagnames from the tags table and create a completely new one, the according entry in the link table will be created correctly.)

                                     

                                    The list view with clicking the tags seems overkill and a bit clunky. The type-ahead suggestions from the value list in text entry fields is best for this kind of data entry, in my opinion.

                                     

                                    Since I don't get my trigger scripts to prevent the error popup, right now it seems as if my solution with the external entry field and creating the entries by script is still the easiest best solution here.

                                    1 2 Previous Next