Using prompt to make change to record before inserting new record

[philmills]

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?

[arbei]

You may Create Your Own API Action to check the input value first.

Also read viewtopic.php?t=49447.