Filter SharePoint List based on Dates selected and Name selected

(1) Share

Report

Posted on by Dave-ITMan

Hi Experts,

I have a DateFrom and DateTo DatePicker fields, I then have a table (named Table2) to show the results of my sharepoint list based on the staff name entered in a textinput. I would like the table to filter based on the name entered in the textinput and also between the dates selected in DateFrom and DateTo, can you advise how to do this please?

Thanks in advance
Dave

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

level36 77 on at

Like (0)

Report

Hi,

You should be able to do this with the below formula. And additional arguments to the filter function will be treated as AND statements.

Filter(Table2, staff_name = textinput.Text, date >= DateFrom.Selected, date <= DateTo.Selected)

This should return the list filtered by name and between the two selected dates.

Hope this helps!

Was this reply helpful? Yes No
Dave-ITMan 24 on at

Like (0)

Report
Hi @level36

Thanks for the reply, I had to doctor it slightly to the below but the issue I have is that I just did a sign in (today being 25/07/2024) but it's showing me the result if I set the date range from yesterday to yesterday (yesterday being 24/07/2024) so the list should be empty as I never signed in. Do you know what needs changing please?

Filter(VisitorSystem, StaffName = Title1_3.Text && DateFrom.SelectedDate <= DateFrom.SelectedDate, DateTo.SelectedDate >= DateTo.SelectedDate)

Regards

Dave

Was this reply helpful? Yes No
WiZey 3,023 Moderator on at

Like (1)

Report

Hello,

It's showing wrong results because you changed @level36 formula. He proposed a valid solution, so I don't see why you would change it like you did.

Have you tried his formula? Does it work correctly? If not, could you elaborate more on the issue?

Was this reply helpful? Yes No
level36 77 on at

Like (0)

Report
Hi @Dave-ITMan,

Looking at your reply it seems that you are comparing the Date Selector to the Data Selector. You need to compare a value from your table to the selected date. With an example if you were using something like the created date in a SharePoint List.

Hope this helps!

Filter(VisitorSystem, StaffName = Title1_3.Text, created <= DateFrom.SelectedDate, created >= DateTo.SelectedDate);

Was this reply helpful? Yes No
Dave-ITMan 24 on at

Like (0)

Report
Hi @level36,

Thanks yeah I spotted that after i'd sent the message, it still doesn't show any results though and i'm wondering if it's because the table is showing dates in US format (mm/dd/yyyy) but my sharepoint list is showing the entries in UK format (dd/mm/yyyy). I took off all the date filtering to prove that it's filtering by the name entered in Title1_3.Text and it is and that's when I noticed that the date format is showing as US. Is that the likely cause and if so, is it the DateFrom and DateTo date pickers that I need to change something?

Below is the code i've got currently

Filter(VisitorSystem, StaffName = Title1_3.Text, StaffSignIn <= DateFrom.SelectedDate, StaffSignOut >= DateTo.SelectedDate)

Thanks

Dave

Was this reply helpful? Yes No
Suggested answer

level36 77 on at

Like (0)

Report
Hi @Dave-ITMan,

Format shouldn't play into it, PowerApps works with all datatimes as UTC, then converts to local time zone. I think this may be a logic issue, currently with your filter you are saying the following rules must be true for a record to show.

The StaffName must equal Title1_3.Text

The StaffSigneIn Date must be before the DateFrom.Selected Date

The StaffSignOut must be after the DateTo.Selected Date.

If people are always signing in before signing out, and "From" date is always before the "To" date your logic needs to look more like this

The StaffName must equal Title1_3.Text

The StaffSigneIn Date must be after the DateFrom.Selected Date

The StaffSignOut must be before the DateTo.Selected Date.

Try the following formula and see if it works better.

Filter(VisitorSystem, StaffName = Title1_3.Text, StaffSignIn >= DateFrom.SelectedDate, StaffSignOut <= DateTo.SelectedDate)

Was this reply helpful? Yes No
Dave-ITMan 24 on at

Like (0)

Report

Hi @level36,

Appreciate the detailed response and you are correct that I had it the wrong way around. I've changed it as you've suggested but still not getting any results, perhaps the screenshots below will help?

Here is a screenshot of my sharepoint list to show the entry is in there and the date

Here is a screenshot of my PowerApp to show the search with zero results

Here is a screenshot of my PowerApp with the below removed from the Items property of the table so it's only filtering on the name selected and not the date.

, StaffSignIn >= DateFrom_1.SelectedDate, StaffSignOut <= DateTo_1.SelectedDate

Hopefully that makes it easier to see where the issue may be.

Regards

Dave

Was this reply helpful? Yes No
Suggested answer

level36 77 on at

Like (0)

Report

Hi Dave,

Sorry it took me a few days to get back to you, been out of office. Your screenshots are very helpful, I think the issue you may be having is that your StaffSignOut date is empty. I've re-created your system and added a feature that may be helpful.

The Filter String I'm using is

Filter(

StaffList, If(Len(NameInput.Value) > 0, StaffName = NameInput.Value, true), //Filters the list to the entered name, will only filter on date if no name is provided.

StaffSignIn >= DateFrom.SelectedDate, //Get all records where the sign in date is after the FromDate

StaffSignOut <= DateTo.SelectedDate || StaffSignOut = Blank() //Get all records where the sign out date is before the to date or the sign out date is blank.

)

I've attached some screenshots of how this works.

With all filters set the table returns:

With the Date From filter set after the sign in date

Without the Name box filled

With the Date to filter set before the sign out date.

Hope this helps! Please reply if you have any additional questions.

Was this reply helpful? Yes No