Cannot make my Gallery Filter work on a Many to One relationship between 2 SharePoint Microsoft Lists with Datepicker as filter criteria - Help needed

[Fenvy]

Hello Power Apps Community,

I am stuck with my designed reservation powerapp. It can go in production if i can solve the problem that i face with an important Gallery Filter. I tried to modify it several times, but always fail with the expected result.

In this example: i want to select a free date to reservate an laptop between 21/02/2024 till 23/02/2024.

In the Gallery items are 4 free laptop records where i can choose from. but also record laptop ID 6, that already is reserved in the reservationlist 2 times under ID 134 and ID 135

These are reservations for the same Laptop (via a lookup column field to the other list "LoanList")

The problem is, whenever i change the start or enddate in the datepicker: does it still give record ID 134 as available reservation

Here it should give only the records (8, 12, 16, 18) that are still available and not reserved in the reservationList as results, since record Laptop ID 6 from the LoanList refers to an existing reservation in LaptopReservations List with ID 134 the same device as record 135, reservated on 2 different time ranges

the startdate 21/02/2024 is the Enddate of the reservationrecord 135 from the same laptop with lookup ID 6, so it shouldn't show the other record 134  as well.

An overview from the Parent List "LoanList"

Code structure in the Powerapp

The filter in this gallery.items is:

 With(
        {wReservations: LaptopReservations};
        Filter(
            LoanList;
            Purpose.Value = "Mobile Office";
            'Id (ID)' in Filter(
                wReservations;
                startDatePicker.SelectedDate > EndDate && EndDatePicker.SelectedDate > EndDate 
                || 
                EndDatePicker.SelectedDate < StartDate
                ||
                startDatePicker.SelectedDate > EndDate && startDatePicker.SelectedDate > StartDate
                ||
                startDatePicker.SelectedDate < StartDate && EndDatePicker.SelectedDate < StartDate
            ).NID  || Not('Id (ID)' in LaptopReservations.NID)
        )
    )

The column 'NID' is from type "Number", since i need it to filter on, somehow my filter fails completely when i try to filter on the lookup field.Id in LaptopReservations List.

With a Automated flow does the NID field gets populated with the laptop_ID lookup number after a item is created in this Microsoft List (this is not relevant information but i want to try to give all details that can help)

Can you help or give me advice in how i need to solve this filter problem?

Kind regards,

Fenvy

[renatopc84_ch]

Hi @Fenvy 

i had a similar issue, now I should put everything on a whiteboard to say you exactly the solution, anyway is in the Filter conditions. You use all “||”, so OR condition, as I understood from your use case, you should group some of the OR conditions under AND (“&&”) groups

[renatopc84_ch]

more specific I assume this should be the issue:

EndDatePicker.SelectedDate < StartDate

You have this condition as OR of all others

[Fenvy · Accepted solution]

Hello @renatopc84_ch ,

Thank you very much for your input about the use for "GroupBy", it started to let me rethink about how i created my filters.

With this i have searched about the syntax for Combining  Filter with GroupBy to filter on the data from a nested groupby table.

I found the solution in this post: Solved: Filtering a gallery if all items in a groupby equa... - Power Platform Community (microsoft....

And modified it slightly for my filter criteria.

My new Gallery Items is:

Filter(
    AddColumns(
        SortByColumns(
            GroupBy(
                LaptopReservations;
                "NID";
                "GroupByNID"
            );
            "NID";
            SortOrder.Ascending
        );
        "Count";
        !CountRows(GroupByNID);
        "CountForDateRange";
        CountIf(
            GroupByNID;
            startDatePicker_4.SelectedDate > StartDate && startDatePicker_4.SelectedDate > EndDate
            ||
            EndDatePicker_4.SelectedDate < StartDate && EndDatePicker_4.SelectedDate < EndDate
        )
    );
    (CountRows(GroupByNID) = CountIf(
        GroupByNID;
        startDatePicker_4.SelectedDate > StartDate && startDatePicker_4.SelectedDate > EndDate
        ||
        EndDatePicker_4.SelectedDate < StartDate && EndDatePicker_4.SelectedDate < EndDate
    ))
)

Results in: If the count from total record in the groupby table where i perform the filter on, is not the same as the count without filter on it,  (in other words "Count is true" then that specific record will not be shown.

If startdatepicker is on 21/02/2024, the gallery does not shown the groupby Record ID 6

Since 21/02/2024 is the Enddate from a reservation on this laptop ID 🙂

But this shows only the Reservations, and not the Loanlist from laptops, so i need to work this out to combine the correct filter groupby code with showing it as a part in a gallery from LoanList items.

But thank you again for the time you spent to help me out with this filtering issue.

Kind regards,

Fenvy