cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Collect Items from sharepoint list to powerapps

Hi everybody 🙂

 

I am quite new to PowerApps and I need your help. 

 

I have a SharePoint list which is continuously being populated using an app I created in PowerApps, but now the last element of the search function is an item that was added two weeks ago.

I think that I have to create a collection in order to divide the items, but I am not able to find the right code to make it work. 

 

Could someone please help me?

 

Thanks a lot 🙂 

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User III
Dual Super User III

If you are using Power Apps then this is the wrong forum.  But anyway

 

1) In Power Apps you can change the data row limit by going to File > Settings > Advanced Settings.

screenshot.png

2) Using User().Email inside a function is non-delegable.  You should set a variable equal to that value and then use the variable.  Put the following in OnStart and replace the User().Email in your formula with currentuser.

set(currentuser,user().Email)

 See if that works better.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

15 REPLIES 15
Dual Super User III
Dual Super User III

I'm assuming that your data source has now grown bigger than the Data Row Limit default of 500 rows. One way to temporarily fix your issue is simply to increase the Data Row limit in Advanced settings. The maximum is 2,000.  But that just delays the inevitable.  The right fix is to make sure that you are retrieving only the records you need using a Delegable function to filter the data set.  Delegable functions are not limited by the Data Row limit, so they can work with your entire data source.

 

Your specific problem is that Search is not a delegable function in SharePoint, but Filter is.  If you can pre-filter your data source inside the Search function to a dataset smaller than 500 rows, you will find that Search will work reliably.

 

For example, If I have a list of People and addresses I want to search I could pre-filter them on a State Abbreviation and then search the results using this formula

Search(Filter('People List', WorkState="some abbreviation"),Searchbox.Text,Searchcolumn1, Searchcolumn2)

The pre-filter can also be dynamically supplied from something like a dropdown.  The key here is to get the list being search below the limit. Reading everything into a local collection will work, but there are a number of downsides dealing with performance and refreshing data.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Thank you for your answer. 

I actually already use both filter and search, but the filter doesn't show 500 filtered items, it seems like it counts all the items anyway.

The problem is that I cannot find the "Data Row limit" in Advanced settings. Does it have another name? Thank you

Dual Super User III
Dual Super User III

Sorry, the Data Row limit can only be changed in Power Apps, not Power Automate.  I forgot which forum I was in.  Can you share what you are using for Filter and Search and I'll try to help you fix your issue.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

I am actually using PowerApps...

 

I am using this code: 

Search(Filter('MyList',Data>=DatePicker1.SelectedDate && Data<=DatePicker2.SelectedDate, Who=User().Email), SearchBox_7.Text, "Row1")

 

"Row1" is actually "Column1", sorry

Dual Super User III
Dual Super User III

If you are using Power Apps then this is the wrong forum.  But anyway

 

1) In Power Apps you can change the data row limit by going to File > Settings > Advanced Settings.

screenshot.png

2) Using User().Email inside a function is non-delegable.  You should set a variable equal to that value and then use the variable.  Put the following in OnStart and replace the User().Email in your formula with currentuser.

set(currentuser,user().Email)

 See if that works better.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

I tried both suggestions! For now it works well, I hope it won't stop after reaching 2000 elements. 

 

Thank you!

Have a nice day 🙂

Dual Super User III
Dual Super User III

It will stop being effective once you reach 2,000 items.  What you have done is just a stop gap measure.  YOu really need to find a way to redefine what you are doing to use delegable functions.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

I was quite sure it will stop working, but I thought that using the variable you suggested would have change something.

Unluckily, I think there is no other way to redefine what I am doing, so I guess that the solution will be to create a new SharePoint list.

Dual Super User III
Dual Super User III

removeing the User().Email will help.  But it will probably still break at 2,000 unless you change the formula.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Is there another formula to do the same thing avoiding this issue?

Dual Super User III
Dual Super User III

If you switched out the User().Email for a variable, then the inner Filter should be delegable.  If it is then you only have to worry that it will return less than 2,000 records.  At that point your search will continue to work.  Can you provide a screenshot of the formula when you are editing it so we can see what parts are giving delegation warnings (blue underlines).



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

screen.png

Here it is. Thank you for your time!

Dual Super User III
Dual Super User III

That's about what I expected.  Search is definately not delegable, so you make that the outermost function.  YOu've already done that.  But dates aren't normally delegable either.  so you need to use two filter statements.  An inner one that pre-filters based on the user.  Hopefully that one will get you below the row limit.  Then you can filter on the dates and apply the search.  Try this.

Search(Filter(Filter(SharePointList,Who=info.mail),Data>=DatePicker1.SelectedDate && Data <= DatePicker2.SelectedDate),SearchBox_7.Text, "Valore ")


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

screen.png

Unluckily, it seems nothing changed

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (6,391)