cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Filter DirectReport results using Office365Users connector

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!

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

6 REPLIES 6
Community Support
Community Support

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

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

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

View solution in original post

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

Hi @nmn_lamba ,

 

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

Snipaste_2020-10-14_12-03-39.png

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

 

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.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (70,983)