Check for Data Duplicates on a Grid

Your rating: None Average: 4.3 (10 votes)

Here is a piece of code to prevent duplicate data on a specific field on a page grid. You can of course modify it to check for multiple fields or even the whole row.

   /* Check for data duplicates on a grid. */
   Local Row &row1, &row2;
   Local number &r, &r1;
   
   &rs = GetLevel0().GetRow(1).GetRowset(Scroll.grid_table);
   
   For &r = 1 To &rs.ActiveRowCount
   /*Get grid row*/
      &row1 = &rs.GetRow(&r);
      /*once we have a row, we are going to loop through the grid rows and make sure a specific field value is unique*/
      For &r1 = 1 To &rs.ActiveRowCount
         &row2 = &rs.GetRow(&r1);
         /* if this is a different row, and the field_name value matches then throw an error*/
         If &r1 <> &r And
               &row1.grid_table.field_name.Value = &row2.grid_table.field_name.Value Then
            MessageBox(0, "", 0, 0, "Error.  Duplicate values are not allowed.");
         End-If;
      End-For;
   End-For;

I recommend storing your Error message into the message catalog. If you do that then just replace the last two zeros in the MessageBox Function with your message_set and message_num accordingly.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Chris Clark's picture
Chris Clark (not verified)
Re: Check for Data Duplicates on a Grid

I would recommend changing the loop counts as we only need to compare 1 to 2,3,4 and 2 to 3,4,5 etc. We don't really need to go back and compare 5 to 1,2,3,4 as this was already handled in previous iterations.

Hence, the outer loop would be:

For &r = 1 To &rs.ActiveRowCount - 1

and the inner loop would be:

For &r1 = &r + 1 To &rs.ActiveRowCount

And you can delete the &r <> &r1 condition as &r will never = &r1.

CompShack's picture
User offline. Last seen 4 years 34 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Check for Data Duplicates on a Grid

Chris,
Great observation on your part. Thanks for your feedback, performance wise, your suggestion wins hands down.

Thanks for taking the time to share your remarks with us!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

DEEPTHI's picture
DEEPTHI (not verified)
Re: Check for Data Duplicates on a Grid

IN THE CODE THAT YOU HAVE SPECIFIED THE MESSAGE IN THE MESSAGE BOX WILL GET REPEATEDLY DISPLAYED
ie IF ROW1 AND ROW2 ARE SAME AND ROW3 AND ROW4 ARE SAME THEN THE MESSAGE WILL GET DISPLAYED TWICE
INSTEAD TAKE A VARIABLE AND INITIALIZE IT TO ZERO, THEN INCREMENT IT WITHIN THE IF CONDITION.
THEN COME OUT OF THE LOOP AND GIVE ONE MORE IF CONDITION STATING THAT IF THE VARIABLE IS >0 THEN MESSAGE BOX... THIS WAY THE MESSAGE WILL BE DISPLAYED ONCE..
HERE IS THE CODE

/*Check for data duplicates on a grid*/
Local Row &row1, &row2;
Local number &r, &r1, &I;

&I = 0;
&rs = GetRowset(Scroll.USHR_REC_EX3);
For &r = 1 To &rs.ActiveRowCount - 1
   /*Get grid row*/
   &row1 = &rs.GetRow(&r);
   /*once we have a row, we are going to loop through the grid rows and make sure a specific field value is unique*/
   For &r1 = &r + 1 To &rs.ActiveRowCount
      &row2 = &rs.GetRow(&r1);
      /* if this is a different row, and the field_name value matches then throw an error*/
      If &row1.USHR_REC_EX3.LOCATION.Value = &row2.USHR_REC_EX3.LOCATION.Value Then
         &I = &I + 1;
      End-If;
   End-For;
End-For;
If &I > 0 Then
   MessageBox(0, "", 0, 0, "Error.  Duplicate values are not allowed.");
End-If;

Guest's picture
Guest
Re: Check for Data Duplicates on a Grid

Thanks!

Guest's picture
Guest
Re: Check for Data Duplicates on a Grid

I would recommend that do not use the messagebox function ..it show's the error but allowed to save the data .use error function instead of messagebox function . error function terminates the transaction until we correct the data..