Answered

SharePoint Delegation and Filters with DateTime Columns

(0) Share

Report

Posted on by CU22051816-0

Hello Y'all,

Newbie here. I'm trying to use a formula to filter by the Date/Time column I have called 'Status'. I got it to work but not without delegation errors of course. Is there a way to make this formula without StartsWith? At least that is what I think is causing the error. Formula in Items for the Gallery;

SortByColumns(Filter([@'Paid Time Off (PTO)'], StartsWith(Start,TextSearchBox1.Text)), "Start", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))
 
Also, would it be possible to add on a filter using the datetime columns with datepicker to filter a range of items between those dates? I've searched YouTube and the web with no luck on this. At least not without Delegation or Boolean errors so nothing to work off of there I can provide besides the date picker names:
 
dpStartDate
dpEndDate
 
Any enlightenment would be greatly appreciated!

Categories:

GCC, GCCH, DoD - Federal App Makers (FAM)

I have the same question (0)

All responses (6)

Answers (1)

WarrenBelz 153,079 Most Valuable Professional on at

Like (1)

Report
Hi @CU22051816-0,'

Something like this should do it - note that the top filter (between the dates) needs to return record numbers (the list can be of any size) less than your Data Row Limit for full results to be returned.
With( { _Data: Filter( [@'Paid Time Off (PTO)'], DateFieldName >= StartDatePicker.SelectedDate && DateFieldName <= EndDatePicker.SelectedDate ) }, SortByColumns( Search( _Data, TextSearchBox1.Text, Start ), "Start", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
CU22051816-0 9 on at

Like (0)

Report

Good morning @WarrenBelz

Hope your coffee is strong this morning and problems are few!

I am getting an invalid argument error at search. It is excepting the column "Start" to be a text type.

With(

{

_Data:

Filter(

[@'Paid Time Off (PTO)'],

Start >= dpStartDate.SelectedDate &&

End <= dpEndDate.SelectedDate

)

},

SortByColumns(

Search(

_Data,

TextSearchBox1.Text,

Start

),

"Start",

If(

SortDescending1,

SortOrder.Descending,

SortOrder.Ascending

)

)

)

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report

Hi @CU22051816-0,

I was expecting it to be of Text type as well as you used StartsWith() in your post. What type is it and how exactly are you expecting it to filter ?

Was this reply helpful? Yes No
CU22051816-0 9 on at

Like (0)

Report

Hello @WarrenBelz,

It is a Date and Time Column in SharePoint. End state would be for it to filter by the current user (Office365 Users Connected) with a checkbox to where they can see all items. If they know the start date of their request they could search by the date or search between a range. I dont know if that is the best way but that was the vision.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
Hi @CU22051816-0,

Sorry, still a bit lost here - if Start is a Date/Time column how exactly are you wanting to search on it other than between the two date pickers ? Are you wanting to search on another field or if you wanted to search on a particular date format
With( { _Data: Filter( [@'Paid Time Off (PTO)'], DateFieldName >= StartDatePicker.SelectedDate && DateFieldName <= EndDatePicker.SelectedDate ) }, SortByColumns( Search( AddColumns( _Data, DateText, Text(Start, "ddd d mmm yyyy") ), TextSearchBox1.Text, DateText ), "Start", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
CU22051816-0 9 on at

Like (0)

Report

Hi @WarrenBelz,

That is exactly how I was wanting to do it, search by a certain date format. That did it! I just had to "reconnect" the data source for the items to show when selected for some reason.

Thanks for your help!!

Was this reply helpful? Yes No