Check for duplicates and present a warning

[MelZia]

Hi all, 

I have a user entered field (ABN) which isn't required, but if it IS entered it needs to be a certain format and length and not be a duplicate with any other records.  I was looking for a crafty way to perform a check upon entry (which may involve a check field of some sort?) the use that check to show a warning or stop entry entirely.  Any ideas would be appreciated.    If at all possible I really want to avoid a multi-step process for this check. 

With thanks 

Mel

[kpcollier]

17 hours ago, MelZia said:

but if it IS entered it needs to be a certain format and length and not be a duplicate with any other records.

First thought that came to mind is making that field Unique in the table level. If you do not want any duplicates for that field, checking the 'Unique' box on the table level for that field will prevent the submission automatically when trying to enter a dup. This could be a fairly easy solution.

For the format and length, can you give us the requirements? Is this a text field?

I believe with a combination of using the Unique feature and some simple JS, you can prevent form submission based on your requirements. 

[MelZia]

G'day kpcollier, thanks for the thought.

I started with making it unique in the table design.   But some users won't have this ID number, so I need the capability to leave it blank for some users - hence the unique quality falls over at this point. (to the limit of my knowledge). 

In terms of format is just a 11 digit numerical - which is also easily constrained with table properties and DataPage validation...but once again falls over with allowing it to be left blank in some cases. 

If you have some more detail on how JS might allow both my validated non-dups and blank values I'd like to hear more? 

Mel

[NailDyanC]

Hi, just want to add in the previous comment above, you may use a calculated field that will determine if that value is already existing in your table and just add a JavaScript that will prevent the form to be submitted if the value is existing:

CASE WHEN 
(SELECT COUNT(field_name) FROM Table_name WHERE fieldname = [@field:fieldname] THEN
'Existing' 
ELSE
'Not Existing'
END

Then, you may add this JavaScript in the footer of your DataPage:

<script>

 document.addEventListener('BeforeFormSubmit', clickHandler);

function clickHandler(event) { 

var calc = document.querySelector("[id*=fieldID]").innerText;

 

if (calc  == "Not Existing"){

event.preventDefault();

window.alert("Submission BLOCKED!");

}

else {
document.location = "http://www.mysite.com/menu.html";
}

}

</script>

 

Just change the field names and table names depending on the field that you are using.

[Kurumi]

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

1. Insert a Calculated Field that will check whether the record (specific field) 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: 

I hope this helps!

[cheonsa]

Hi all!

Sharing this script with you to hide the Submit button based on the result of a Calculated Value:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {
document.querySelector("input[name='cbParamVirtual1']").addEventListener('change', function() {
if (document.querySelector("input[name='cbParamVirtual1']").value == 'Good') {
document.getElementsByClassName('cbSubmitButtonContainer')[0].style.display = 'none';
} else {
document.getElementsByClassName('cbSubmitButtonContainer')[0].style.display = 'inline';
}
});
});
</script>