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

Filter On data source connection

Hi all,

 

I am trying to connect to a SharePoint list and filter by user().email=person.email()

Filter('Active Field Leadership', 'Performed By'.Email=User().Email)

 

I previously had it wrapped in a Sort by columns  function

SortByColumns(Filter('Active Field Leadership', 'Performed By'.Email=User().Email), "Date", SortOrder.Descending)

 

i have been using a filter() query in my browse screen, but I think it has maxed out on the number of entries in the list.

How can I create a connection to the list that only pulls the list items of the user, rather than pulling all items and trying to filter?

30 REPLIES 30
Super User
Super User

Re: Filter On data source connection

 
The Filter query, looks for the records matching the condition  provided and only fetches those records which satisfies it rather than pulling all the records. 
 
What is the approximate total number of records associated with each user in the sharepoint list? Can you please share more details, so that we can help you better.
 
Thanks, 
Highlighted
NicolaNeedsHelp
Level: Powered On

Re: Filter On data source connection

Probably around 10-20 per user at the moment, but we have around 150 users. I have this in the browse gallery at the moment. Is there a better place to use this function?
 
It looks as if it is pulling older records, but not the newest ones.
Super User
Super User

Re: Filter On data source connection

Hi @NicolaNeedsHelp 

 

Can you try this: Refresh the data source using Refresh(<<DataSource>>) and sort the returned results based on Last Modified attibute to get the newly created ones?
 
Thanks, 
NicolaNeedsHelp
Level: Powered On

Re: Filter On data source connection

I tried using refresh() in two places

- App -> Action -> On start

- BrowseGallery1 -> Data -> Items (as part of my existing query above )

 

Taking ages to load and still only getting older items.

I checked and my ahrepoint list currently has 891 entries.

 

Any other ideas, or should I try the refresh somewhere else?

Super User
Super User

Re: Filter On data source connection

Hi @NicolaNeedsHelp , 

 

Kindly check that the Data Row limit for Non-Delegable queries is set to 2000, this is the max value that can be entered by the user and works upto 2000 records out of all using the Filter query. The soft limit is 500 which can be updated to 2000 records.

 

Settings:

Navigate to File -> App Settings -> Advanced Settings -> Look for Data Row limit for Non-Delegable queries

 

Hope this helps!

NicolaNeedsHelp
Level: Powered On

Re: Filter On data source connection

Yes, I have tried this. It will give me my 17 current list items, but what happens when the full list exceeds 2000.
Super User
Super User

Re: Filter On data source connection

Hi @NicolaNeedsHelp , 

 

In that case you can use collections: 

 

If you more than 2000 records and want to use a local collection then follow below steps:

a) We will use the ID field to get the records

b) create a collection to store the number of loop count:

ClearCollect(LoopCount,FirstN([0, 2000, 4000, 6000, 8000, 10000],RoundUp(First(Sort(<<YourSPList>>,ID,Descending)).ID/2000,0)));

c) Now work on the main collection to get all the data, this can be done through OnVisible property of the page.

Clear(ListItems);

ForAll(LoopCount,Collect(ListItems,Filter(<<Sp LIST>>,ID> Value)));

d) Once all the data is inside the collection, filter out the gallery items based on the collection created using the same formula rather than referring to the data source directly.

 

Hope this Helps!

NicolaNeedsHelp
Level: Powered On

Re: Filter On data source connection

Ok, I have tried this in

-> App ->Action ->On Start

AND ->BrowseGallery1->Action->OnVisable

 

ClearCollect(LoopCount,FirstN([0,2000,4000,6000,8000,10000],RoundUp(First(Sort('Active Field Leadership',ID,Descending)).ID/2000,0)));

Clear(ListItems);

ForAll(LoopCount,Collect(ListItems,Filter('Active Field Leadership','Performed By'.Email=User().Email)))

 

All I get is collections with no records...what am doing wrong?

Super User
Super User

Re: Filter On data source connection

Hi @NicolaNeedsHelp 

 

This might be beacause you removed the condition for looping. 
What data are you getting inside the LoopCount collection?
ClearCollect(LoopCount,FirstN([0,2000,4000,6000,8000,10000],RoundUp(First(Sort('Active Field Leadership',ID,Descending)).ID/2000,0)));
Clear(ListItems);
ForAll(LoopCount,Collect(ListItems,Filter('Active Field Leadership','Performed By'.Email=User().Email && ID > Value)))
Also, do a right click on the App Start and select run AppStart. I am assuming you have increased the soft limit of 500 to 2000 in the App Settings as mentioned in my previous responses. 
 
Hope this Helps!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 132 members 4,005 guests
Please welcome our newest community members: