Answered

Dropdownlist Filter Not Displaying Correct Result

(0) Share

Report

Posted on by ruihaolrh

473

I have a dropdownlist where it only displays available 'Desks', by checking for existing 'Reservation Details' associated to the 'Desks'. Below is my formula for dropdownlist.items:

Filter(
 Filter(
 'Bookable Desk',
 Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)'
 ) As AllDesks,
 !(AllDesks.Name in ForAll(
 Filter(
 'Reservation Details',
 Date >= FromDate_Dp.SelectedDate,
 Date <= ToDate_Dp.SelectedDate
 ),
 'Reserved Desk'.Name
 ).Value)
)

There is an existing 'Reservation Details' on 1-Aug-2024.

However, when I select FromDate = 6-Feb-2024, and ToDate = 7-Dec-2024, my dropdownlist still displays that Desk.

May I know is there something incorrect in my filter formula? Thank you.
____________

Edit: I have broken down into 2 filters.

1) All Desks

Filter(
 'Bookable Desk',
 Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)'
)

2) All Reservation Details

Filter(
 'Reservation Details',
 Date >= FromDate_Dp.SelectedDate,
 Date <= ToDate_Dp.SelectedDate,
 'Reserved Desk'.Name = "Desk-07"
)

The result I want is to only display Desks that 'All Desks'.'Name' is not found in 'All Reservation Details'.'Reserved Desk'.'Name' between the selected FromDate and ToDate

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @gymcode ,

Please try below formula:

With( {wDesks: Filter( 'Reservation Details', Date >= FromDate_Dp.SelectedDate, Date <= ToDate_Dp.SelectedDate ). 'Reserved Desk'.Name}, Filter( 'Bookable Desk', Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)', !(Name in wDesks) ) )

Best regards,
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!
I have broken down into 2 filters.

1) All Desks

Filter( 'Bookable Desk', Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)' )

2) All Reservation Details

Filter( 'Reservation Details', Date >= FromDate_Dp.SelectedDate, Date <= ToDate_Dp.SelectedDate, 'Reserved Desk'.Name = "Desk-07" )

The result I want is to only display Desks that 'All Desks'.'Name' is not found in 'All Reservation Details'.'Reserved Desk'.'Name'
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni , thank you for your reply. I tried to input your formula but encountered the following errors:
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @v-jefferni , below is a screenshot of the original formula without errors, just that the result is incorrect.

*an observation: on some date ranges, the filter is working, but when the date range is over a longer period, there is this bug that the filter did not hide the Desk record...

I also tried changing the Date filter to '&&' condition but it didn't work too.

Date >= FromDate_Dp.SelectedDate && Date <= ToDate_Dp.SelectedDate

FromDate and ToDate also checked.
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @gymcode ,

I think the issue may be due to delegation. The Not ! and in operators are non-delegable. If you increase the data row limit to 2000, will the result change? How many desks are there in total in the Bookable Desk table?

I have modified the formula so you can have a try with it first:

With( {wDesks: Filter( 'Reservation Details', Date >= FromDate_Dp.SelectedDate, Date <= ToDate_Dp.SelectedDate, !IsBlank('Reserved Desk') ). 'Reserved Desk'}, Filter( 'Bookable Desk', Area.'Area (hsl_areaid)' = Area_Ddl.Selected.'Area (hsl_areaid)', !(Name in wDesks.Name) ) )

Best regards,
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni , thank you for the formula. I tried it but it says `Name is not recognized'

I have 15 records of Bookable Desks, but 'Reservation Details' will have much more than that.

I changed the record limit to 2000, and my original filter seems to be working now!!!!

May I know what are the recommendations to set max row limit for long term sustainability if records are increased day after day?
ruihaolrh 473 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni , allow me to rephrase my query.

Is there a way for me to modify my formula so it allows delegation?

I'm concern that there will be missing data in the long run with the increased data rows (specifically for 'reservation details').

Edit:
For a context, I have approximately 2500 Reservation Details right now.
v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @gymcode ,

If filtered reservation details records won't exceed 2,000 records in any given time period, that's fine, don't worry.

Best regards,