Stop Form Submission Based On Calculated Field

[rhinger]

Hello All,

I am trying to create an account creation datapage that has a built in checker to make sure the last four digits of the social security number entered matches the last four in our system.

To do this, I have already created a calculated field, which gives me a YES or NO depending on if the last four digits entered on the social security match what we have on file. 

Here is that code:
 

CASE WHEN

(SELECT COUNT(*) FROM CollectionCRM WHERE Filenumber=[@field:CustomerTable_FileNumber] AND LastFour=[@field:CustomerTable_LastFour]) > 0

THEN 'YES'

ELSE
'NO'

END

I am a little stuck now. What I am trying to do is when the calculated field says YES the account is created, if it says NO, an alert is presented that says "You are not authorized to create this account." Here is what I have tried:
 

<script>

document.querySelector('#caspioform').onsubmit = function(e) {
    e.preventDefault();

var input1= document.getElementById("LastFourCheck").value;


if (input1  =="NO"){
alert('You are not authorized to create this account.');
} 


else {
this.submit();

}

}

</script>

Could somebody take a look and let me know what I am doing wrong? I hope you guys can help me out, thanks so much!

[CoopperBackpack]

Hello @rhinger,

As far as I understand this is a Submission Form DataPage ,CASE statement returns correct value and you need to fix only the JavaScript code.

1) Please use the BeforeFormSubmit Caspio event handler. 

2) If you set the "LastFourCheck" field as a Calculated Value Form Element as shown on the screenshot

then this input field on the Submission form has id that starts from InsertRecord prefix and has dynamic so-called uniqueSuffix.

Here is the example:

To get the value of this input field, please use the syntax:  document.querySelector("input[id*='InsertRecordLastFourCheck']").value

Please try to add this code in the Footer section and test:

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

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

You may want to check these articles:

https://forums.caspio.com/topic/4377-js-guide-caspio-form-elements/

https://howto.caspio.com/datapages/ajax-loading/

Please update this thread if you have additional questions.

[rhinger]

Hi @CoopperBackpack,

Thanks for your response, I appreciate you taking your time to give me such a detailed response and references.

I did go ahead and deploy the code and get it to work as intended.  I had to alter it slightly due to me using a view, but that was no issue.

Thanks again.

[Kurumi]

Hi! If you would like to automatically format SSN/Social Security Number in the Text Field, you can try these solutions:

1. From this link: https://stackoverflow.com/questions/7685175/autoformat-ssn-while-entering-the-number#:~:text=at 0%3A25-,Karl Keefer,-6361 - JavaScript

Applying in the DataPage's Footer with this code:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

function formatSSN(ssn) {

  // remove all non-dash and non-numerals
  var val = ssn.replace(/[^\d-]/g, '');

  // add the first dash if number from the second group appear
  val = val.replace(/^(\d{3})-?(\d{1,2})/, '$1-$2');

  // add the second dash if numbers from the third group appear
  val = val.replace(/^(\d{3})-?(\d{2})-?(\d{1,4})/, '$1-$2-$3');

  // remove misplaced dashes
  val = val.split('').filter((val, idx) => {
    return val !== '-' || idx === 3 || idx === 6;
  }).join('');

  // enforce max length
  return val.substring(0, 11);

}

// bind our function
document.getElementById("InsertRecordSSN").onkeyup = function(e) {
  this.value = formatSSN(this.value);

}

});

</script>

2. https://stackoverflow.com/questions/7685175/autoformat-ssn-while-entering-the-number#:~:text=at 6%3A20-,Jason Hansen,-33 - jQuery
 

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

 $('#InsertRecordSSN').keyup(function() {
          var val = this.value.replace(/\D/g, '');
          var newVal = '';
          if(val.length > 4) {
             this.value = val;
          }
          if((val.length > 3) && (val.length < 6)) {
             newVal += val.substr(0, 3) + '-';
             val = val.substr(3);
          }
          if (val.length > 5) {
             newVal += val.substr(0, 3) + '-';
             newVal += val.substr(3, 2) + '-';
             val = val.substr(5);
           }
           newVal += val;
           this.value = newVal.substring(0, 11);
        });

});

</script>

3. By using Cleave JS - https://nosir.github.io/cleave.js/

<script src="https://cdnjs.cloudflare.com/ajax/libs/cleave.js/1.6.0/cleave.min.js"></script>

<script>

var cleave = new Cleave('#InsertRecordSSN', {
    delimiter: '-',
    blocks: [3,2,4],
    numericOnly: true
});

</script>

Take note that you need to change the InsertRecordSSN based on your right field name.