Auto Remove Duplicate Row in Form Grid

Introduction

Managing data integrity is crucial when dealing with form grids. Duplicate rows can lead to inconsistencies and errors in your data collection. This guide demonstrates how to automatically remove duplicate rows in a form grid to maintain clean and accurate data entries. By implementing a simple JavaScript solution, you can ensure that newly added rows are unique based on specified criteria.

Objective

Ensure that Form Grid data does not contain duplicate rows by automatically removing any duplicates upon form submission.

How does it work?

This script checks for duplicate rows in a form grid and removes any newly added rows that are identical to existing ones. You can specify which columns to compare to identify duplicates. By default, all columns are compared, but you can customize this to compare specific columns only.

To implement this script, follow the steps below:

1. Add the following JavaScript code into the Custom HTML or appropriate scripting area of your form:

   <script>
       $(document).ready(function(){
           var formGridFieldId = "gridId";
    
           //run when form grid value change
           $("[name="+formGridFieldId+"]").live("change", function(){
               var table = $(this).find("table");
    
               //get last added row
               var addedRow = $(table).find("tr.grid-row:last-child");
    
               var duplicate = false;
    
               //Loop all the row and compare all the field
               $(table).find("tr.grid-row").each(function(){
                   var row = $(this);
    
                   if ($(row).attr("id") != $(addedRow).attr("id")) {
                       var similar = true;
    
                       //Loop all field
                       //Change '$(row).find(".grid-cell")' to '$(row).find("[column_key=field1], [column_key=field2]")'
                       //if only want to compare to certain fields. Separate with comma.
    
                       $(row).find(".grid-cell").each(function(){
                           var field = $(this);
    
                           var columnKey = $(field).attr("column_key");
                           var value = $(field).text();
    
                           var newValue = $(addedRow).find("[column_key="+columnKey+"]").text();
    
                           if (value != newValue) {
                               similar = false;
                               return false;
                           }
                       });
    
                       if (similar) {
                           duplicate = true;
                           return false;
                       }
                   }
               });
    
               //if record is duplicate, remove it
               if (duplicate) {
                   $(addedRow).remove();
               }
           });
       });
   </script>

2.  Replace "gridId" with the "Field ID" of your form grid.
3. If you only want to compare certain columns, modify '$(row).find(".grid-cell")' to '$(row).find("[KB:column_key=field1], [KB:column_key=field2]")', separating column keys with commas.