Search default value = timestamp - 3 month (or xx days)

[vidierre]

I have a pivot datapages for all invoices.  I whish to hgave two search datafields like From and To where To has default today and From is 3 month backward.

My feeling is that some Javascript has to be written, but before i start to, anyone know if this can be done natively?

[CoopperBackpack]

Hello @innov2e,

I as I know it is only possible to set the Date/Time field to Timestamp:

So, it seems we need to apply JavaScript code. 

I can suggest 2 options:

1) To use only JavaScript,

2) To use an external library.

 

The first solution is to add the following code to the Footer section on the Configure Search Fields page. Please don`t forget to disable the HTML editor before pasting:

<script>
document.addEventListener('DataPageReady', function (event) {

//Select the Criteria input fields 
const dateFrom = document.querySelector("#Value1_1"); //need to change the ID depending on the order number of the field
const dateTo = document.querySelector("#Value1_2"); //need to change the ID depending on the order number of the field

//Today 
const date = new Date();

const todayDay = `${date.getDate()}`.padStart(2, 0);
const todayMonth = `${date.getMonth() + 1}`.padStart(2, 0);
const todayYear = date.getFullYear();

const today = `${todayMonth}/${todayDay}/${todayYear}`;

//Three months prior
const datePrior = new Date(date.setMonth(date.getMonth() - 3));

const priorDay = `${datePrior.getDate()}`.padStart(2, 0);
const priorMonth = `${datePrior.getMonth() + 1}`.padStart(2, 0);
const priorYear = datePrior.getFullYear();

const datePriorFinal = `${priorMonth}/${priorDay}/${priorYear}`;

// Assigning values 
dateTo.value = today;
dateFrom.value = datePriorFinal;

});
</script>

The only change is to select the correct IDs.

If the Date field is the first one on the Search form, then IDs are #Value1_1 and #Value1_2 as in the example above. 

If the Date field is the second one, then IDs are #Value2_1 and #Value2_2, etc. However, it`s better to inspect the page. 

 

! With this solution, for example, if Today is May 31, 2022, then the date that is calculated as 3 months before is March 3, 2022. 

To test the output you may pass the date like this: 

const date = new Date('May 31, 2022');

[CoopperBackpack]

The second solution is to to use the momentjs library that is available via the link https://momentjs.com/

For example, you may open moment.min.js. , copy the code:

And paste it to the Value section of the App parameter:

The code in the Footer section:

<script>

[@app:moment_min_js] // app parameter

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

//Select the Criteria input fields 
const dateFrom = document.querySelector('#Value1_1');
const dateTo = document.querySelector('#Value1_2');

//Set dates
const today = moment().format('L');
const threeMonthsPrior = moment().subtract(3, 'months').calendar();

//Assign values
dateTo.value = today;
dateFrom.value = threeMonthsPrior;

});
</script>

 

! The output with this solution is different. 

With the same example, when Today is May 31, 2022, the date that is calculated as 3 months before is February 28, 2022. 

[vidierre]

 

Thank @CoopperBackpack,  very interesting.

I'll pursue the second, it seems more elegant and natural for user point of view.

[DaveS]

Just to add something that wasn't obvious to me... until it was:

I have a search and report page and wanted to filter by a date range as well.  The criteria are the 4th and 5th items in my list...  or so I thought.  Long story short, once I realized I had to reference them as 4_1 and 4_2 it worked perfectly. 

Thanks again!