cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
panand99
Level 8

AddColumns Delegation Issue - Filter with User().Email

Hi,

 

I have more than 500 records and I want to filter these with User(), the user email is added in a seperate multi value text field.

 

What I did, I add a new colum by using AddColumn and then I use this on filter but it has delegation issue, it can`t filter more than 500 records.

 

SortByColumns(Filter(AddColumns(Request,"colAllReaders",DocumentReaders),Value(Find(Lower(loginUserEmail),Lower(colAllReaders),1))>0),"Created",If(SortAescending1,Ascending,Descending))

 

* Request - DataSource- SharePoint List

*DocumentReaders -Multi Value field having user email

*loginUserEmail - User().Email

 

I know, we can filter records if we use - loginUserEmail=RequestCreater, but in this case it only filter if it is having only 1 user email, my query is if we have multiple emails in a field, then how do we filter?

 

However, there is a separate post that Microsoft enhanced AddColumns, but it yet did not for sharepoint list.

 

 

 

9 REPLIES 9
Community Support Team
Community Support Team

Re: AddColumns Delegation Issue - Filter with User().Email

Hi @panand99 ,

Could you please share a bit more about the DocumentReaders column in your SP List? Is it a Choice type column (enabled "Allow multiple selections" option) or a Multi-Line text type column?

Could you please share a screenshot about the Delegation warning issue within your formula?

 

1. If the DocumentReaders column is a Multiple lines of Text type column in your SP List:

Based on the needs that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. There is no Delegation warning issue with my formula:1.JPG

Please consider modify your formula as below:

SortByColumns(
Filter(
AddColumns(Request,"colAllReaders",DocumentReaders),
!IsBlank(Find(Lower(loginUserEmail), Lower(colAllReaders))) /* <-- Modify your formula here */
),
"Created",
If(SortAescending1, Ascending,Descending)
)

 

2. If the DocumentReaders column is a Choice type column or Person type column (allow Multiple ) in your SP List:

    a)  If the DocumentReaders column is a Choice type column, which enabled "Allow multiple selections" option in your SP list:

Please consider modify your formula as below:

SortByColumns(
Filter(
AddColumns(Request, "colAllReaders", DocumentReaders),
!IsBlank(Find(Lower(loginUserEmail), Lower(Concat(colAllReaders, Value))))
),
"Created",
If(SortAescending1,Ascending,Descending)
)

b) If the DocumentReaders column is a Person type column, which enabled "Allow multiple selections" option in your SP list:

Please consider modify your formula as below:

SortByColumns(
Filter(
AddColumns(Request, "colAllReaders", DocumentReaders),
!IsBlank(Find(Lower(loginUserEmail), Lower(Concat(colAllReaders, Email))))
),
"Created",
If(SortAescending1,Ascending,Descending)
)

2.JPG

Please consider take a try with above solution, then check if the issue is solved. Please also consider take a try to add Choice type column or Person type column (which enable "Allow multiple selections" option) in your SP List to store the user emails, then try above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
panand99
Level 8

Re: AddColumns Delegation Issue - Filter with User().Email

Hello @v-xida-msft 

 

Thanks for your reply, DocumentReaders in multiple value of text field. The issue here is that - AddColumns in a delegate function, it will retrun only 500 records. Please look into below link from powerapps help.

 

If you need more information, please revert.

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

 

However, the output of these functions is subject to the non-delegation record limit. In this example, only 500 records are returned even if the RealEstateAgents data source has 501 or more records.

 

 

Community Support Team
Community Support Team

Re: AddColumns Delegation Issue - Filter with User().Email

Hi @panand99 ,

Why do you use AddColumns function in your formula?

 

Based on the needs that you mentioned, I think it is not necessary to add a AddColumns function within your formula. Please modify your formula as below:

SortByColumns(
Filter(
Request,
!IsBlank(Find(Lower(loginUserEmail), Lower(DocumentReaders))) /* <-- Modify your formula here */
),
"Created",
If(SortAescending1, Ascending,Descending)
)

Above formula may cause a Delegation warning issue, it the amount of your SP List records is not more than 2000, you could ignore this warning issue. You could change the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App setting of your appthen you could process 2000 records locally at most in your app.

 

If the amount of your SP List records is more than 2000 records, please check and see if the alternative solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

On your side, please take a try with the following workaround:

Set the OnStart property of the App control to following (I assume that there are 10000 records stored in your SP List):

Concurrent(
    ClearCollect(col1, Filter(Request, ID >= 1 && ID <= 2000)),
    ClearCollect(col2, Filter(Request, ID >= 2001 && ID <= 4000)),
    ClearCollect(col3, Filter(Request, ID >= 4001 && ID <= 6000)),
    ClearCollect(col4, Filter(Request, ID >= 6001 && ID <= 8000)),
    ClearCollect(col5, Filter(Request, ID >= 8001 && ID <= 10000))
);
ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

then modify above Filter formula as below:

SortByColumns(
Filter(
MergedCollection,
!IsBlank(Find(Lower(loginUserEmail), Lower(DocumentReaders))) /* <-- Modify your formula here */
),
"Created",
If(SortAescending1, Ascending,Descending)
)

Note: Please make sure that you have already changed the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App setting of your app.

Please take a try with above solution, check if the issue is solved.

 

Also please check and see if the following blog would help in your scenario:

https://blog.coeo.com/davidmorrison/2017/06/21/powerapps-500-record-limit-delegation-and-how-to-work...

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
panand99
Level 8

Re: AddColumns Delegation Issue - Filter with User().Email

Hi @v-xida-msft 

 

I`ve changed my limit to 2000, but it still shows total of 2000 records in merged collection.

 

Could you please let me know what I am doing wrong.

 

Community Support Team
Community Support Team

Re: AddColumns Delegation Issue - Filter with User().Email

Hi @panand99 ,

How many records existed in your SP list?

Have you reload your app to fire the OnStart property of the App control in your app?

 

Based on the needs that you mentioned, I think the soltion I provided above could achieve your needs.

 

If the issue still exists, please consider take a try with the solution mentioned within the following blog:

https://blog.coeo.com/davidmorrison/2017/06/21/powerapps-500-record-limit-delegation-and-how-to-work...

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
panand99
Level 8

Re: AddColumns Delegation Issue - Filter with User().Email

Hello @v-xida-msft 

 

That solution also didn`t work as expected. it collect only 2000 records.

 

If you have any working example then please share.

 

Thanks

 

Community Support Team
Community Support Team

Re: AddColumns Delegation Issue - Filter with User().Email

Hi @panand99 ,

Have you taken a try with above solution I provided?

Have you remove the AddColumns function from your formula?

Do you mean that the MergedCollection set up within the OnStart property of the App control only contains 2000 records? How many records existed in your data source? More than 2000?

 

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs. Currently, I could not reproduce your issue on my side.

Note: Please do not add AddColumns function within your formula.

 

The user @Vinith46 has faced similar issue with you, please check my response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/powerapps-search-with-huge-data-against-share...

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
K-A-R-L
Level 10

Re: AddColumns Delegation Issue - Filter with User().Email

Hi @panand99 ,

 

Can you please check if you have changed the Non-delegable limits properly within your App Settings. More details about changing the Non-delegable limits in  File > App Settings > Advance Settings > Change the Data Row Limit for non-delegable queries.

 

----------------------------------------------------------------------------
Thanks,
K-A-R-L


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you thought this post was helpful, please give it a Thumbs Up.

panand99
Level 8

Re: AddColumns Delegation Issue - Filter with User().Email

Hello @K-A-R-L  @v-xida-msft 

 

Please see I have set 2000 records in app setting properly. Is there any other setting to be checked?

 

2000 limit issue.png

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

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

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 47 members 4,300 guests
Recent signins:
Please welcome our newest community members: