Date Range In The Future

[jeffs88keys]

This is a travel agency application.  We have a table with the field "arrive_date", which of course is the date that a guest arrives at their destination.

 

I am trying to generate a report that will return all records where the "arrive_date" is 180-201 days in the future.

 

I'm fairly new to this, but I'm not seeing where the date/time criteria allows for this type of criteria calculation.  Any help would be greatly appreciated.

[GoCubbies]

Have you tried filtering by arrive_date and adding criteria in the Configure FIltering Fields and using a Comparison type of: Next X days.  That should allow you to filter for x to y day range in future.

[jeffs88keys]

Thanks, GoCubbies, but what it appears your solution is doing is grabbing all records today through today+180 (criteria 1) as x and then ignoring criteria 2.  I appreciate the thought, and I get the logic behind what you are attempting.  It just appears the system isn't allowing for that.

[Jan]

Hello jeffs88keys,

 

It seems, I have found a solution.

The idea is:

On the Select Search Fields page, add the field with dates.

On the Configure Search Fields, add a New Criteria and check the AND radio-button.

Set the "Greater Than or Equal" Comparison Type for the first criteria  and "Less Than or Equal" one for the second criteria.

Add the Java Script code that hide the Search form, enters Today + 180 to the first field, Today + 210 to the second, and then autosubmit the Search.

 

To hide the Search Form, add a Header&Footer element, enter to the Header the following code:

<div align="center"> <p>Processing...</p></div>

And enter the following code to the Footer:

<SCRIPT LANGUAGE="JavaScript">
if(document.getElementById("caspioform")) 
{
   document.getElementById("caspioform").style.display = 'none';
   var nDate = new Date(); 
   var base_day = nDate.getDate();
   var first_day = base_day + 180;
   var first_date = new Date (nDate.setDate(first_day));
   var d_month = first_date.getMonth() + 1;
   if (d_month<10) {d_month = "0" + d_month;}
   var d_day = first_date.getDate();
   if (d_day<10) {d_day = "0" + d_day;}
   var d_year = first_date.getFullYear();
   var str_date = d_month + "/" +  d_day + "/" + d_year;
   document.getElementById("Value1_1").value=str_date;

   var sDate = new Date();
   var last_day = base_day + 210;
   var last_date = new Date (sDate.setDate(last_day));
   d_month = last_date.getMonth() + 1;
   if (d_month<10) {d_month = "0" + d_month;}
   d_day = last_date.getDate();
   if (d_day<10) {d_day = "0" + d_day;}
   d_year = last_date.getFullYear();
   str_date = d_month + "/" +  d_day + "/" + d_year;
   document.getElementById("Value1_2").value=str_date;

  setTimeout('document.forms["caspioform"].submit()',1000); 
} 
</SCRIPT>

Please note, that Value1_1 and Value1_2 will be correct if only two criteria are present on the "Configure Search Fields" step.

 

And I am not sure that dates should be like "Today + 180 days", so the following code insert dates like "Today + 6 months" and "Today + 7 months":

<SCRIPT LANGUAGE="JavaScript">
if(document.getElementById("caspioform")) 
{
  document.getElementById("caspioform").style.display = 'none';
   var nDate = new Date(); 
   var base_month = nDate.getMonth();
   var first_day = base_month + 6;
   var first_date = new Date (nDate.setMonth(first_day));
   var d_month = first_date.getMonth() + 1;
   if (d_month<10) {d_month = "0" + d_month;}
   var d_day = first_date.getDate();
   if (d_day<10) {d_day = "0" + d_day;}
   var d_year = first_date.getFullYear();
   var str_date = d_month + "/" +  d_day + "/" + d_year;
   document.getElementById("Value1_1").value=str_date;

   var sDate = new Date();
   var last_day = base_month + 7;
   var last_date = new Date (sDate.setMonth(last_day));
   d_month = last_date.getMonth() + 1;
   if (d_month<10) {d_month = "0" + d_month;}
   d_day = last_date.getDate();
   if (d_day<10) {d_day = "0" + d_day;}
   d_year = last_date.getFullYear();
   str_date = d_month + "/" +  d_day + "/" + d_year;
   document.getElementById("Value1_2").value=str_date;
  setTimeout('document.forms["caspioform"].submit()',1000); 
} 
</SCRIPT>

[jeffs88keys]

Thank you very much !!   I just noticed the reply this evening.  I'll work on it this week and let you know how it goes.  Thanks again for the time !!!

[PotatoMato]

Hi! Just to update, Caspio has added an article to disable past or future dates: https://howto.caspio.com/tech-tips-and-articles/disabling-past-or-future-dates-in-calendar/

[Kronos]

Hi everyone, just to add to this thread:

Comparison Types in Report DataPages

Hope this helps!