web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Power apps not scannin...
Power Apps
Answered

Power apps not scanning entire SQL DB

(0) ShareShare
ReportReport
Posted on by CharlesBrewer 697

Hey there,


So I have a filter in place for my gallery Items, and I noticed it was only showing 3 items, when it should be showing about 277. The first 3 are in the top 500 rows of the SQL DB, and then the 4th one (which is does not show) is in the 551st row. Does it not scan the entire DB, only 500 rows or so? How can I get it to scan the entire thing?

Categories:
Building Power Apps
I have the same question (0)
  • TheRobRush Profile Picture
    TheRobRush 11,128 Moderator on at

    If you end up having /always/ less than 2000 items you can just change the limit in settings

    TheRobRush_0-1671135570708.png

    past that you will want to start ensuring all your calls to your datasource are properly delegated

     

  • LaurensM Profile Picture
    LaurensM 12,516 Moderator on at

    Hi @CharlesBrewer that entirely depends on how the filter was written.
    Within Power Apps you have delegable and non-delegable functions / fields for data sources.

    When you have a blue line somewhere in/under your filter statement, your query to SQL is not delegable. This causes the filter to only look at the first 500 (can be adjusted to 2000) records. You can change the amount of records to 2000 by (1) going to your app settings, (2) scrolling down in the general tab and (3) changing the 'Data row limit' from 500 to 2000.

    If your data source is larger than 2000 records (or will be in the future), you will have to rewrite your filter statement.

    I will link some useful documentation:


    I hope this helps!

  • CharlesBrewer Profile Picture
    CharlesBrewer 697 on at

    Yeah total rows is over 4000 😞 I'll have to figure out what Delegated means...I did a google and it say the Filter() command is delegated but perhaps that means something else. Thanks though! I'll have to figure this out.

  • TheRobRush Profile Picture
    TheRobRush 11,128 Moderator on at

    Share the current formula and we can help you set it up correctly for delegation

  • CharlesBrewer Profile Picture
    CharlesBrewer 697 on at

    Oh hey much appreciated! Assuming I have the filter set correctly (I am unsure if there should be a comma or an && between the two filters on last line), then it is this : 

    Filter(AddColumns(tblAssets As Main,
"Allison",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom1,
"AllisonExp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom2,
"Bendix",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom3,
"BendixExp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom4,
"CatET",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom5,
"CatETExp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom6,
"Insite",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom7,
"InsiteExp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom8,
"Davie",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom9,
"ESA",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom10,
"JPRO",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom11,
"JPROExp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom12,
"WabcoExp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom13,
"WabcoKey",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom14,
"WabcoOrderNum",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom16,
"WabcoSN",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom17,
"SR4OrderNum",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom18,
"SR4",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom19,
"SR4Exp",LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom20,
"Department",LookUp(tblAssetCustom,AssetID=Main.AssetID).Department,
"State",LookUp(tblAssetCustom,AssetID=Main.AssetID).State),
State=1,Department = "Service (Diagnostic)")
  • LaurensM Profile Picture
    LaurensM 12,516 Moderator on at

    Hi @CharlesBrewer the delegation issue occurs due to the AddColumns() function.
    This function is partially delegable, meaning that it will go over all the records in the database but will only output 500 (to max 2000) records.

    With the current requirements and since the filter values are hardcoded, I would opt for a Power Apps triggered Power Automate flow (e.g. OnVisible of the screen). Power Automate has the ability to send SQL queries (Execute SQL Query V2 - if your SQL is not on-prem) to your database, allowing you to easily JOIN tables and apply filters.

    The filtered response can then be sent to Power Apps as a JSON body and used in a collection. You would still be limited to 2000 records due to the max fetch size of a ClearCollect(), but it would be 2000 records after your filter.
    (I have also seen some threads about workarounds to collect more than 2000 records, so that would probably be possible as well.)

     

    @TheRobRush , do you see another solution besides a flow based query?

  • CharlesBrewer Profile Picture
    CharlesBrewer 697 on at

    Hey @LaurensM , there are about 4200 record pre-filter, but if by "output" you mean after the filtering than there is only about 277. Though I am guessing it goes by the number before that, since it is only showing 3 and I cant bump it up over 4000.  I don't mind doing it the Automate way, but I've only done about 3 Desktop Flows and I imagine they aren't like this, I have no idea where to start 😞

  • LaurensM Profile Picture
    LaurensM 12,516 Moderator on at

    @CharlesBrewer the AddColumns returns a limited set of records after which you apply the filter. The AddColumns goes over the 4200 records, returns 500 to 2000 (depending on your data row limit settings) and then the filter is applied on that output set.

    To my knowledge the AddColumns are needed first to join the tables together, because your filter values are based on the related tables and not the main table itself. Before starting to revamp your data fetching, it might be beneficial to look if other users have workarounds in Power Apps instead of Power Automate. 😊

  • TheRobRush Profile Picture
    TheRobRush 11,128 Moderator on at

    @LaurensM honestly I tend to use sharepoint myself, so not sure if theres an extra way to do that with SQL, @WarrenBelz might know?

     

     

  • WarrenBelz Profile Picture
    WarrenBelz 155,972 Most Valuable Professional on at

    Hi @CharlesBrewer ,

    Thanks @TheRobRush for the tag.
    One possibility here is to "pre-filter" as below. Providing the output of the top filter (you can query as many records as you want) is under your Delegation limit (max 2,000), this should work.

    With(
 {
 wList:
 Filter(
 tblAssets,
 State = 1 && 
 Department = "Service (Diagnostic)"
 )
 }, 
 AddColumns(
 wList,
 "Allison",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom1,
 "AllisonExp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom2,
 "Bendix",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom3,
 "BendixExp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom4,
 "CatET",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom5,
 "CatETExp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom6,
 "Insite",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom7,
 "InsiteExp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom8,
 "Davie",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom9,
 "ESA",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom10,
 "JPRO",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom11,
 "JPROExp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom12,
 "WabcoExp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom13,
 "WabcoKey",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom14,
 "WabcoOrderNum",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom16,
 "WabcoSN",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom17,
 "SR4OrderNum",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom18,
 "SR4",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom19,
 "SR4Exp",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Custom20,
 "Department",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).Department,
 "State",
 LookUp(tblAssetCustom,AssetID=Main.AssetID).State
 )
)

    Also if tblAssetcustom is under 2,000 records, this will work much faster

    With(
 {
 wList:
 Filter(
 tblAssets,
 State = 1 && 
 Department = "Service (Diagnostic)"
 ),
	 wAsset: tblAssetCustom
 }, 
 AddColumns(
 wList,
 "Allison",
 LookUp(wAsset,AssetID=Main.AssetID).Custom1,
 "AllisonExp",
 LookUp(wAsset,AssetID=Main.AssetID).Custom2,
 "Bendix",
 . . . . . . . .
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 305 Most Valuable Professional

#2
11manish Profile Picture

11manish 212

#3
Valantis Profile Picture

Valantis 167

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans