cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerAppsJunkie
Helper I
Helper I

How do you Filter / Search SQL data returned from a prior search?

Hi I'm new to PowerApps and trying to learn as I go.

 

I have an application where I am doing a search on a SQL Table to return records based on who is logged in and that part works.  Now I want to either do another search based on those returned results or filter of those results based on what someone is searching on in a text box.

I've tried filter when using on prem-SQL and the results take forever so I went with search statement instead and it's much much quicker.  Is there something wrong in my filter statement? or is filter that much slower than search?

Here is the formula I'm using on Items to display in a gallery.

 

Filter Function: (Very Slow) but it returns data based on OwnerName who is logged in

Filter('[dbo].[Test]',Lower(OwnerName) = Lower(Office365Users.MyProfile().DisplayName))

 

Search Function to display same data (Much Faster)

Search('[dbo].[Test]',Office365Users.MyProfile().DisplayName,"OwnerName")

I get results back in the gallery but now I want to add a search box that will search / filter the first results returned.

So the search is first on Owner Name but that might return 400 rows and to limit that further I want a search box that a user could type information in that would further filter this data down so it's more usable.

This is all tied back to a SQL on-premise backend.

Any suggestions or help would be greatly appreciated.

1 REPLY 1
Jeff_Thorpe
Super User
Super User

You can easily fix the performance issue with the filter by changing a couple of things.

 

Create a variable in the OnStart of the App or OnVisible of the Screen that will hold the user's name:

Set(gvUserDisplayName, Lower(Office365Users.MyProfile().DisplayName))

 

Then change filter formula to 

Filter('[dbo].[Test]',Lower(OwnerName) = gvUserDisplayName)

 

This should improve performance by only making one call to Office365Users.MyProfile() instead of one call for each row being required. 

 

You can add additional search functionality by wrapping the search function around the filter:

Search(Filter('[dbo].[Test]',Lower(OwnerName) = gvUserDisplayName), SearchInput.Text, "ColumnName")

 

SearchInput would need to be changed to the name of your search box and ColumnName would need to be changed to the SQL column you want to search. If the search box is empty it should just return what the Filter() is returning.

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (83,227)