Search with custom date ranges

[CoopperBackpack]

I would like to share an example of the solution to create a search with custom date ranges.

Use case: need to filter records based on different conditions, for example, this week, previous week, this month, previous month.

For example, there is the field in the table with the Date/Time data type named 'Date_Time' and the search page should look like this:

[CoopperBackpack]

The first option is to apply JavaScript.

1) Add a Virtual field that can be Dropdown or Listbox with the list os options for search

 

2) Add Criteria for the Date/Time field. They should be combined by the 'AND' logical operator. 

 

This article can be helpful https://howto.caspio.com/datapages/reports/how-to-add-today-to-after-now-or-next-x-days-criteria/

Criteria 1 should be Hidden, have the 'Greater than or equal' comparison type, and the 'Date' precision  

 

Criteria 2 should be Hidden, have the 'Less than or equal' comparison type, and the 'Date' precision  

 

3) The idea is to populate these Criteria with corresponding dates depending on the selected date range in the Virtual field.

For that, the easiest solution is to use the 'moment.js' library. It is available via the link https://momentjs.com/

- Copy the script from the 'moment.js' library https://momentjs.com/downloads/moment.min.js

- Add the App parameter in the application 

This article can be helpful https://howto.caspio.com/apps/app-parameters/app-parameters/

App parameter type should be 'Long text', the value is the script copied form the library.

 

4) Add the JS code in the Footer of the Search page. Disable the HTML editor on the Advanced tab before pasting.

In this script Monday is the 1st day of the week.

<script>
//moment.js library, add parameter with correct name
[@app:moment_js]

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

    const listboxField = document.querySelector('#cbParamVirtual1'); // virtual field, change the name if needed
    const dateFrom = document.querySelector('#Value2_1'); // Criteria 1 field, its ID should be changed if needed
    const dateTo = document.querySelector('#Value2_2'); //Criteria 2 field, its ID should be changed if needed

    const mondayThisWeek = moment().startOf('isoWeek').format('L');
    const sundayThisWeek = moment().endOf('isoWeek').format('L');

    const mondayPreviousWeek = moment().subtract(1, 'weeks').startOf('isoWeek').format('L');
    const sundayPreviousWeek = moment().subtract(1, 'weeks').endOf('isoWeek').format('L');

    const firstDayThisMonth = moment().startOf('month').format('L');
    const lastDayThisMonth = moment().endOf('month').format('L');

    const firstDayPreviousMonth = moment().subtract(1,'months').startOf('month').format('L');
    const lastDayPreviousMonth = moment().subtract(1,'months').endOf('month').format('L');

    listboxField.addEventListener('change', (event) =>{
        const options = Array.from(event.target.selectedOptions);
        let selectedValue = options[0].value;
        
        switch (selectedValue) {
        case 'This week':
            dateFrom.value = mondayThisWeek;
            dateTo.value = sundayThisWeek;
            break;
        case 'Previous week':
            dateFrom.value = mondayPreviousWeek;
            dateTo.value = sundayPreviousWeek;
            break;
        case 'This month':
            dateFrom.value = firstDayThisMonth;
            dateTo.value = lastDayThisMonth;
            break;
        case 'Previous month':
            dateFrom.value = firstDayPreviousMonth ;
            dateTo.value = lastDayPreviousMonth ;
            break;
        case '':
            dateFrom.value = '';
            dateTo.value = '';
            break;
        }
    })
});
</script>

[CoopperBackpack]

The second option is to use a Lookup table updated by Task. 

This is the easier solution if Tasks are included in the plan.

1) Create a Lookup table with the following table  design

2) Populate the 'Date_name' field

3) Create a Task that runs daily to update the dates

4) On the Search page use the 'Range' Comparison type with the following logic:

 

Result: