Handling Duplicate Data Table Entries

[NarayanJr]

I am trying to make it so a user cannot submit the same exact information twice.  I have a data page that accepts a, Name, Country, State, and City and is added to a table.  However you can resubmit the same info over and over again filling the table with redundant information.  I thought about setting the table fields to Unique but that doesn't really do what I want.  I need to be able to enter very similar data as different entries, but not allow identical data.  So an entry of,  Remote Diagnostics, USA, New Jersey, and Toms River, can not be entered twice but allow that entry and Remote Diagnostics, USA, Georgia, and Toms River.  What I need is a Unique entry and not just a Unique field.

 

I tried searching for this but was unable to find anything.  If anyone has an answer or can link me to something I missed it would be much appreciated. 

 

Thank You

 

 

[kRv]

Hi NarayanJr,

 

In your case, composite unique index, create by four mentioned fields, is right solution, but Caspio don't support unique indexes by default. You can contact with support team and ask about some PS development.  Also if this feature important for you, please post new idea on Caspio IdeaBox

 

If you want know more technical details please see following link Unique Indexes

 

Thanks

[NarayanJr]

Thanks for the information delegate.  I took your advice and added it to the IdeaBox, hopefully it will become a feature in the future.

[MayMusic]

It is also good if others vote to the feature 

[telepet]

On 12/3/2013 at 7:33 PM, MayMusic said:

It is also good if others vote to the feature 

Looks like this feature is planned.  Wouldn't hurt to vote and nudge this feature along though.  Here's the Idea Box link.  (Hover around blank area if you can't see the link-- I believe the forum CSS is making it appear invisible for some.)

 

http://ideabox.caspio.com/forums/164206-caspio-bridge/suggestions/5074733-unique-data-table-entries-not-just-unique-fields

 

[RuisiHansamu]

You can also check the standard way that Caspio have in this article: https://howto.caspio.com/troubleshooting/errors-and-messages/#:~:text=Duplicates values are not allowed,enter a duplicate email address.

[cheonsa]

The first thing that you can do is to add a Formula field in the Table to concatenate all fields. You can use this field as a Unique field.

[@field:Name] + [@field:Country]  +[@field:State] +[@field:City]

Then, in the DataPage, you need to add two Virtual Fields and set them to Calculated Values.

In the First Virtual Field, you can use the same formula that you have in the Formula Field in the Table.

[@field:Name] + [@field:Country]  +[@field:State] +[@field:City]

Then, for the Second Virtual field, you can check if the value of Virtual 1 is already existing in the Table.

CASE WHEN 
(SELECT COUNT(FORMULA_FIELD_NAME) FROM A_1 WHERE '[@cbParamVirtual1]'= target.[@field:FORMULA_FIELD_NAME]) > 0  THEN
'Existing' 
ELSE
'Not Existing'
END

Lastly, to prevent the submission if the value of the Virtual Field 2 is Existing, you need to paste this code in the Footer of the DataPage. Make sure to disable the HTML editor before adding this code:

<script>
  document.addEventListener('BeforeFormSubmit', function(event) {

     let checkValue = document.querySelector("input[id*='cbParamVirtual2']").value;
    
     if (checkValue == 'Existing') {
     event.preventDefault();
     alert("You are not authorized to create this account.");
    }
  })
</script>

Hope this helps!  

[Kurumi]

Hello! If you want to highlight duplicate records/column in the Results Page, you can use this workaround:

1. Insert a Calculated Field that will check the record (specific field) if it has duplicates or not. The formula would be:

CASE WHEN (SELECT COUNT(FIELD_NAME) FROM TABLE_NAME WHERE FIELD_NAME=target.[@field:FIELD_NAME]) > 1
THEN 'Has Duplicate'
ELSE
'No Duplicate'
END

This formula uses a CASE WHEN statement that will determine if the COUNT of the field is more than 1. More than 1 means that the record has duplicates.

References:
- https://howto.caspio.com/function-reference/#:~:text=Return-,Case,-Evaluates a list
- https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

2. Insert an HTML Block that will highlight the column. Insert this code:

<div id="visi[@cbRecordIndex#]"></div>

<script>

var isi = document.getElementById("visi[@cbRecordIndex#]");

if('[@calcfield:1]' == 'Has Duplicate'){
isi.parentNode.parentNode.querySelector("td:nth-child(4)").style.cssText = 'background: yellow';
}

</script>

Make sure to disable the HTML Editor on the Advanced tab before pasting the code: https://howto.caspio.com/datapages/datapage-components/disabling-html-editor-in-datapage-header-footer-and-html-blocks/

The code means that it will check the value of the Calculated Field and highlight the specific column 4 if it meets the criteria. You can change the number 4 based on the arrangement of columns. To know the number, you can select the column and then copy its selector. For more information: https://www.scrapingbee.com/tutorials/how-to-extract-css-selectors-using-chrome/#:~:text=The process is very simple,and click on Copy selector. 

3. Hide the column Calculated Field. The solution comes from this post: