cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate IV
Advocate IV

Help with function

Hi everyone

 

I've created a simple app to manage annual leave which which stores its data in a SharePoint list. I need to be able to provide a view for managers so they can see all of their direct reports leave. To do this I've first created a collection called MyReportsCollection using Office365Users.DirectReports() to store their direct reports.

 

I then initially planned to join this onto my SharePoint list to return only the leave relating to the current users direct reports (I do not want to bring all of the data into the app to then filter it). However I understand there is no join function so I've resorted to using the below function which works fine.

 

ClearCollect(MyCollection,Filter(AddColumns(SharePointList,"FilterOnThis",LookUp(MyReportsCollection,CreatedByO365UserId=Id).DisplayName),!IsBlank(FilterOnThis)))

 

Where "CreatedByO365UserId" is the office id of the user who created the item which is stored in the list and

"Id" is the office id of the user in MyReportsCollection.

 

Basically, I am trying to understand if this is being deletgated or if all of the list data is being returned to the app and then filtered. I understand filter is delegable but don't know if the addition of AddColumns is collecting it into the app first.

 

If anyone can help provide an answer on this or an alternative method which would prevent me from bring a large amount of data into the app it would be much appreciated.

 

Thanks Adam

4 REPLIES 4
Highlighted
Advocate IV
Advocate IV

Re: Help with function

It looks like the AddColumns function can't be delegated so I am assuming all of the list data (500 items max?) is being brought back into the app before the calculation is applied? What is strange is that I do not receive a blue dot in the PowerApps designer to inform me of this.

 

I have raised an Idea here requesting join functionality that can be delegated.

Highlighted
Community Champion
Community Champion

Re: Help with function

Hi @areid

The join functionality should really be covered by the 'in' operator.  According to this documentation it is not currently delegated for SharePoint, so it would not solve your issue anyway.

In any case, indeed strange that you are not receiving the blue-dot suggestion.

Are you able to filter across all your data source (i.e. more than the first 500 rows)? May the operation is delegated now and the documentation is simply not up to date.

Highlighted
Advocate IV
Advocate IV

Re: Help with function

Thanks @Meneghino, I was not aware of the "in" operator. I've written the below function and receive the blue indicator saying "Part of the formula cannot be evaluated remotely, right side of "in" operator is not a column name". It does actually work and I receive the results I expected.

 

ClearCollect(NewCollection,Filter(SharePointList, CreatedByO365UserId in MyReportsCollection.Id))

 

My data source is currently small as I'm still testing the app but I would probably need to create some test data with a few thousand records to check if my original function captures all results.

Highlighted
Community Champion
Community Champion

Re: Help with function

I have just checked and the 'in' operator is still not delegated for SQL server even though documentation says it is.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,597)