Filtering SharePoint list based on value in different SharePoint list

[Anonymous]

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!

[v-yutliu-msft]

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, 

[Anonymous]

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:

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

From list here:

[v-yutliu-msft]

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,

[Anonymous]

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)

2) Yes

For some reason this formula isnt working 😞

[v-yutliu-msft]

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, 

[Anonymous]

@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?

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]

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.

[v-yutliu-msft · Accepted solution]

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,

[Anonymous]

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?