I’m using Row_Inserting to check if a possible conflict exists before a record is added.
In my case the conflict would be a previous record’s EndDate is later than StartDate of the new record.This code is working:
// Row Inserting event
function Row_Inserting($rsold, &$rsnew)
{
// Enter your code here
//Check if similar record exists before adding this one
// Get the values from the new record
$fk_StudentID = $rsnew["fk_StudentID"];
$fk_School = getSchoolID(); // Assuming you have a function to get the current school ID
$newStartDate = $rsnew["StartDate"]; // Get the StartDate of the new record
// Build SQL query to check for existing records
$sql = "SELECT * FROM Students_StudyPlans
WHERE fk_StudentID = '" . AdjustSql($fk_StudentID) . "'
AND fk_School = '" . AdjustSql($fk_School) . "'
AND EndDate > '" . date('Y-m-d') . "'";
// Execute the query
$rs = ExecuteQuery($sql)->fetch();
// Check if a record already exists
if ($rs) {
// Record exists, store its ID in a session variable for later
$_SESSION["existing_record"] = $rs["StudyPlanID"]; // Store the ID of the existing record
// Ask the user if they want to update the EndDate of the existing record to one day before new StartDate
$this->setFailureMessage("Conflicting study plan detected");
return false; // Stop insertion for now
}
// Check if the user confirmed to update the existing record's EndDate
if ($_POST["action"] == "update_existing_enddate") {
// Get the ID of the existing record from the session
$existingRecordID = $_SESSION["existing_record"];
// Calculate the new EndDate as one day before the new record's StartDate
$newEndDate = date('Y-m-d', strtotime($newStartDate . ' -1 day'));
// Update the EndDate of the existing record
$sqlUpdate = "UPDATE Students_StudyPlans SET EndDate = '" . $newEndDate . "' WHERE StudyPlanID = " . AdjustSql($existingRecordID);
ExecuteUpdate($sqlUpdate);
// Clear the session variable after the update
unset($_SESSION["existing_record"]);
// Now allow the new record to be inserted
return true;
}
// To cancel, set return value to false
return true;
}
But now rather than expecting the user to manually go and correct the previous record, before re-adding the new record, I would like to give the user chance to automatically fix the date on the previous record before attempting to add the new record again. I tried to use Form_CustomValidate to do that, but its not working.here’s the code for that.
function (fobj) { // DO NOT CHANGE THIS LINE! (except for adding "async" keyword)!
// CHATGPT - put your code in here
// Get the failure message
var failureMessage = ew.language.phrase("FailureMessage");
// Check if the failure message contains the specific prompt text
if (failureMessage && failureMessage.indexOf("Would you like") !== -1) {
// Show a confirmation prompt to the user
var confirmUpdate = confirm("This student already has a current study plan. It must be closed before adding a new one. Would you like to update the previous study plan to end one day before the start of this one?");
if (confirmUpdate) {
// If user confirms, set action to update the existing record's EndDate
fobj.action.value = "update_existing_enddate";
} else {
// If user cancels, stop the form submission
return false;
}
}
// Allow the form to proceed otherwise
return true;
}
Is there a better way?