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!
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.
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.
