3 Replies Latest reply on Apr 22, 2009 12:26 PM by comment_1

    Unique Child Records

    obeechi

      Title

      Unique Child Records

      Post

      If you have a many to many relationship where you want the records in the join table to be unique, what do you do? 

       

      For example, lets say you have PRODUCTS and each product requires that VENDORS sign a release form to handle the product. You only want one release form for a vendor for a given product. Likewise, if you were to view things from a product table based layout, you only want to see a vendor listed once for the product release form. 

       

      It's still a many to many relationship between PRODUCTS and VENDORS

       

      So we might have a three tables: PRODUCTS, PRODUCTRELEASE, VENDORS

       

      Then, we might work from a vendor based layout, where each time a product release form comes across the desk, we search for the vendor, and then choose a product that hasn't already been chosen. In the event we choose a Product redundantly, we want to be able to bounce that redundant attempt without recording it. So at this point, not really knowing what a standard approach for this would be, I've created a field-aka-attribute in the ProductRelease table that concatenates ProductIDf & VendorIDf, and is defined to be unique (in the validation tab). Just concatenating ProductIDf & VendorIDf isn't enough, so the I use "P" & ProductIDf & "V" & VendorIDf. 

       

      This seems to work, but what I don't like about it is that if I make the mistake of trying to add a non-unique child record, I end up having to close two different dialogues that FileMaker presents to me. These are the revert dialogues. I'd prefer to have only one dialog, which is less confusing, especially when dealing other things during a busy day. 

       

      I'm also wondering if there isn't another way to deal with this scenario.  

        • 1. Re: Unique Child Records
          comment_1
            

          There are two levels to this issue:

           

          1. On the data level, you must have field validation to prevent duplicate combinations. It looks like you have this solved(although a simpler calc would be Product ID & "|" & VendorID).

           

          2. On the presentation level, you should have a selection mechanism that will offer only remaining combinations for users' selection and prevent validation from kicking in. Note that this is not "another way" but an addition.

           

          There are several ways to build such a mechanism - it really depends on the selection method (value list, portal, list). 

          • 2. Re: Unique Child Records
            obeechi
              

            So the only way to circumvent the double validation dialogs that FileMaker presents is to use a Presentation approach that disallows the validation from running at all?

             

            I guess this might like a filtered value list that shows only products that haven't yet been released for a given vendor, where I would have to explicitly change a value in a field to filter out a given Product (because the release form was signed for). Then also, this allows for reverting a form when the form is flawed or lost, et (by changing the value back). 

             

            I like this idea better than just using a button that takes over the whole process. The button only approach seems too vague and implicit and doesn't give a sense of control.  

            • 3. Re: Unique Child Records
              comment_1
                

              Yes, you want to prevent any kind of validation dialog, double or single, because it's confusing to users. I'm afraid I don't quite follow the rest of your post. If you want to use a value list, you indeed need to filter it to include only unreleased products. This can be done by defining a relationship based on a calculation field listing all released products' ID's, and using the ≠ relational operator. There's a bit more to the technique, depending on your version - see:

               

               link