Dynamic calculations and validation in tabular form in ORACLE APEX 5.0

Question

I am building the tabular form in oracle apex where I need to validate the start date and end date.

I have date picker field in tabular form for both start date and end date and the start date should not accept the date less 45 days from system date(today's date) and it should not be greater than system date.

I am getting the error even though I have given start date as today's date. I need the alert, when start_date is less than (today's date - 45 days)

I am using ORACLE APEX 5.0 version

On the page I have defined this function in the "Function and Global Variable Declaration" section:

function f_validate_sd(pThis) { 

  var row_id      = pThis.id.substr(4);  
  var start_date  = $('#f10_'+row_id).val();
  //var end_date    = $('#f11_'+row_id).val();


 var currentDate = new Date();
 var dy          = currentDate.getDate();
 var month       = currentDate.getMonth() + 1;
 var year        = currentDate.getFullYear();
 var sd          = (  dy + "-" + month + "-" + year);

 var subdate     = new Date(sd); 
 var numberOfDaysToAdd = 45;
 subdate.setDate(subdate.getDate() - numberOfDaysToAdd );
 var dd          = subdate.getDate();
 var mm          = subdate.getMonth() + 1;
 var yy          = subdate.getFullYear();
 var sub         = ( dd + "-" + mm + "-" + yy );

alert(start_date);
    alert(sub);
alert(start_date - sub );
    alert(sd);

if(start_date < sub  ) {
                 alert("START DATE should be within 45 days from todays date");
                 $('#f10_'+row_id).val(" ");}
}

In the tabular form I call this function by having add this to the "custom attributes" field of the "START_DATE" column:

onchange="javascript:f_validate_sd(this);"

When I run my page and change the date, the message "NaN" as the third alert.

I've set up an example application here: DEMO

You can check it on apex.oracle.com by using these credentials:

Workspace: APEX_CHECKBOX
Username: appuser
PWD: demo

Answer 1

Defining the check:
Use some real date arithmetic in JavaScript.

Page > Function and Global Variable Declaration

I've modified some of my code I've used in a previous answer: Comparing dates using Dynamic Action on DatePicker Oracle Apex

function isBetweenNowAnd(pDateItem, pRange){  
  function getRange(pRange){ 
    var future = new Date();
    future.setDate(future.getDate() + pRange);
    return future;
  };

  function cutTime(pDate){
    return new Date(pDate.getFullYear(), pDate.getMonth(), pDate.getDate());
  };

  // check if pDateItem leads to a selection
  // check if it is a datepicker
  // check if a date has been selected
  // if pRange is at least a number
  if ( $(pDateItem).length 
       && $(pDateItem).data("datepicker")
       && $(pDateItem).datepicker("getDate") !== null 
       && parseInt(pRange) !== "NaN"
     ) 
  {        
    var future = getRange(pRange);
    var check = $(pDateItem).datepicker("getDate");
    var one = cutTime(check);
    var two = cutTime(future);

    return one <= two;
  };
  return false;
}

I really hate using "onxxx" attributes. This is just obfuscation within your own application. If you don't know how to use javascript or jQuery then read up instead of going the easy way out with these annoying attributes.
This Dynamic action will fire on change of the start date, and will only fire the true actions when the condition in the expression is fulfilled: the item is not empty (don't fire when user blanks the selection) and the date is beyond the 45 day limit.
The first action then shows the alert, and the second action will blank out the field.

Dynamic action - Check bounds

• Event: Change
• Selection Type: jQuery selector
• jQuery Selector: td[headers=START_DATE] input.hasDatepicker
• Condition: JavaScript Expression
• Expression: !apex.item(this.triggeringElement).isEmpty() && !isBetweenNowAnd(this.triggeringElement, 45)

True actions:

Action 1: Alert

• Text: Start Date has to be within 45 days of today!
• Fire on page load: No

Action 2: Set Value

• Set Type: JavaScript Expression
• Expression: ""
• Suppress Change Event: Yes
• Fire on page load: No
• Affected Elements: Triggering Element

---

UX: don't let users select a date past today + 45 through the UI to start with With the previous DA you block manual changes on the item (changing the date text not using the popup) and also selections on the UI, but you can also disable non-applicable dates in the datepicker.
This DA will fire after the refresh of the tabular form (eg pagination) and also on page load. It will set the maximum selectable date of the datepicker.

Dynamic action - Check bounds

• Event: After Refresh
• Selection Type: Region
• Region: (select the tabular form region)

Action: Execute JavaScript

• Code:

  $("td[headers=START_DATE] input.hasDatepicker").each(function(){
    $(this).data("datepicker").settings.maxDate = "+45"; 
  })
  

• No affected elements

• Fire on page load: Yes

(Anyone wondering why I'm not using a direct call to $("...").datepicker("option","maxDate","+45") : this messes up the datepicker's visuals in Universal Theme as it removes classes from the dialog button)

---

Take in account that JavaScript can be circumvented. It's not hard to manipulate the date and be able to save it. However, it's no real use trying to add a server validation as you can only specify "for created and modified rows" - which would trigger the validation for a non-related change on said record. But you should be aware. You could use a validation which checks the date versus the "creation" date of the record if you have that audit column on your table.

---

I've built these things in your linked application in "PAGE 3 - Update TOM" so you can check it there.