Filter Between two date fields

[HayleighFisher]

Hello,

We are trying to create a selection on a layer based on a specified date or date range. For reference we have two date fields we want to compare between: Start Date and End Date. The user wants to be able to select a date (or date range) and display any records that are active between those dates.

The layer is work projects and the user wants to enter a date (or date range) to select out any projects that are active (within the start or end date). For example if there is a project with a start date of 1/1/2020 and end date of 12/23/2020 if the user enters date 3/5/2020 then that project should be returned. We have tried using the filter widget, however there are several issues with this:

1. The date picker is missing certain dates when using the between option (October missing the 31st)

2. The between functionality is not returning the expected records-- see below, it is returning records outside the selected dates

3. We do not want the user to have to enter the date range twice. Ideally they could just enter a single date (or date range) and the filter would return any "Active" projects (i.e. compare the start and end date and if the user selection is within that range then it is active).

How we are able to set it up now (not working/not user friendly)

Thank you for any help,

Hayleigh

[jcarlson]

Your date picker looks like it's just not including Saturdays in general, which is... odd.

For avoiding the date range issue, the closest you might be able to get is to use a Group Filter, which can act on multiple fields using a single input. Unfortunately, you can only specify a single operator per group, so you would need a single group for the beginning date of the range, and a second for the end date.

Now, that wouldn't be a problem, except that the widget only shows a single group at a time...

So, less typing of dates, but more clicking. Maybe worse than the current situation, since you can't see both dates at once.

A Slightly Different Idea

What if instead of having the date range on both fields, you simply filtered start date as "on or after" and end date as "on or before" and set the filter expression to "match all of the following expressions". Assuming that the value in the end date is always after the before date, such a filter ought to work as intended. By adjusting the filter label text, it can still look like a range, too.

[HayleighFisher]

Hello Josh,

Thank you for looking into this! Yes we are experiencing the issue with the date picker in WAB Developer and In the OOTB web app builder available in portal. We submitted a ticket with Esri to look into this further. 

I also thought of using the Group Filter but as you also mentioned for this particular case the user group wants to avoid additional clicking around. 

As for your other idea "filtered start date as "on or after" and end date as "on or before" and set the filter expression to "match all of the following expressions""

We also tried this but unfortunately the issue is that sometimes these projects may fall before or after the queried start/end date and the project may still be considered active. For example if the user enters a start date of 3/1/2020 and an end date of 6/1/2020 there may be projects that starts on 2/1/2020 and ends on 5/1/2020 are therefore still "active" but being omitted by the filter. The issue is we want to basically compare the start and end date to determine which projects are active within a certain date range or specific date. It seems we may need to create a custom widget to do so but was hoping someone else already created something similar. 

Thank you again for taking the time to look into this!

-Hayleigh

[jcarlson]

Ah, that's right. It's not an easy thing to filter, but it doesn't seem like it would be too tricky to have a custom widget that applied the entered date values against two fields.

Have you looked at designating the two fields as the "start" and "end" dates in the layer's time settings and using a time slider widget?

[HayleighFisher]

Yes we are using the time slider as well but unfortunately the customer always wants more, so they still want the filtering capability directly. We have started working on a custom solution, thank you again for your help and input!

- Hayleigh 

[Samd603]

Did you find a solution to this problem?