Searching/Filtering Using a Huge Excel Table

[Anonymous]

Hi,

This is a big ask and it is the hardest part of my app I am trying to develop.

I have an Excel Table that has around 5000 rows of data.

Each row is for a different piece of equipment, each piece of equipment has an Area it is found in, its Part Number, and its Description. I have attached some of the data table to a screenshot to show you.

I could create dependent dropdowns in my App based on the Area, however there is hundreds of pieces of equipment/rows in each area so these dropdowns would be way too long/big.

So I want the user to be able to search the Equipment Number in the app, and then when that equipment number is searched and selected, that Equipment's Area and Description will populate in the app.

Any help is much appreciated as this seems very difficult for me.

[timl · Accepted solution]

Hi @Anonymous 

One thing you could try (if equipment number is a text column) is to use the combo box control.

This control enables the user to enter such text into the control. You can then use formula to retrieve the selected item in the combo box control, and use that to determine the equipment area and description of the selected item.

[Anonymous]

Hi @timl ,

Thank you for that suggestion, I think this could work really well. Im watching some videos on how to use the comboboxes.

Is it possible to put the table of Equipment Number, Description etc into Excel Online rather than Normal Excel, and use this Excel Online table as the ComboBox data.

Because basically I want it so that if a new piece of equipment is added, I want someone to be able to go into the Excel Online and add the Equipment Number, Description etc to the table and this then automatically refresh in the App. Because if I use a normal Excel table, and then a new piece of equipment is added to it, it will require someone to go into the app, remove the Excel Table, and then re-upload it.

Thanks

[timl]

Yes, it's possible to use Excel Online. If you are considering changing data sources, I would recommend that you store your data in a SharePoint list rather than Excel. This would provide a much more robust solution, compared to Excel.

[Anonymous]

Hi, the issue I think with this is that on PowerApps there is the Data Row Limit of 2000 rows '(this is the number of rows that are retrieved from server-based connections where delegation is not supported)'. The data table I have with Equipment Numbre etc has nearly 6000 rows. Therefore, would this mean it will not work with Excel Online or SharePoint List?

[Drrickryp]

@Anonymous

The best would be to move the data to Dataverse if you anticipate adding more rows. 

[Anonymous]

Does this require a higher level of PowerApps?

[Drrickryp]

@Anonymous 

I believe Dataverse for teams does not require a higher license.