Get all the Items for SharePoint which ID is inside a collection, without delegation warning

(0) Share

Report

Posted on by johnjohn123

3,506

i have 2 SharePoint lists:-

1) Task list .. with these fields:-

- ID

- Title

2) PeopleWork list.. with these fields:-

- ID

- TaskID

- Single selection people/group field named People

So now i tried to create a demo gallery which should only show the Tasks where the user is assigned to. so on the Screen OnVisible i build this collection to get the PeopleWork items which have the login user email:-

ClearCollect(relatedtasks,Filter(PeopleWork,People.Email=User().Email))

then on the Items property of the gallery i define this formula:-

but i also got a delegation warning..is there a way to fix this?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

v-qiaqi@microsoft.com on at

Like (0)

Report

Hi @johnjohn123,

If you want to overcome the in operator within the Filter() function, I am afraid there is no way to achieve this in Power Apps.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@v-qiaqi-msft that looks weird to me.. i am asking a very basic functionality,,, isn't it? so are their any workarounds to this?

Was this reply helpful? Yes No
jaredbidlow 256 on at

Like (0)

Report

It's a feature currently in preview for Dataverse for Teams.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@jaeiow which feature you are talking about? and i am not using Dataverse, i am using SharePoint !!

Was this reply helpful? Yes No
v-qiaqi@microsoft.com on at

Like (0)

Report
Hi @johnjohn123,

No, this is not a basic feature.

Currently, the 'in' operator is not delegated in SP connector, please check the following article for more details:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint

The Delegation warning issue is not an error, it just means that you could not delegate the Data process from your app to your data source itself. Instead, you could only process data locally. And you could only process 2000 records locally at most.

If you want to remove the Delegation warning prompt, and the amount of your SP List is not more than 2000, I think the collection could achieve your needs. Please try the following workaround:

Set the OnSelect property of the OnStart property to following:

ClearCollect(colarran,task)

then modify your formula as below:

Filter(colarran, ID in relatedtasks.TaskID)

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@v-qiaqi-msft my list contain 200K ++ items.. so your approach will not work

Was this reply helpful? Yes No
v-qiaqi@microsoft.com on at

Like (0)

Report

Hi @johnjohn123,

If the amount of your SP List records is more than 2000, I think there is no direct way to achieve your needs in PowerApps.

As a possible solution, you could consider fire a flow from your canvas app, and then retrieve records from your SP list using the flow, then return the retrieved records back to your canvas app, and save it into a collection. After that, you could apply above Filter formula to collection.

Please check and see if the following thread solution or video resource could achieve your needs:

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-get-combobox-to-show-all-the-results-from-SP-list/m-p/612204#M193064

https://www.youtube.com/watch?v=2M0zCyu__20

Was this reply helpful? Yes No
Verified answer

johnjohn123 3,506 on at

Like (0)

Report
@v-qiaqi-msft then what about looping through the collection and issue separate lookups(), as follow:-

i tried it and it worked,, also i did not get any delegation warning..

ClearCollect(relatedtasks,Filter(PeopleWork,People.Email=User().Email)); Clear(colUserTasks); ForAll( relatedtasks, If( !IsEmpty( LookUp( task, ID = TaskID ) ), Collect( colUserTasks, LookUp( task, ID = TaskID ) ) ) )

Was this reply helpful? Yes No