Blog

Data - How to filter records by a specific date, today's date, or range of dates

A common requirement to filter a list of records by date. This post describes the formula to carry out three common tasks - how to filter records by a specific date, today's date, or a range of dates. We also cover the formula to return all dates, if the user does not select a date.

A common requirement is to filter tables/lists of data by date. This post provides examples of how to carry out this task and to demonstrate,we'll take the example of a SharePoint list of issues. This list includes several SharePoint columns that are of type 'Date and Time'.

1 - Filtering records by today's date

A common problem that some app builders encounter is when they try to filter source records that include a time component (eg, columns of data type Date and Time). In this case, it's not possible to use the equality (=) operator. Instead, we must use the greater than/less than operators to return records between 00:00 and 23:59 of the target date.

On a browse screen that displays a list of records, we can filter the items in a gallery control to show only the records that were created today by setting the items property as follows:

Filter(Issue, 
       CreateDateTime >= Today(),  
       CreateDateTime < DateAdd(Today(), 1, Days)
)


2 - Filtering records by a specific date

To filter the records in the gallery by a user specified (or specific date), we can apply a similar technique. We add a date picker control to allow the user to enter a date - dteSearch in this example.

We can then modify the items property of the gallery control as follows:

Filter(Issue, 
       CreateDateTime >= dteSearch.SelectedDate,  
       CreateDateTime < DateAdd(dteSearch.SelectedDate, 1, Days)
)


3 - How to show all records when the user does not enter a date

We can adapt the above example so that if the user does not enter a date, the gallery will display all records. We set the isEditable property of the date picker control true, which will enable the user to clear the date.


We can then modify the items property of the gallery control as follows:

 

Filter(Issue, 
      (
       CreateDateTime >= dteSearch.SelectedDate And  
       CreateDateTime < DateAdd(dteSearch.SelectedDate, 1, Days)
      ) 
      Or IsBlank(dteSearch.SelectedDate)
)

4 - Filtering records by a range of dates (start date and end date)

To filter records by a user specified start and end date, we add a set of date picker controls to allow the user to enter a start date and end date. As an example, we'll add two date pickers called dteStartDate and dteEndDate.

We then modify the items property of the gallery control as follows:

  

Filter(Issue,
       CreateDateTime >= dteStartDate.SelectedDate,  
       CreateDateTime < DateAdd(dteEndDate.SelectedDate, -1, Days)
)

The natural adaptation of this is to modify the behaviour so that if a user does not enter either a start or end date, the gallery will display all dates greater than the user specified start date, or less than the user specified end date respectively. Here's the formula to carry out this adaptation.
 
Filter(Issue, 
      (CreateDateTime >= dteStartDate.SelectedDate 
       Or IsBlank(dteStartDate.SelectedDate)
      )
      And
     (CreateDateTime <  DateAdd(dteEndDate.SelectedDate, 1, Days) 
      Or IsBlank(dteEndDate.SelectedDate)
     )
)



4 - How to provide a radio option to show all records, or only records created today

Finally, here's an example of how to build a feature that enables a user to either show all records, or only the records that were created today through a radio option.

First, we add a radio control and set the items property to the following:

["Today","All dates"]


We then modify the items property of the gallery control so that it conditionally displays results depending on the selected radio option.:

 

If(rdoDateSelect.Selected.Value="Today",
   Filter(Issue, 
       CreateDateTime >= Today(),  
       CreateDateTime < DateAdd(Today(), -1, Days)
   ),
   Issue
)

Conclusion

A common requirement is to build screens that filter records by dates. This post highlighted several typical examples, including how to show records that were created today, records that were created on a specific date, and records that fall between a start and end date.
Previous
Next
Related posts