PowerApps Filter Dropdown A if Value Found in Dropdown B

[gymcode]

I have 2 Dropdown controls, AvailableSeats and BookedSeats, both containing one Text value of SeatName.

I want to hide rows in AvailableSeats that are found in BookedSeats.

How can this be implemented? Thank you in advance.

[v-yueyun-msft]

HI , @gymcode 

This is my test data created by the Button-OnSelect:

Set(avaData,[{SeatName:"s1"},{SeatName:"s2"},{SeatName:"s3"},{SeatName:"s4"},{SeatName:"s5"}]);
Set(Bookdata,[{SeatName:"s2"},{SeatName:"s4"},{SeatName:"s6"},{SeatName:"s7"},{SeatName:"s9"}]);

Items:

Filter(avaData,Not(SeatName in Bookdata.SeatName))

Filter(Your Raw data,Not(ColumnName in BookedSeats COmbo box Items.ColumnName))

If this reply can help you , you can click mark this reply as solution, thanks in advance!

Best Regards,

Yueyun Zhang

[gymcode]

Hi @v-yueyun-msft , thank you for your example.

However, Apologies, as I am new to Canvas Apps, I was unable to fully relate your examples to my fields.

Referencing to your formula, I formatted my formula to the following:

Filter(AllDesks_FromDate_ToDate_Ddl,Not(AllDeskName in ReservedDesks_FromDate_ToDate_Ddl.'ReservedDeskName')

Below are 3 Dropdown lists that I have, and I seek your advice on the error I faced in my formula:

1) All Desks Dropdown List: AllDesks_FromDate_ToDate_Ddl
Formula:
Filter('Bookable Desk', Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)' && Facility.Facility = Building_Ddl.Selected.Facility)

2) Reserved Desks Dropdown List: AllDesks_FromDate_ToDate_Ddl
Formula:

ForAll(Filter(Reservations, 'Reservations (Views)'.'Active Reservations', Date >= FromDate_Dp.Value, Date <= ToDate_Dp.Value && ('Status Reason' = 'Status Reason (Reservations)'.Blocked || 'Status Reason' = 'Status Reason (Reservations)'.Booked)),'Reserved Desk'.Name)

3) Available Desks Dropdown List : AvailableDesks_FromDate_ToDate_Ddl
Formula: (to be calculated)

Thank you, I appreciate it.

[v-yueyun-msft]

Hi , @gymcode 

Thanks for your quick response! Dose the above two code is your dropdown-items?

Can you try to use this code in your third dropdown -items:

Filter( Filter('Bookable Desk', Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)' && Facility.Facility = Building_Ddl.Selected.Facility) As AllDesks
,Not(AllDesks.AllDeskName in 
ForAll(Filter(Reservations, 'Reservations (Views)'.'Active Reservations', Date >= FromDate_Dp.Value, Date <= ToDate_Dp.Value && ('Status Reason' = 'Status Reason (Reservations)'.Blocked || 'Status Reason' = 'Status Reason (Reservations)'.Booked)),'Reserved Desk'.Name).'ReservedDeskName'
)

Best Regards,

Yueyun Zhang

[gymcode]

Hi @v-yueyun-msft , I appreciate your prompt assistance too.

I tried your suggested formula for the 3rd dropdown box, but there were issues with the specific field names, as follow:

Below are the details of the fields for each dropdown list, and I hope it helps.

1) AllDesks_FromDate_ToDate_Ddl -> AllDeskName
   a) FieldDisplayName: "Name"
   b) FieldName: "hsl_name"

2) ReservedDesks_FromDate_ToDate_Ddl -> ReservedDeskName
   a) FieldDisplayName: "Value"
   b) FieldName: "Value"

3) AvailableDesks_FromDate_ToDate_Ddl-> AvailableDeskName
   a) FieldDisplayName: "Name"
   b) FieldName: "hsl_name"

[v-yueyun-msft · Accepted solution]

Hi , @gymcode 

Can you try to use this code :

Filter( Filter('Bookable Desk', Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)' && Facility.Facility = Building_Ddl.Selected.Facility) As AllDesks
,Not(AllDesks.hsl_name in 
ForAll(Filter(Reservations, 'Reservations (Views)'.'Active Reservations', Date >= FromDate_Dp.Value, Date <= ToDate_Dp.Value && ('Status Reason' = 'Status Reason (Reservations)'.Blocked || 'Status Reason' = 'Status Reason (Reservations)'.Booked)),'Reserved Desk'.Name).Value
)

And you can see the column Name when your mouse hover on the dropdown-items function, like this:

FIlter( 'Your All Desks Table'   As  AllDesks , AllDesks.'ColumnName in your All Desks Table'  in 'Your ReservedDeskName Table'.'ColumnName in your ReservedDeskName Table' )

Best Regards,

Yueyun Zhang

[gymcode]

Hi @v-yueyun-msft , thank you for your prompt reply.

Firstly, I input your suggested formula, and there is no results found.
- AvailableDesks_FromDate_ToDate_Ddl

Secondly, thank you for the tip on viewing columns data for each formula. Below are the columns for All and Booked Desks dropdown lists:

1) AllDesks_FromDate_ToDate_Ddl

2) ReservedDesks_FromDate_ToDate_Ddl

[gymcode]

@v-yueyun-msft I mouse-hovered above the following sections of the formula, and the data seems to exist.

AllDesks contains multiple rows.
ForAll ReservedDesk contains one row.

I am trying to identify why "Not-In" condition is not displaying results...

[gymcode]

@v-yueyun-msft I have sorted it out, replacing "NOT" with "!" worked!

Thank you so much for your help. Below is my final formula for reference:

Filter(
    Filter(
        'Bookable Desk',
        Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)' && Facility.Facility = Building_Ddl.Selected.Facility
    ) As AllDesks,
    !(
        AllDesks.hsl_name in ForAll(
            Filter(
                Reservations,
                'Reservations (Views)'.'Active Reservations',
                Date >= FromDate_Dp.Value,
                Date <= ToDate_Dp.Value && ('Status Reason' = 'Status Reason (Reservations)'.Blocked || 'Status Reason' = 'Status Reason (Reservations)'.Booked)
            ),
            'Reserved Desk'.Name
        ).Value
    )
)