Filter DirectReport results using Office365Users connector

[nmn_lamba]

Hi,

On App load, I have these variables defined:

Set(
varUser,
{
myManager: Office365Users.ManagerV2(User().Email),
myDirectReport: Office365Users.DirectReportsV2(User().Email)
}
);

Now, I need to filter results in a gallery, where my data source is SharePoint and a single line text column named 'Submitter', which contains email address of employees who have created the record.

I have used this function and I am not getting results + also getting the delegation warning: 

Filter(myList, Submitter in varUser.myDirectReport.value.userPrincipalName)

I haven't had a chance to ask a manager to login to the App yet, so that he/she can see the results in gallery, plus I am concerned about delegation.

Thanks in advance!

[v-siky-msft]

Hi @nmn_lamba ,

The code you used is good, but Delegation issue is hard to eliminate, since 'in' operator is not delegable for SharePoint.

If the row number of list is less than 2000, I would suggest you leave that issue alone since the delegation issue won't make any influence on the data( increase the data limit to 2000).

If the list has more than 2000 record, I may suggest you change to use SQL Server data base, because in operator is delegable for SQL.

Hope this helps.

Sik

[nmn_lamba]

Thanks @v-siky-msft !

I don't think I am in a position to switch the data source, also the list is expected to grow beyond 2000. Hence, this delegation would need a workaround.

Any further suggestions, please do let me know...

[v-siky-msft · Accepted solution]

Hi @nmn_lamba ,

If the list has more than 2000 records, here is a workaround that collect all list records to local collection. Due to the Delegation Limit , we need to collect multiple times, and each time can only save 2000 pieces of data.

ClearCollect(col1, Filter('SP list', indexcolumn<=2000));
ClearCollect(col2, Filter('SP list', indexcolumn>2000&&indexcolumn<=4000));
ClearCollect(colAll,col1,col2)

 Note: The indexcolumn can't be ID column since SharePoint only supports the equal ('=') operation for delegation on an ID column. It must be a custom Number column.

For further assistance, Try to submit a Support Ticket to Microsoft. The PowerApps Teams will make a contact on you.

Sik

[nmn_lamba]

Thank you @v-siky-msft !

I added this collection on App load and I have noticed that the performance has boosted a bit, which is great!

On collections, I was hoping the delegation would go away on Sort function, but it didn't.

As a last query on this, could you please help identify, how to use it effectively?
ClearCollect(FilterSomething,{Result:"All"},Sort(Distinct(AllItems,Region),Result)); 

[v-siky-msft]

Hi @nmn_lamba ,

I made a similar test, but I can't reproduce your issue.

When only used with a data source, these function might have delegation issue. I don't think a local collection will also have delegation issue. Could you please share more details on this scenario?

Sik

[nmn_lamba]

Thank you @v-siky-msft , I didn't have a chance to visit this forum, but you are correct no more delegations and records also load super fast by collecting them in the app itself.