cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wedwards
Level: Powered On

StartsWith and 500 record limitation

I am using the following function in the Gallery

 

SortByColumns( If(IsBlank(TextSearchBox1.Text),TestList,Filter(TestList,StartsWith( GivenName, TextSearchBox1.Text ) || StartsWith( SurName, TextSearchBox1.Text ) )),"UserDisplayName", If(SortDescending1, Descending, Ascending))

 

This allows me to search records for the user by first or last name.  The function partially works in that I can search, however only the first 500 items of a list that contains less than 1000 items are visible.  I have checked (see references) and all the functions and operators are delegatable but I am only seeing the first 500 records.  The simplest way to demonstrate this is to reverse sort the list and you see the viewable items in the gallery is incomplete.  How do I overcome this 500 item limitation even though delegation is not the issue

 

Notice, there is no indication of a filtering, delegation or limitation on the number of records

 

 

 

Record_Limitations.png500 record limitation

  

References:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/
https://powerapps.microsoft.com/en-us/blog/powerapps-update-large-sharepoint-lists-startswith-delega...

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: StartsWith and 500 record limitation

SortByColumns( 
    If(IsBlank(TextSearchBox1.Text),TestList,
        Filter(TestList,
            StartsWith( GivenName, TextSearchBox1.Text ) || 
            StartsWith( SurName, TextSearchBox1.Text ) 
        )
    ),
"UserDisplayName", If(SortDescending1, Descending, Ascending)
)

I don't know if this will work, but can you try inverting the condition so it's outside? 

 

If(IsBlank(TextSearchBox1.Text),    
    SortByColumns(     
        TestList,
    "UserDisplayName", If(SortDescending1, Descending, Ascending)
    ),

    SortByColumns(     
        Filter(TestList,
            StartsWith( GivenName, TextSearchBox1.Text ) || 
            StartsWith( SurName, TextSearchBox1.Text ) 
        ),
    "UserDisplayName", If(SortDescending1, Descending, Ascending)
    )
)

 

My guess is it's just a blue dot they forgot to put in. 

Microsoft Employee
@8bitclassroom

View solution in original post

10 REPLIES 10
mr-dang
Level 10

Re: StartsWith and 500 record limitation

SortByColumns( 
    If(IsBlank(TextSearchBox1.Text),TestList,
        Filter(TestList,
            StartsWith( GivenName, TextSearchBox1.Text ) || 
            StartsWith( SurName, TextSearchBox1.Text ) 
        )
    ),
"UserDisplayName", If(SortDescending1, Descending, Ascending)
)

I don't know if this will work, but can you try inverting the condition so it's outside? 

 

If(IsBlank(TextSearchBox1.Text),    
    SortByColumns(     
        TestList,
    "UserDisplayName", If(SortDescending1, Descending, Ascending)
    ),

    SortByColumns(     
        Filter(TestList,
            StartsWith( GivenName, TextSearchBox1.Text ) || 
            StartsWith( SurName, TextSearchBox1.Text ) 
        ),
    "UserDisplayName", If(SortDescending1, Descending, Ascending)
    )
)

 

My guess is it's just a blue dot they forgot to put in. 

Microsoft Employee
@8bitclassroom

View solution in original post

Highlighted
wedwards
Level: Powered On

Re: StartsWith and 500 record limitation

I dont know if I should call you brilliant, miracle worker, magician or just genius.

 

I've been testing this for about 10 minutes, trying to see if there is something that I've overlooked but everything is working exactly as it is supposed to.  I'm looking at the logic and I now wonder if SortByColumns is limited to 500 records, under certain conditions, which you have brilliantly resolved.

 

Hopefully, one day I can repay the favor or at least pay it forward.

 

Thank you,


W.

mr-dang
Level 10

Re: StartsWith and 500 record limitation


@wedwards wrote:

I'm looking at the logic and I now wonder if SortByColumns is limited to 500 records, under certain conditions, which you have brilliantly resolved.


I think you are right that what was in between SortByColumns was too much for it to do on the other end.

 

I'm glad it worked out for you!

Microsoft Employee
@8bitclassroom
radtadBrad
Level: Powered On

Re: StartsWith and 500 record limitation

This solved a problem that i've had for a while...but is it possible to include a third column to search from?  I've tried, but get an error message.

wedwards
Level: Powered On

Re: StartsWith and 500 record limitation

If I understand your question correctly, you would add another logical OR (||) to the filter and query function.

 

SortByColumns(    
        Filter(TestList,
            StartsWith( GivenName, TextSearchBox1.Text )    ||
            StartsWith( SurName, TextSearchBox1.Text )        ||

            StartsWith( ThirdColumn, TextSearchBox1.Text ) 
        ),
    "UserDisplayName", If(SortDescending1, Descending, Ascending)
    )

radtadBrad
Level: Powered On

Re: StartsWith and 500 record limitation

Yea that's correct....but i'm an idiot.  Just found an extra ) in the formula.  So it works, however the blue dot error is showing up now.  2017-07-20_12h28_25.jpg

wedwards
Level: Powered On

Re: StartsWith and 500 record limitation

Unfortunately, I couldnt replicate what you experienced.  I added a third query (StartsWith).  I didnt get a blue dot and it worked without issue.  I would look up each function and see what the delegation status is.  You must do this not just for the function but also against the datasource you are using.   When I look at the documentation, I've seen that a particular function or operator is delegated for one data source but not another.  An example of this is that <, <=, =, <>, >, >= are all delegable under common data services but currently only = is delegable under sharepoint. I'm using sharepoint, so I have to contort myself to figure out under what conditions can I use a function or some of the other operators mentioned and not constrain myself to 500 records.

 

I think right now, your best bet is the below references to determine what may be causing your blue dot.

 

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/ ( I forgot to add this one )

https://powerapps.microsoft.com/en-us/tutorials/delegation-list/

https://powerapps.microsoft.com/en-us/tutorials/formula-reference/

radtadBrad
Level: Powered On

Re: StartsWith and 500 record limitation

I had an idea and turns out i was right.....my column "Description" is a multiline text column....so I created another column in my sharepoint list called "Description2" and make it a singleline text column......and boom, problem solved.  Is this a known issue that i wasn't aware of?  Is there anything that i can do about it?  Or should i eliminate all multiline text columns that i intend to use to search and filter galleries?

wedwards
Level: Powered On

Re: StartsWith and 500 record limitation

Unfortunately, I do not have much experience with multiline columns. I added them briefly but then removed them.

I would be inclined to remove them, now that its on the radar or perform a stress test (add 2-3 times as much data to your tables as you think you will ever need) as a test.  If you do this and conclude that you can live with the result (performance, functionality), great but otherwise remove them, rather than find out this is a show-stopper later

 

W.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 125 members 5,353 guests
Please welcome our newest community members: