Data validation for calculations

[PaulBI]

Good day,

Does anyone know how to enforce field rules. On a submission form I have three fields FD_1, FD_2 and FD_3 and I want to make sure that when I add the fields together the result is not greater than 100.

Basically (FD_1 + FD_2 + FD_3 <= 100)

Thank you 
 

[futurist]

Hi,

You can achieve this with a bit of JavaScript.

 

1) On your Submission Form DataPage, go to Configure Fields, add a Header & Footer.

2) Select the Footer, disable the HTML editor and paste the following:

 

<script>

document.addEventListener("BeforeFormSubmit", function(e){
var sum= parseInt(document.getElementById("InsertRecordFD_1").value) + parseInt(document.getElementById("InsertRecordFD_2").value) + parseInt(document.getElementById("InsertRecordFD_3").value);

 if(sum> 100)
{
e.preventDefault();
alert("Sum of fields should not exceed 100");
}

});

</script>

 

How this works is you are prevented from submitting the entry if the sum of the three fields exceed 100.

 

[Queso]

Hi, 

May I know where are the results displayed?

I encountered this one before and I am using 4 fields (1 field for sum using Calculated Value element). Those fields are Number data type. I used a CASE WHEN function on my Calculated Value element. Here is the formula I used: 

CASE WHEN ([@field:X] + [@field:Y] + [@field:Z]) <= 100

THEN ([@field:X] + [@field:Y] + [@field:Z])

ELSE 

0

END

When the sum exceeds 100, the current value of sum will become 0. I always rely to this Caspio Function Reference when I need to review some functions: https://howto.caspio.com/function-reference/

[PaulBI]

Thanks to both of you for taking the time to address my question.  Futurist your script works the way I needed it to so thank you. 

trickson I hadn't heard of CASE WHEN before but now seeing what it can do you have helped me solve another problem I was having difficulty with.

Thanks to both of you again.

 

[kpcollier]

Trying to combo this post with some others, seen below.

If 'InsertRecordStock_CartItems_Table_Quantity' field value is greater than 'cbParamVirtual7' value, I need to throw an alert and disable/recolor the Submit button. I also want to put a red border around the Quantity field.

The keyup event for Quantity is working. If the user puts in a value that is greater than Virtual 7 value, it will pop up an alert and also disable the Submit button. 

My problem is with the styling of the Submit button and the Quantity field. Also, the BeforeFormSubmit function is throwing an error, I believe because it is also trying to run the DataPageReady/Keyup event at the same time (that is where the error shows, on document.querySelector('[id*=InsertRecordStock_CartItems_Table_Quantity]').addEventListener('keyup', function)).

document.addEventListener("BeforeFormSubmit", function(e){
  var quantNum = document.querySelector("input[id^='cbParamVirtual7']").value;
  var stockNum = document.getElementById('InsertRecordStock_CartItems_Table_Quantity').value;
  
  if(stockNum = 0){
    e.preventDefault();
    alert("This item is not in stock.");
  }

});

document.addEventListener('DataPageReady', function (event) {
	document.querySelector('[id*=InsertRecordStock_CartItems_Table_Quantity]').addEventListener('keyup', function() {
    var stockNum = document.querySelector("input[id^='cbParamVirtual7']").value;
    var quantNum = document.getElementById('InsertRecordStock_CartItems_Table_Quantity');
  
    if(quantNum.value > stockNum){
      document.querySelector("input[name='Submit']").style.backgroundColor = "grey !important";
      document.querySelector("input[name='Submit']").disabled = true;
      quantNum.style.border = "2px solid red !important;";
      
      alert("Not enough in stock for your order.")

    }
  })
});

 

 

Any help would be appreciated.