cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kdunk
Frequent Visitor

Filter Gallery for Last 5 Days

Currently my time tracker app is displaying all items from a SharePoint.  The app is running very slow and I don't know if this is the part of the problem.  I'd like to limit the gallery to showing the last five days of entries (including today's entries).  Can someone help me modify my existing formula: 

 

SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text)), "StartofShift", If(SortAscending1, Ascending, Descending))

 

The gallery is currently sorted by start time in descending order, and the gallery can be searched by employee name.   It also displays the end time but is not needed for any filtering. I don't need all records available for browsing.

 

kdunk_0-1594210448757.png

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
KrishnaV
Super User III
Super User III

Hi @kdunk ,

 

I agree with @eka24 but a small change since you want last 5 days:

SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
or
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

View solution in original post

Hi @kdunk ,

 

try this:

SortByColumns(Filter(LastN([@'Time Tracker 3'],20), StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
or
SortByColumns(Filter(LastN([@'Time Tracker 3'],20), StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

View solution in original post

8 REPLIES 8
eka24
Super User III
Super User III

Try:
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
 
Or
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
 
Or
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateValue(DateColumn),Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
 
Or
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateValue(DateColumn),Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
KrishnaV
Super User III
Super User III

Hi @kdunk ,

 

I agree with @eka24 but a small change since you want last 5 days:

SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
or
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

View solution in original post

kdunk
Frequent Visitor

Thanks @KrishnaV,

 

Your solution worked.  What if, instead of filtering for the last 5 days, I wanted to filter for the last 20 records - what would that look like?  Also, adding the date filter reintroduces a delegation issue.  Any way around that?

hi @kdunk ,

 

Try this for 20 days:

SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateColumn,Today(),Days)<=20
), "StartofShift", If(SortAscending1, Ascending, Descending))
or
SortByColumns(Filter([@'Time Tracker 3'], StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateColumn,Today(),Days)<=20
), "StartofShift", If(SortAscending1, Ascending, Descending))

In SharePoint Date is not a delegable control :(, please see the below article with all the functions which are delegable:
https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
kdunk
Frequent Visitor

Thanks again @KrishnaV, but I wanted to try filtering for the last 20 records rather than the last 20 days.  Any way to do that?

 

 

Hi @kdunk ,

 

try this:

SortByColumns(Filter(LastN([@'Time Tracker 3'],20), StartsWith(EmployeeSearch, TextSearchBox1.Text),
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))
or
SortByColumns(Filter(LastN([@'Time Tracker 3'],20), StartsWith(EmployeeSearch, TextSearchBox1.Text)&&
DateDiff(DateColumn,Today(),Days)<=5
), "StartofShift", If(SortAscending1, Ascending, Descending))


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

View solution in original post

kdunk
Frequent Visitor

This seems to have done the trick and now the delegation warning is gone (not sure why).  Need to test it out a little bit more, but I'll likely stick with the 5 day filter only.

 

Thanks @KrishnaV for your help.

Hi @kdunk ,

 

I am happy that it worked for you, would you mind to mark the relevant reply (which helped you) as the solution so that the new members of the community will make use of it.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (71,044)