Answered

Combo box to query large list 5000+ items

(0) Share

Report

Posted on by Verbiage211

I'm attempting to use a combo box to query a large collection.

The list has 15k+ items currently and what I'm trying to do is have the user select an item from a combo box and basically do a where clause to query the list and load it into a gallery.

This is the OnChange Event for the combo box as well as the datepicker

ClearCollect(laborAllocation, Filter('Labor Allocation',
Date >= (DatePicker1.SelectedDate-Weekday(DatePicker1.SelectedDate,2) + 1) And
Date <= (DatePicker1.SelectedDate-Weekday(DatePicker1.SelectedDate,2) + 7) And
Project.Id = ProjectCombobox.Selected.ID));

the gallery is set to laborAllocation

The results should generally a couple dozen to a hundred entries.

It doesn't currently work consistently or accurately.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@Verbiage211

If you are getting unpredictable results, likely you have a delegation issue. ClearCollect() is not delegatable. Assuming you have maxed the row numbers to 2k in the Settings, If the number of projects between the dates is >2k then only the first 2k will be in the collection. To test, put a label and the screen and set the Text property to CountRows(laborAllocation). If it is 2k, then that is the problem. If so, and if you are using Dataverse, then you can create a view for the dates in question and use that in the your filters. If you are using SharePoint, then you will need a work around.

Was this reply helpful? Yes No
Verbiage211 10 on at

Like
a
(0)

Report
Copy link

Link copied!

The list is a little over 15k items, the result of items between dates with the ID should be about 1-200 records.

if it were a WHERE clause the results will consistently be under 500.

I do have the delegation warning at the moment.

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Verbiage211

Getting rid of the collection: for the items property of the combobox:

With({la: Filter('Labor Allocation', Date >= (DatePicker1.SelectedDate-Weekday(DatePicker1.SelectedDate,2) + 1) && Date <= (DatePicker1.SelectedDate-Weekday(DatePicker1.SelectedDate,2) + 7) )}, la )

For the Items property of the Gallery

With({la: Filter('Labor Allocation', Date >= (DatePicker1.SelectedDate-Weekday(DatePicker1.SelectedDate,2) + 1) && Date <= (DatePicker1.SelectedDate-Weekday(DatePicker1.SelectedDate,2) + 7) )}, Filter(la, Project.Id = ProjectCombobox.Selected.ID) )

Was this reply helpful? Yes No
Verbiage211 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you very much, I hadn't learned about With before.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@Verbiage211

It's very helpful in cutting down the verbiage in long and complicated formulas!

Was this reply helpful? Yes No
Verbiage211 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Well played my friend,

Was this reply helpful? Yes No