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

[johnjohn123]

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

[v-qiaqi-msft]

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.

[johnjohn123]

@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?

[jaeiow]

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

[johnjohn123]

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

[v-qiaqi-msft]

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

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)

[johnjohn123]

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

[v-qiaqi-msft]

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

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

[johnjohn123 · Accepted solution]

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