1 2 Previous Next 18 Replies Latest reply on Nov 15, 2012 9:49 PM by laguna92651

    Preventing Duplicate Records

    laguna92651

      I am generating a set of records in a table called PRODUCTS, using a looping script, based on records in a table called SIZES. The records created would be of the form;

       

      DM12013-2

      DM12013-4

      DM12013-6

      DM12013-8

       

      What changes do I need to make so a record is not created (duplicate record), if the record already exists in the PRODUCTS table.

       

      If DM12013-2 already exists I do not want to create a second copy of it. I would not want an error message, but the script to just go ahead and create the remaining records for the PRODUCTS table.

        • 1. Re: Preventing Duplicate Records

          laguna92651 wrote:

           

          I am generating a set of records in a table called PRODUCTS, using a looping script, based on records in a table called SIZES. The records created would be of the form;

           

          DM12013-2

          DM12013-4

          DM12013-6

          DM12013-8

           

          What changes do I need to make so a record is not created (duplicate record), if the record already exists in the PRODUCTS table.

           

          If DM12013-2 already exists I do not want to create a second copy of it. I would not want an error message, but the script to just go ahead and create the remaining records for the PRODUCTS table.

           

          Hi  :^)

           

          You can use field validation on that Products code field.  Set it to validation unique, validate always and do not allow override.  Then script when you are creating the records can be:

           

          Loop

          Exit Loop If [ whatever your criteria - end of count etc  ]

          New Record/Request

          Set Field [ Products::ProduceCode ; whatever your values ]

          Set Error Capture [ On ]  <--- move the error capture to before the commit, sorry about that

          Commit Records/Requests [ Skip data entry validation; No dialog ]

          If [ Get ( LastError ) ]

          Revert Record/Request [ No dialog ]

          End If

          End Loop

           

          You have not indicated where you are getting these sizes values - if looking through the Products table, only the first size will be listed (IF there is a relationship between Products and Sizes already) but I assume you have that addressed.

           

          By using field validation, 1) it won't fail you and 2) it does not require an additional table occurrence or calculation to function.

           

          EDITED:  One thing I wanted to mention ... if the validation fails and it skips a record (as it should), it still increments the auto-enter serial number (primary key) on the table.  Usually that is not an issue since these serials are meaningless anyway but I thought I should mention it.

          • 2. Re: Preventing Duplicate Records

            Another option is straight import with the validation in place.  Any duplicates are automatically ignored.  But since you were looping, my suggestion should fit in nicely.

            • 3. Re: Preventing Duplicate Records
              laguna92651

              On the form where the script is run, I make an entry like DM121013, this is being replicated by the number of records in the SIZE table and the new entry is being be put into the PRODUCTS table, along with each cycle of the loop a size number is being added to the PRODUCTS table along with the DM12013 entry.

              In the PRODUCTS table the two values are being concatentated in a field called Product Code and I will get

              something in the form DM12103-4. This is the entry that I want to be unique, the concatenated value in the PRODUCTS table.

               

              The idea is not to have to make manual entries for one product that may have twelve different sizes.

               

              Go to Layout["SIZES"(SIZES)]

              Set Variable[$cycle;Value:Get(FoundCount)]

              Go to Layout["PRODUCTS_Form"(PRODUCTS)]

              Set Variable[$counter;Value:0]

               

              Loop

                   Duplicate Record/Request

                   Set Variable[$line;Value:$counter+1]

                   Set Field[PRODUCTS::line;$line]

                   Set Variable[$counter;Value:$counter+1]

                   Commit Records/Requests [Skip data entry validation;No dialog]

                   Exit loop If[$counter=$cycle]

              End Loop

               

              Is there a way to copy a script from FM to paste onto the web site or to notepad for reference material?

              Thanks for your help.

              • 4. Re: Preventing Duplicate Records

                I wonder if you have considered instead just leaving ONE product and instead use a related table which would hold one each of an allowed combination of ProductID and SizeID.  This join table would have many advantages over 'duplicate' Product records.

                 

                Some advantages are:  1) easier reporting &amp; ability to summarize by base product as well as Size, 2) ability to create conditional value lists, 3) since you control which Products can have which sizes, the record count/file size will be less, 4) with the combination code, you will not be able to find 'all XL' or group by size.

                 

                On Mac I've read you can print preview script then copy to clipboard.  On Windows I print to PDF.

                 

                As for adjusting my script yes we can.  Are the two tables currently related?  I assume no since your script goes to the layout and does not perform find or GTRR.  You may not have to leave your Products layout at all.  It is preferable to consider the structure change first however.  :-)

                • 5. Re: Preventing Duplicate Records
                  comment

                  If I understand your request correctly, you should start by getting a list of all the required sizes into a variable. Then go to the Products (?) table and loop over the list, creating a new product for each item.

                   

                   

                  Note that good practice would require:

                   

                  1. The code "DM12013" and the size 2 should be in separate fields (which you can later concatenate using a third field);

                   

                  2. The code "DM12013" should be entered somewhere into a "master" record. For example, you could have one "DM12013" entry in a Products table with 4 related entries in a ProductSizes table. This would also help handling possible duplicates, because your script would check if the current product has any sizes before proceeding.

                  • 6. Re: Preventing Duplicate Records
                    laguna92651

                    You are probably right, I'm bringing this over from Excel and really didn't stop to rethink this.

                    Are you saying a Product table with:

                     

                    ID    Style_No

                    1     DM1001

                    2     DM1002

                    3     DM1003

                    4     DM1004

                    5     DM1005

                    6     DM1006

                     

                    Then link this to a Size table with possible combinations, what would the structure of that look like? Wouldn't I still have to assign a size x times to each product? Could you explain a little more?

                    • 7. Re: Preventing Duplicate Records

                      "Then link this to a Size table with possible combinations, what would the structure of that look like?"

                       

                      Products --< ProductSizes >-- Sizes

                       

                      ProductSizes is where you would create these records.  This join table (which reconciles a many-to-many) will have the following fields:

                       

                      ProductSizeID (unique auto-enter straight serial )

                      ProductID

                      SizeID

                      ... and possibly QuantityInStock or sku or ?

                       

                      ADDED. also possibly LowStockLevel. :^)

                      • 8. Re: Preventing Duplicate Records
                        laguna92651

                        Would the ProductSizes join table in essence be my inventory table?

                        If I had a new Product with 10 sizes would I have to create 10 new records, if not how would these records be created? I trying get away with entering one set of base data, and then letting her rip.

                        I think I'm going to have to do a little reading on this, it isn't real clear in my mind yet, a newbie. But get the general idea of seperate tables.

                         

                        Currently the record generation does include quantity and reorder level. In the meantime could you show me where I should intergrate your code with mine.

                        Thanks

                        • 9. Re: Preventing Duplicate Records
                          laguna92651

                          "1. The code "DM12013" and the size 2 should be in separate fields (which you can later concatenate using a third field);"

                           

                          This is basically what I am doing now, I'm concatentating the DM12013 with the various sizes from the sizes table, in a new field in the Products table

                           

                          "2. The code "DM12013" should be entered somewhere into a "master" record. For example, you could have one "DM12013" entry in a Products table with 4 related entries in a ProductSizes table. This would also help handling possible duplicates, because your script would check if the current product has any sizes before proceeding."

                           

                          Initially I enter DM12013 in the products table, then run a script to create, an example, ten new DM12013 records with each record getting a unique pre-defined size, which are the concatentated with DM12013. What I'm doing now is to prevent any duplicates of the concatentated field, for example DM12013-4, be created.

                          • 10. Re: Preventing Duplicate Records

                            Michael and I are saying essentially the same thing structurally.  Is this one-time migration or on-going process? Will you always be importing the new Products or can Users enter a single new Product?  What columns exist on your spreadsheet? 

                             

                            You will want (or should have) two value lists both based upon all values in their Products and Sizes,  selecting the table's ID on left pane and its Name on right.  Now you can use these lists without having to loop Product records.  Instead you can have a loop through SizeIDs list within a loop of ProductIDs list right in the new join table itself.  Validation would be on a third concatenated field which would be an auto-enter by calculation (replace existing value) and validated as suggested above.

                             

                            But it is best to consider options on how to handle the record creation and script, particularly from Michael, once we understand a bit more.

                             

                            As for Inventory there are different constructions.  Usually an Inventory table maintains a balance much like a checkbook even allowing for Damaged Goods, Returns, Purchases, physical inventory adjustments and minuses sales from Invoice LineItems.  Some businesses and developers use standard number field in the table with their SKU which would be this new ProductSizes table and then they depend upon script triggers or transactional approaches to keep it accurate.

                            • 11. Re: Preventing Duplicate Records

                              I just have to say it ... If you depend upon script trigger, every time it fires you are at risk that something will go wrong, whether power failure, conflict with another script etc ... Many points of failure throughout every day. 

                               

                              But if you instead write summaries of your sales nightly, weekly or monthly then you only have one point of failure which can be properly error-trapped and protected.  Triggers can be very alluring but some things are best in batches.  And this doesn't mean the inventory figures aren't up to date; they are:

                               

                              Inventory Balance - SalesNotPosted = OnHand

                              • 12. Re: Preventing Duplicate Records
                                laguna92651

                                The product list has been imported, about 300 items, these records have already been concatentated. What I'm now dealing with is generating on-going new product configurations (clothing) on a on-going basis. A hand full of products every now and then? A dozen new products times a dozen sizes. Currently the product table contains all of the attributes of the product; product no (Style No + Size), size, color, Category, cost, price, qty, reorder level etc.

                                 

                                What do you mean here.

                                "selecting the table's ID on left pane and its Name on right"

                                 

                                Would I not need the script you showed above, with the validation you just mentioned. I guess I need to play around some with tables to get a better idea of what your suggesting, thanks for yours and Michaels help.

                                • 13. Re: Preventing Duplicate Records
                                  comment

                                  LaRetta wrote:

                                   

                                  Michael and I are saying essentially the same thing structurally.

                                   

                                  Yes, sorry about that. I posted before seeing your post.

                                  • 14. Re: Preventing Duplicate Records
                                    comment

                                    laguna92651 wrote:

                                     

                                    Currently the product table contains all of the attributes of the product; product no (Style No + Size), size, color, Category, cost, price, qty, reorder level etc.

                                     

                                    It would be helpful to know which attributes belong to the product itself (what you call Style No) and which are specific to the variation, i.e. the combination of Style No & Size. The common attributes should be stored in one place only, rather than being duplicated among all sizes of a product.

                                    1 2 Previous Next