7 Replies Latest reply on Jun 7, 2013 9:11 AM by billbarman

    Need a value list that removes duplicates from a table

    ascii7x

      Good morning all.

       

      I would like to create a valuelist that removes duplicates from the "Products" table.

      I created a relationship that searches based on "type".

      That works but the problem is that it retrives all records with the same "product name".

      Some products are listed multiple times because they come in different sizes.

      Some products are listed 20 times.

      I would like the value list to only display single product names, not all of them.

      How can this be accomplished.

       

      josh

        • 1. Re: Need a value list that removes duplicates from a table
          ascii7x

          Thank you for response dvalley.

          I should of been more clear, it's "Product Name" that I need limited.

          Just finished reviewing the table data, the only thing the duplicate have in common are: type, prodcut name.

          The only difference is the "UUID".

          They are all the same product, it's just different volume size(size).

           

          Need a method to strip out the duplicates from view in the laue list.

           

          josh

          • 2. Re: Need a value list that removes duplicates from a table
            billbarman

            you could build a file "product_name_index" and then relate the product name in the new index file to the product name in the "Products" table.

             

            Loop through all the records in the Products table and set the Product Name in your new index table to the Product Name in the Products table.  This creates a new record in the product_name_index file if a match doesn't already exist.  It does nothing if the the match already exists.

             

            At the end of the loop, all the unique Product Names will be found in the products_name_index file.

             

            BTW, this is a good way to check and see if you have any mispelled product names in the Products file.  The new index file is also a good place to calculate sum totals, counts, etc. for each Product.

            • 3. Re: Need a value list that removes duplicates from a table
              ascii7x

              billbarman, that sounds like a good idea.

              I could do this for many other value lists I have that are manual entry.

              Now how exactly would I create a script on looping through the products table live on its own to copy that data and transfer it to the index table?

              Would the script be called from the index table or the products table?

               

               

              Can you please provide some insight into your idea?

               

              Thank you.

               

              josh

              • 4. Re: Need a value list that removes duplicates from a table
                billbarman

                would look something like this.  script would be something like "build index"

                • 5. Re: Need a value list that removes duplicates from a table
                  ascii7x

                  Thank you.  I was about to start building table occurences to isolate all dynamic value lists.

                  Right now the previous coder has the value list implemented into the main tables with other stuff braching off the main tables.

                  It is a mess to follow so much stuff off one main table occurence.

                  Let view your zip file and get back to you.

                   

                  josh

                  • 6. Re: Need a value list that removes duplicates from a table
                    ascii7x

                    billbarman,

                     

                    Looked at the files and scripts.

                    Would I create a button to launch the script?

                    I looked at the script "build index"

                    Please explain why it would delete all records from  the 'product_index'?

                    Then it sets the fields again though a loop.

                     

                    Please help me understand the logic.

                    Will I have to go to the "build index" and manually delete the duplicates?

                     

                    Your help would be greatly appreciated.

                     

                    josh

                    • 7. Re: Need a value list that removes duplicates from a table
                      billbarman

                      That was only a sample database to show off the concept of building a related index file.

                       

                      Whether or not you delete all the records first depends on the circumstances.

                      Exactly when you run the script to populate the index file depends on the circumstances. 

                      Whether the item table is large or small, has a lot or few  adds/deletes requires different strategies.

                      Running the script manually, by a triggered event, on a schedule are all options that could be considered but the optimal one depends on your clients needs.

                       

                      have fun!