SharePoint Date Delegation in Power Apps

Currently filtering dates in SharePoint produces delegation warnings and on large data sets you will not retrieve all the records you would expect. This post will show you how to workaround SharePoint date delegation in Power Apps. Continue reading and you too will become an Office Power User!

SharePoint-Date-Delegation-in-Power-Apps

Setting up SharePoint

The main part of this work around is to create an additional column in your SharePoint list to store the date value as a number. It is this column that you will use in Power Apps to filter your data on.

  • New Column
    • Name: DTvalue
    • Type: Number
    • Description: This is used for date delegation.
Workarond-SharePoint-Date-Delegation-in-Power-Apps-SP-Field

Preparing The Column in Power Apps

To recreate this demo In Power Apps we will include two date picker controls a gallery control and an icon to sort. The gallery control is bound to a SharePoint list named ” lstDateDelegationDemo” consisting of 3 columns, “Title”, “TransactionDT” and “DTvalue”.

  • Date Picker
    • Name: DatePickerStart
    • X: 40
    • Y: 190
    • Width: 236
    • Height: 54
  • Date Picker
    • Name: DatePickerEnd
    • X: 40
    • Y: 304
    • Width: 236
    • Height: 54
  • Icon
    • Icon: Icon.Sort
    • X: 927
    • Y: 62
    • Width: 42
    • Height: 42
    • OnSelect:
      If(
      varSort=”Asc”,
      UpdateContext({varSort:”Desc”}),
      UpdateContext({varSort: “Asc”})
      )

Finally we will now insert the gallery and set up the items property to filter the “DTvalue” column.

  • Vertical Gallery
    • X: 360
    • Y: 118
    • Width: 636
    • Height: 610
    • Items:
      If(
      varSort = “Asc”,
      SortByColumns(
      Filter(
      lstDateDelegationDemo,
      DTvalue >= Value(
      Year(DatePickerStart.SelectedDate) & Text(
      Month(DatePickerStart.SelectedDate),
      “00”
      ) & Text(
      Day(DatePickerStart.SelectedDate),
      “00”
      )
      ) && DTvalue <= Value(
      Year(DatePickerEnd.SelectedDate) & Text(
      Month(DatePickerEnd.SelectedDate),
      “00”
      ) & Text(
      Day(DatePickerEnd.SelectedDate),
      “00”
      )
      )
      ),
      “TransactionDT”
      Ascending
      ),
      SortByColumns(
      Filter(
      lstDateDelegationDemo,
      DTvalue >= Value(
      Year(DatePickerStart.SelectedDate) & Text(
      Month(DatePickerStart.SelectedDate),
      “00”
      ) & Text(
      Day(DatePickerStart.SelectedDate)
      “00”
      )
      ) && DTvalue <= Value(
      Year(DatePickerEnd.SelectedDate) & Text(
      Month(DatePickerEnd.SelectedDate),
      “00”
      ) & Text(
      Day(DatePickerEnd.SelectedDate),
      “00”
      )
      )
      ),
      “TransactionDT”,
      Descending
      )
      )
  • Label //Insert inside your gallery control.
    • Text: ThisItem.Title
    • X: 28
    • Y: 14
    • Width: 132
    • Height: 36
  • Label //Insert inside your gallery control.
    • Text: ThisItem.TransactionDT
    • X: 172
    • Y: 36
    • Width: 172
    • Height: 36
  • Label //Insert inside your gallery control.
    • Text: ThisItem.DTvalue
    • X: 376
    • Y: 16
    • Width: 176
    • Height: 34

Keeping the Date Value Column in Sync.

To keep the “DTvalue’ in sync with the Transaction date you have to always update this column as the transaction date changes.

  • DT Value Column
    Value(
    Year(TransactionDT) & Text(Month(TransactionDT),”00″)
    & Text(Day(TransactionDT),”00″)
    )

Now you know how to workaround SharePoint date delegation in Power Apps! Please leave your feedback. I hope this article has been helpful for you so bookmark this blog as new articles are posted regularly.

Additional Resources

Do It Form Me!

Share the knowledge!

Paul Rodrigues

Business Analyst with 20 years of IT experience creating practical solutions. I love to automate business processes through the use of technology while making the end users work easier. My current favorite tools are Power Apps, Power BI and Power Automate. #PowerAddict

You may also like...

1 Response

  1. November 30, 2019

    […] SharePoint Date Delegation in Power AppsDefining Default Values for Complex SharePoint Columns […]

Leave a Reply

Your email address will not be published. Required fields are marked *