Date Range Search Fields To Have Dates From X Years Ago Based on the Selected X Years on a Virtual Dropdown Field

[futurist]

I have a Tabular Report DataPage with a search form. This search form has a date range field that allows me to search for records that fall under this range - so that's From and To fields. I also have a dropdown search field containing options such as 5, 10, 15, etc (this will be  a virtual field). This number represents the records from X years ago I want to search for. For example, I chose 10, I want to get all the record from 10 yrs ago, and so on. I also want the search form to be more interactive so that, if I chose the option 10 from the dropdown, I want the From and To fields to automatically have the value of the date ten years ago, so (in the time of this writing which is 15/02/2023), From and To fields would have values of 15/02/2013, and that changes every time I chose a different option on the dropdown field.

 

I was able to achieve this through JavaScript. Paste the following code on the Footer of your search form:

 

<script src="https://cdnjs.cloudflare.com/ajax/libs/datejs/1.0/date.min.js"></script>
<script src="https://momentjs.com/downloads/moment.min.js"></script>
<script>

document.addEventListener('DataPageReady', function (event) {

const selectElement = document.querySelector('#cbParamVirtual1');

selectElement.addEventListener('change', (event) => {
var selectedoption = event.target.value;

var from = document.querySelector("#Value1_1");
var to = document.querySelector("#Value1_2")

var date = new Date().toString('MM/dd');
const currentYear = new Date().getFullYear();

from.value = moment(new Date(new Date().setFullYear(new Date().getFullYear() - selectedoption ))).format('MM/DD/YYYY');
to.value = moment(new Date(new Date().setFullYear(new Date().getFullYear() - selectedoption ))).format('MM/DD/YYYY');

});

});
</script>

Note: cbParamVirtual1 refers to the virtual dropdown field containing the options. In my case, it's the 1st virtual field so it's cbParamVirtual1. You simply need to change the "1" to have it refer to the right virtual field.

Value1_1 and Value1_2 refer to the From and To fields, respectively. 

 

You also have to make sure that the logical operator of your From and To fields are set to AND, and the Comparison Type for From is "Greater than or Equal" while for "To" it should be "Less than or equal"