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.