Data Validation

[roelmagpoc]

Is there a way to validate data entered without the use of dropdowns and cascading dropdowns?

 

e.g. If a Site ID entered is not part of the related table, then error message after data entry or restricted during submission of record.

[MayMusic]

It looks more like it needs server side programming. You can try to contact Caspio developers http://www.caspio.com/support/professional-services.aspx

[ShWolf]

Hi Heisenberg,

 

One-to-many relationship with 'Enforce referential integrity' option seems can help you (http://howto.caspio.com/tables/table-relationships.html).

[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]) = [@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.

[SushiPizza]

Hi,

There is a similar question in this post. Maybe you will find it useful.

Regards,
SP

[futurist]

Just to add, if you also wish to validate value entered in an input field to make sure that it follows this format: www.websitename.com

Then you can use this code:

 

<script type="text/javascript">

document.addEventListener('BeforeFormSubmit', function(event) {

var inputField = document.querySelector('#InsertRecordNameofField');

  const value = inputField.value;
  const regex = /^www\.[a-zA-Z0-9-]+\.[a-zA-Z]{2,}$/;

if (!regex.test(value)) {
event.preventDefault();
alert('Invalid Submission!');

} else {

document.form["caspioform"].submit();

}

});
</script>

Make sure to replace "NameofField" with the actual name of the field that you want to validate before submitting.

[cheonsa]

Hi!

Another option is to hide the submit button if the condition is unmet. You can add this in the Footer of the DataPage.

<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>

 

cbParamVirtual1 is the Virtual Field used for checking the data entered.