cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering SharePoint list based on value in different SharePoint list

Hi!

 

Im trying to create a booking application where user has to select single day/date range for a booking, if date range is selected i'd like to display only items that are book-able in the given period based on SharePoint list as my dataset and so im wondering if its possible to exclude items with the same name in Gallery( based on sharepointlist1 ) that have given value (in this example date but i could change it to string or integer) in a different sharepointlist2 ?

 

If possible how would my formula look like? Any tips are welcome!

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous ,

Firstly, the problem should be about fieldnames. I did not know you use different field names in different lists, which is a key problem.

Secondly, the problem is not date format type. There's a known issue about Filter based on sharepoint date type . I forgot to mention that.

I believe if you use this formula will be ok too:

 

Filter(Hub_garage,Not(SlotNum in
                                Filter(HUB_UserBookings,
                  DatePicker1.SelectedDate = DateValue(Text(BookingDate))||
                  DatePicker2.SelectedDate = DateValue(Text(BookingDate))
                                       ).SlotNumber
                      )
)

 

 

Anyway, I'm very glad that you've solved your problem.

If you do not have any other problem, could you mark my answer as solution?

Thanks!

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-yutliu-msft
Community Support
Community Support

Hi @Anonymous ,

Do you firstly filter list1 based on date range and then exclude items with the same name in list2?

Both these two filter rules are based on date field. Am I right?

Is this date field date type?

I assume yes.

Then try this formula:

Filter(list1, date>=DatePicker1.SelectedDate,date<=DatePicker2.SelectedDate,date Not in list2.date)

//within date range and not in date field of list2

 

 

Best regards, 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,

 

What i want to do is to display items in list1 but to exclude items from list2 which have date assigned to them selected in datepicker1 and datepicker2 so something like:

 

 

 

Filter(Hub_garage,Not(DatePicker1.SelectedDate in HUB_UserBookings.BookingDate),Not(DatePicker2.SelectedDate in HUB_UserBookings.BookingDate))

 

 

 but it displays no items

 

I've also tried:

 

 

 

Filter(Hub_garage.SlotNum,Not(Date(Year(DatePicker1.SelectedDate),Month(DatePicker1.SelectedDate),Day(DatePicker1.SelectedDate)) in HUB_UserBookings.BookingDate),Not(Date(Year(DatePicker2.SelectedDate),Month(DatePicker2.SelectedDate),Day(DatePicker2.SelectedDate)) in HUB_UserBookings.BookingDate))

 

 

 

But unfortunately when i select the date in datepicker that is in HUB_UserBookings i get 0 items from Hub_Garage 😞

 

Also, for a little more context this is how my data is formatted:

 

I want to show items from this list:

SlotNum.PNG

And to display them in the gallery but when using date pickers to filter out "booked" items

DatePicker.PNG

 

From list here:

bookings.PNG

 

Hi @Anonymous ,

Do you want to filter SlotNum in list1 that are not in the filtered list2?

Could you tell me:

1)BookingDate field's data type(HUB_UserBookings list)?

2)does SlotNum field in list1 and list2 have the same value?

I assume that 1)date type 2)yes

If so, try this formula:

Filter(Hub_garage,SlotNum Not in(
                                Filter(HUB_UserBookings,DatePicker1.SelectedDate in BookingDate||
                                              DatePicker2.SelectedDate in BookingDate).SlotNum
                                 )
)

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yutliu-msft 

 

First of all, thanks for the fast reply!

 

I want to filter out SlotNum in list1 that have selected date in DatePicker in column BookingDate in list2

 

1)

bookingdate.PNG

2) Yes

 

For some reason this formula isnt working 😞

 

formula.PNG

 

 

Hi @Anonymous ,

Just need to make a little change:

Filter(Hub_garage,Not(SlotNum in
                                Filter(HUB_UserBookings,DatePicker1.SelectedDate in BookingDate||
                                              DatePicker2.SelectedDate in BookingDate).SlotNum
                    )
)

 

 

Best regards, 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yutliu-msft Almost there!

 

One more error in the code anymore & i dont get any hits, my gallery is blank 😞 might that be due to the date format difference between SharePoint & PowerApps?

 

formula.PNG

 

Also, column SlotNum in hub_garage is named SlotNumber in HUB_UserBookings ( as obviously i want to keep things simple when it comes to naming the columns 😛 )

Anonymous
Not applicable

I think i've found a solution and it seems that date formats were the issue, i've modified the code so it looks like:

 

Filter(Hub_garage,Not(SlotNum in
                                Filter(HUB_UserBookings,Label4.Text in SearchQuery||
                                              Label4.Text in SearchQuery).SlotNumber
                    )
)

 

Label4 is a string format of a date and search query is also a string format of a date.

 

Hi @Anonymous ,

Firstly, the problem should be about fieldnames. I did not know you use different field names in different lists, which is a key problem.

Secondly, the problem is not date format type. There's a known issue about Filter based on sharepoint date type . I forgot to mention that.

I believe if you use this formula will be ok too:

 

Filter(Hub_garage,Not(SlotNum in
                                Filter(HUB_UserBookings,
                  DatePicker1.SelectedDate = DateValue(Text(BookingDate))||
                  DatePicker2.SelectedDate = DateValue(Text(BookingDate))
                                       ).SlotNumber
                      )
)

 

 

Anyway, I'm very glad that you've solved your problem.

If you do not have any other problem, could you mark my answer as solution?

Thanks!

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks for your support! Although now i realized i need to be checking for the dates that are between DatePicker1 & DatePicker2 also so for example if DatePicker1 = 20.05.2020 & DatePicker2 = 26.05.2020 i also want to exclude items with same SlotNumber on that are reserved on 21.05,22.05,23.05,24.05,25.05 is that possible?

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,148)