cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
opit
Helper II
Helper II

powerapps with 40.000 records filter and search

We have scenario with 40.000 records on SQL server, employee data with 60 columns. the user needs to filter by 5 columns and search by 2 columns.

Could you advice if it is possible to implement this project with powerapps and what would be the best aproach?

7 REPLIES 7
RiccoHipp1903
Resolver II
Resolver II

Hello @opit ,

absolutly. You can realise this of course. But you have to think things through.

 

The Best approache is to read the full article SQL Server - Connectors | Microsoft Docs.

 

The Problem you will face is the delegation limit, the limit of rows Powerapps can recieve from a server. You have to use filters (best all of the time) and you only can use delegatable Functions [see the article I linked]

Drrickryp
Super User
Super User

@opit 

Consider using views in sql to pre filter your data before loading it to PowerApps.

@RiccoHipp1903 thanks for reply.

I am aware of delegation. I already tried to connect sql table direct and to try to filter and search it. I am not loading data to temporary collection and filtering directly. My my doubts is it good? Will i not face to some api calls limit etc, because when user types some text in searchbox or chooses some filter then it always pull data from SQL table.

 

I see what you mean. Maybenot filtering with TextInput1.Text, instead maybe put an Icon where you set a variable onSelect with the TextInput1.Text  and use this variable to filter the gallery?

UpdateContext({SearchField1 : textInpu1.Text})
Filter(SQLTable, StartsWith(COLUN; SearchField1))

That are my untested brainstorm ideas. I'm usually not working with SQL

Ok, but is it an option? Because for example if user enters filters, then click button search and query will give more than 2000 records, then recors is missing.

I know what you mean. And thats why you should be aware to use delegatable filter functions.

So the records could be more than 2000 but will be called in small steps, so you can retrieve more than 2000 rows

RiccoHipp1903
Resolver II
Resolver II

I made it clearer for you 
I created A SP List instead of SQL because I dont have access to an SQL Server.

 

What i did looks like simple this:

RiccoHipp1903_1-1659698151324.png

Thats the version I search the moment I type and select.

 

The code for filtering is this way 

Filter('Asset manager', StartsWith(Title,TextInput1.Text) && 'Purchase price' >= Int(TextInput1_1.Text) && Dropdown1_1.Selected.Value = Status.Value && Dropdown1_2.Selected.Value = Manufacturer.Value && Dropdown1_4.Selected.Value = Color.Value)

I used only delegatable Functions. So this way I only get 100 in a gallery until i scroll down.

 

Your doubt is, that you could make more server requests than allowed. 

To avoid that you could add a Filter Icon:

Just add the Code to the OnSelect

UpdateContext({StartSearch:true})

 

Adjust the Code in the Gallery:

If(StartSearch,Filter('Asset manager', StartsWith(Title,TextInput1.Text) && 'Purchase price' >= Int(TextInput1_1.Text) && Dropdown1_1.Selected.Value = Status.Value && Dropdown1_2.Selected.Value = Manufacturer.Value && Dropdown1_4.Selected.Value = Color.Value), 'Asset manager')

 

To clear the filter maybe do a Clear Filter Button and set the varibale StartSearch to false.

 

And here is a picture of delegatable Functions for SQL Servers

 

RiccoHipp1903_0-1659698086802.png

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,882)