, ,

PowerApps: Filtering a SharePoint List By Date

PowerApps Filter SharePoint List By Date Titelbild

Microsoft PowerApps is a powerful Low-Code Platform which allows you to connect to hundreds of data sources – SharePoint is one of them and a very popular choice in enterprises. A very common thing to implement is a list of items respectively records of a data source – in this example a SharePoint list. This works by navigating to Insert → Gallery → Vertical and adding the SharePoint list as data source as shown in the official documentation.

Having inserted the gallery including the connected SharePoint list, let’s try to filter the list by a date. For our example, we will use the following elements in order to create an exmaple:

  • Gallery with the SharePoint list items → Gallery
  • SharePoint list → SharePointList
  • Selected date from date picker in PowerApps used for filtering → DatePicker.SelectedDate
  • SharePoint list date (field to filter at SharePoint side) → SharePointListDate

Using the Filter() function, we can create the following formula and insert it on the Gallery.Items property:

Filter(SharePointList, SharePointListDate = DatePicker.SelectedDate)

However, this formula which works in almost every other case in PowerApps doen’t work when filtering a SharePoint List by date! It doesn’t work to directly pass a date into the filter formula. Instead, we have to pass the day, month and year separately into the formula.

It works like this by adding the following formula to the Gallery.Items property:

Filter(SharePointList, SharePointListDate <= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)))

Please keep in mind that the above displayed formula only works for dates which are equal or larger than SharePointListDate. In order to create the „ultimate filtering formula“ for filtering for one exact date, we have to create a second filtering criteria:

Filter(SharePointList, SharePointListDate <= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)), SharePointListDate >= Date(Year(DatePicker.SelectedDate), Month(DatePicker.SelectedDate), Day(DatePicker.SelectedDate)))

As you can see, this isn’t a very professional way of filtering a SharePoint list by date. However, as far as I am aware, there isn’t any other possibility a this point of time and the presented workaround is the only way to go.

6 Antworten zu „PowerApps: Filtering a SharePoint List By Date“

  1. Avatar von Joel

    You absolute legend!

    1. Avatar von Lennart Wörmer

      Thanks. Happy I could help you!

  2. Avatar von april

    Hi there
    I’m trying to filter a list to show only items where the date in column Start Date = Today

    Any ideas/

    1. Avatar von Lennart Wörmer

      Hi, you can compare the SharePoint date field with Today() in Canvas Apps. You can start with Filter(SharePointList, StartDate = Today()) function (you may have to adjust it to your needs). Take care that if the date field includes a time you also have to adjust the filter to only filter the date but not the time.

  3. Avatar von Tom

    Didn’t include sorting. Sometimes you want the latest date value from the list item.

    I figured it out though:

    AddColumns(‚Validations List‘,“ValDate“,’Validation Date‘),Title=txtWorkID.Text
    ).’Validation Date‘

    If you have a text box called txtWorkID, you can fill the box with an ID and if it matches an ID in the Title field (which you can safely rename „WorkID“, but don’t have to), it will bring back all dates for that ID, sorted in descending (latest, first) order.

    1. Avatar von Lennart Wörmer

      Thanks for your feedback, Tom! Glad you found a way to figure it out!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert