Help please - Can't see all rows in a list

(0) Share

Report

Posted on by TBut

Hi all

I've inherited an app from a colleague who's left the organisation and I'm a Power apps newbie.

We've got a gallery that pulls from a Microsoft List (an asset inventory). There are over 2000 rows. I've tried pulling straight from the list and using a collection, but I can't get it to show me all the rows. If I try searching for the Title (our unique number) some of them don't appear. I'm not sure if this is because of the 2000 row limit or, as I've seen in other posts, something wrong with the code on the gallery. I know this isn't pretty, but it kind of works. The .value entries are for our drop down lists in the List:

SortByColumns
(Filter
(Assets,
StartsWith(Title, TextSearchBox1_1.Text)
Or StartsWith('Serial Number', TextSearchBox1_1.Text)
Or StartsWith(School.Value, TextSearchBox1_1.Text)
Or StartsWith('Device Name', TextSearchBox1_1.Text)
Or StartsWith(Make.Value, TextSearchBox1_1.Text)
Or StartsWith('Device Type'.Value, TextSearchBox1_1.Text)
Or StartsWith(Model, TextSearchBox1_1.Text)
Or StartsWith('Assigned To', TextSearchBox1_1.Text)

), "Title", If(SortDescending1, Descending, Ascending)
)

If anyone can point me in any direction to sort (better code or just telling me straight it can't be done) I'd appreciate it. I'd ideally like the search box to act as a "search all" box rather than just a specific column. I've tried a combo box but failed abysmally.

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (18)

Answers (0)

Nogueira1306 7,390 Super User 2024 Season 1 on at

Like (0)

Report
Collections and Galleries also have a limit of rows. In gallery it only shows 500 unless you filter.

Try this filter:

SortByColumns (Filter (Assets, StartsWith(Title, TextSearchBox1_1.Text), StartsWith('Serial Number', TextSearchBox1_1.Text), StartsWith(School.Value, TextSearchBox1_1.Text), StartsWith('Device Name', TextSearchBox1_1.Text), StartsWith(Make.Value, TextSearchBox1_1.Text), StartsWith('Device Type'.Value, TextSearchBox1_1.Text), StartsWith(Model, TextSearchBox1_1.Text), StartsWith('Assigned To', TextSearchBox1_1.Text) ), "Title", If(SortDescending1, Descending, Ascending) )

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,

Gonçalo Nogueira

Check my LinkedIn!

My website!

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report
Hi @TBut ,

As can be found in this topic, the combination of complex columns and StartsWith not delegable, hence you are running into delegation issues:

https://powerusers.microsoft.com/t5/Building-Power-Apps/StartsWith-Function-on-SharePoint-List-Choice-Type/td-p/1317935

Since hardcoding doesn't look like something practical for you, I suggest to try introducing two dropdowns to allow for the selection of the Make and 'Device Type':

SortByColumns( Filter( Assets, StartsWith(Title, TextSearchBox1_1.Text) Or StartsWith('Serial Number', TextSearchBox1_1.Text) Or StartsWith(School.Value, TextSearchBox1_1.Text) Or StartsWith('Device Name', TextSearchBox1_1.Text) Or IsBlank(DropdownMake.Selected.Title) Or Make.Value = DropdownMake.Selected.Title Or IsBlank(DropDownDeviceType.Selected.Title) Or 'Device Type'.Value = DropDownDeviceType.Selected.Title Or StartsWith(Model, TextSearchBox1_1.Text) Or StartsWith('Assigned To', TextSearchBox1_1.Text) ), "Title", If(SortDescending1, Descending, Ascending) )

Was this reply helpful? Yes No
TBut 18 on at

Like (0)

Report

Thanks for the reply. When I use your code the search shows no results - the gallery is just blank.

Was this reply helpful? Yes No
TBut 18 on at

Like (0)

Report

Thank you for your reply. I'm happy to add the drop downs. I take it I'm calling them DropdownMake and DropdownDeviceType?

I can't seem to get the columns to show in the drop downs though? If I select the collection then try to choose the column it's not in the list?

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report
Hi @TBut,

Using a collection will also make you run into the Data Row Limit, so it's better to reference the data source directly.

You can call the dropdowns whatever you like, but I gave them these names to make it clear 🙂

Assuming the Make and 'Device Type' columns are choice types, you can use the below code for their items properties:

DropDownMake.Items = Choices([@DataSource].Make DropDownDeviceType.Items = Choices([@DataSource].'Device type'

Was this reply helpful? Yes No
TBut 18 on at

Like (0)

Report

Thanks again for the reply, it's much appreciated.

I'm getting an error on the code above for the drop downs. Says "The function 'Choices' has some invalid arguments". Looks like it's wanting something after the .make at the end? Searchtext?

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report

Hi @TBut ,

I assume you entered the name of your collection as the data source, but that won't work. Please enter the original data source identifier.

Was this reply helpful? Yes No
TBut 18 on at

Like (0)

Report

Hi again,

It was the original data source as you mentioned it wouldn't work well with a collection.

The above error is what I get for the 'Device Type' dropdown and I get a different error for the Make dropdown:

"Incompatible types for comparison. These types can't be compared: Error, Table"

Was this reply helpful? Yes No
BCBuizer 22,505 Super User 2025 Season 2 on at

Like (0)

Report

Hi @TBut ,

Can you please confirm the data type of these two columns and share a screenshot of your code and the errors?

Was this reply helpful? Yes No
TBut 18 on at

Like (0)

Report

Hi @BCBuizer

Sorry for the delay, I haven't touched this for a while.

I've attached pictures. The black blobs are just hiding the company name. Everything is referencing the Sharepoint list. I'd ideally like a couple more dropdowns and for the results to filter as more drop downs are picked.

The data types for those columns are picked from a list

Thanks for your help

DrepDownMakeItems...

GalleryItems.png

DropDownDeviceTyp...

Was this reply helpful? Yes No