Filter multiple Date fields using Start and End Date

[BeffreyJezos]

I have read about adding criteria to my search field if I want to use a range for filtering or searching. However, my problem is I need to check multiple date fields and adding criteria to all of them will make my search form so messy and contain a lot of fields. Is there a way to use just two date pickers to select the start date and end date and select all records that has any of my date fields inside that range?

Any suggestion is appreciated. Thanks!

[Tubby]

You can use javascript to get the copy the value of the first date field's criteria and pass it to the other fields then hide them so it would show only the first date field with 2 criteria (start and end).

There is quite a lot of steps to follow here but it worked for me.

First, add two Criteria to all of them. One for Start Date, another for End Date.

Configure one criteria as “Greater than or Equal” and the other as “Less Than or Equal” to include all Dates within the selected range. This goes for every single one of the date fields.

Afterwards, hide all of the Date Fields except for the first Date field using what I call the "HTML block method". Like this:



Just use the same code I used on HTML Block 1 then put </div></div> to HTML Block 2. This creates a separate section for those fields and hides them.

Next, add a Header and Footer block and put the script below in the Footer. Dont forget to disable HTML editor:

<script>
document.addEventListener('input', function drange() {
document.getElementById('Value3_1').value = document.getElementById('Value1_1').value;
document.getElementById('Value3_2').value = document.getElementById('Value1_2').value;
document.getElementById('Value4_1').value = document.getElementById('Value1_1').value;
document.getElementById('Value4_2').value = document.getElementById('Value1_2').value;
});
</script>

But note that this is assuming that the Date fields are positioned at the very top of the DataPage Wizard. The ID 'Value3_1' can change depending on the position. A good way to check is to remove the codes in the HTML blocks to unhide the fields first and inspect the element using F12. 

Lastly, go to the Logic section and set the Logic to be multiple AND blocks inside a big OR blocks like this:


This basically copies and pastes the dates in the first date fields to the others pass them secretly.

[Tubby]

Here is a reference to the "HTML block method" : https://howto.caspio.com/tech-tips-and-articles/common-customizations/how-to-hide-fields-in-datapages/

[BeffreyJezos]

Let me try this and hopefully it works for me.

On 10/9/2021 at 4:40 PM, Tubby said:

You can use javascript to get the copy the value of the first date field's criteria and pass it to the other fields then hide them so it would show only the first date field with 2 criteria (start and end).

There is quite a lot of steps to follow here but it worked for me.

First, add two Criteria to all of them. One for Start Date, another for End Date.

Configure one criteria as “Greater than or Equal” and the other as “Less Than or Equal” to include all Dates within the selected range. This goes for every single one of the date fields.

Afterwards, hide all of the Date Fields except for the first Date field using what I call the "HTML block method". Like this:



Just use the same code I used on HTML Block 1 then put </div></div> to HTML Block 2. This creates a separate section for those fields and hides them.

Next, add a Header and Footer block and put the script below in the Footer. Dont forget to disable HTML editor:

<script>
document.addEventListener('input', function drange() {
document.getElementById('Value3_1').value = document.getElementById('Value1_1').value;
document.getElementById('Value3_2').value = document.getElementById('Value1_2').value;
document.getElementById('Value4_1').value = document.getElementById('Value1_1').value;
document.getElementById('Value4_2').value = document.getElementById('Value1_2').value;
});
</script>

But note that this is assuming that the Date fields are positioned at the very top of the DataPage Wizard. The ID 'Value3_1' can change depending on the position. A good way to check is to remove the codes in the HTML blocks to unhide the fields first and inspect the element using F12. 

Lastly, go to the Logic section and set the Logic to be multiple AND blocks inside a big OR blocks like this:


This basically copies and pastes the dates in the first date fields to the others pass them secretly.

 

[BeffreyJezos]

Yup. Works fine for me but I sure need to remember that every field that I add on the search form might mess up the ID of the fields.