top of page

Power Apps Dates & Delegation

If you’ve ever needed to filter data with dates, you may have run into a few delegation issues depending on the data source you’re using.


For example, filtering dates by a specific date, month, or year is not delegable in Sharepoint and SQL Server. See the Microsoft delegation doc for more details: Understand Delegation in Power Apps


Let’s say you have a column for “Created Date” and you want to be able to filter by data, month, year, etc. You can create three columns for month, day, and year to be able to filter without any delegation issues.


Step 1 - Create three number columns for:


•Created Month

•Created Day

•Created Year


Step 2 – Form Submission / New Record


Set the default property values in your form for the date columns to the follow:


Created Month = Value(Month(Today()))

Created Day = Value(Day(Today()))

Created Year = Value(Year(Today()))


If this form is used for both new and editing records, then you can modify the default property to:


If(Form1.Mode = FormMode.New, Value(Month(Today())),Parent.Default)


Step 3 - Filter Data


Now you can filter your data in various ways without worrying about delegation issues.


Example 1 - Filtering Gallery by Date Picker Selection


Set your Gallery's Item property to:


Filter(YourDataSource,And(Value(CreatedMonth) = Value(Month(DatePicker1.SelectedDate)), Value(CreatedDay) = Value(Day(DatePicker1.SelectedDate)), Value(CreatedYear) = Value(Year(DatePicker1.SelectedDate))))



Example 2 - Filtering Gallery by Months


We can filter our gallery by date filters like Last Month, This Month, Next Month. To do so, I've added a collection and gallery for the filter.


Step 1 - Add a collection to your apps on start property with:


ClearCollect(colDateFilters,{Label:"All Dates",MonthYear: ""},{Label:"Last Month",MonthYear: Month(DateAdd(Today(),-1,Months)) & Year(DateAdd(Today(),-1,Months))},{Label:"This Month",MonthYear: Month(Today()) & Year(Today())},{Label:"Next Month",MonthYear: Month(DateAdd(Today(),1,Months)) & Year(DateAdd(Today(),1,Months))})


Step 2 - Add a gallery for the filter


Set the gallery's item property to your collection.


Step 3 - Update the item property of your gallery with all your data to:


Filter(YourDataSource,If(MonthFilterGallery.Selected.Label = "All Dates",CreatedMonth <> Blank(),CreatedMonth & CreatedYear = MonthFilterGallery.Selected.MonthYear))





647 views

Kommentare


bottom of page