Is it possible to count records in a table using javascript instead of a calculated field so the comparison is done right before submission? Or, is there a way to force fields with calculated values to refresh prior to submission? Possibly a custom button that refreshes the fields then submits the form?
I've run into a snag using calculated values. Virtual15 is a calculated value and counts how many of a courseID are scheduled for the selected Lesson Date
SELECT Count(Lessons_courseID)
FROM _v_active_lessons
WHERE Lessons_courseID=target.[@field:Lessons_courseID] AND Lesson_Date=target.[@field:Lesson_Date]
Virtual8 is also a calculated value that uses CASE WHEN to determine if it's ok to schedule. We can only teach one of each courseID per day. The case statement is checking for other restrictions as well, but they are not dependent on a record count from the active_lessons table.
CASE
WHEN [@cbParamVirtual15] <> 0
THEN 'This lesson is scheduled for another teacher today. Please select a different day.'
ELSE 'ok to schedule'
END
Then, I use this javascript in the footer to prevent form submission
<script type="text/javascript">
document.addEventListener('BeforeFormSubmit', function(event) {
// prevent form submit if not ok to schedule // if user forget to choose required fields date or time, proceed with submission to generate required field error // alertok variable prevents blank alert box
var Input1 = document.querySelector("span[id^='cbParamVirtual8']").innerText;
var alertok = 1;
if (Input1 == 'ok to schedule' || Input1 == ' ')
{}
else
{event.preventDefault();
if (alertok) {
alert(Input1)}
alertok = 0;
}
});
</script>
The issue: If the lesson is not already scheduled for a selected date, two users can simultaneously select the same lesson for the same date and both will get the 'ok to schedule'. This allows both users to submit the form and I end up with double signups.