2 Replies Latest reply on May 18, 2015 8:42 PM by starstuff

    How to manage these duplicates?



      How to manage these duplicates?


      Hi all!


      i'm stuck on what to do with these duplicates in the table supplier_product_line.


      supplier - contains supplier detail, match to supplier_product_line as fk_supplier

        - pk_supplier

        - name

        - address

        - phone

        - date_created

      product - contains the product, match to supplier_product_line as fk_product

        - pk_product

        - name

        - unit

        - date_created

      supplier_product_line - no pk, connect supplier (fk_supplier) and product (fk_product)

        - fk_supplier

        - fk_product

        - unit

        - cost price

        - discount

        - net price

        - date_created

        - date_modified - timestamp


      Goal: Export and Show only the most up-to-date price of product per supplier

      Problem: Should i be worried about duplicates in the table supplier_product_line? i tested it out and found that i can duplicate every supplier product without warning. is this the expected result? if that is the expected result, then if im going to export it, how to export only the most up-to-date price of a product per supplier?


      thank you for the assistance!

        • 1. Re: How to manage these duplicates?

          It is the expected result if you don't set up something in your design to prevent the generation of records where the combined supplier and product ID values are duplicates.

          The simplest method:

          Add a text field with an auto-enter calculation:

          fk_Supplier & "|" & fk_Product // do not separate ID's with a return!!!!

          Add a unique values validation rule on this field. (You can't do this with a calculation field, it must use an auto-enter calculation.)

          Other methods are interface design techniques that prevent a user from selecting a specific value combination twice. Those take more work to set up, but produce a much more user friendly work flow for the user.

          A diminishing value list can update to drop out either a product ID from the value list for a given supplier each time a product is selected for a given supplier or drop out a given supplier ID each time a supplier is selected for a given product.

          A "check box like" format for selecting products or selecting suppliers can be set up. The check box like interaction means that selecting a product (or a supplier) a second time de-selects that value instead of selecting it a second time. A special portal to either all products or all suppliers with buttons that perform a script to select/deselect the value is used with a "hide object when" setting on the button to simulate the check box action.

          For an example of a diminishing value list see: "Adventures In FileMaking #1 - Conditional Value Lists"

          For an example of button controlled simulated check box system for selecting values, see: "Adventures in FileMaking #2 - Enhanced Value Selection" (This does not have a sample script specific to updating a join table, but does show how to set up a portal to list values that function as a check box set so the scripting part is still left up to you to figure out.)

          • 2. Re: How to manage these duplicates?

            Hi PhilModJunk!


            thank you for the help! the simplest method is brilliant! i will be using that to prevent duplicates. the other methods are very advanced to me.

            thank you also for your tutorials, i will study them!


            Thank you!!