Collect Items from sharepoint list to powerapps

[Anonymous]

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 🙂 

[Pstork1]

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.

[Anonymous]

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

[Pstork1]

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.

[Anonymous]

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")

[Anonymous]

"Row1" is actually "Column1", sorry

[Pstork1 · Accepted solution]

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.

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.

[Anonymous]

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 🙂

[Pstork1]

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.

[Anonymous]

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.

[Pstork1]

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

[Anonymous]

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

[Pstork1]

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).

[Anonymous]

Here it is. Thank you for your time!

[Pstork1]

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 ")

[Anonymous]

Unluckily, it seems nothing changed